About
The Object datatype in PL/SQL
Disambiguation: See Oracle Database - Objects
Articles Related
Syntax
CREATE [OR REPLACE] TYPE type_name
[AUTHID {CURRENT_USER | DEFINER}]
{ {IS | AS} OBJECT | UNDER supertype_name }
(
attribute_name datatype[, attribute_name datatype]...
[{MAP | ORDER} MEMBER function_spec,]
[{FINAL| NOT FINAL} MEMBER function_spec,]
[{INSTANTIABLE| NOT INSTANTIABLE} MEMBER function_spec,]
[{MEMBER | STATIC} {subprogram_spec | call_spec}
[, {MEMBER | STATIC} {subprogram_spec | call_spec}]...]
) [{FINAL| NOT FINAL}] [ {INSTANTIABLE| NOT INSTANTIABLE}];
[CREATE [OR REPLACE] TYPE BODY type_name {IS | AS}
{ {MAP | ORDER} MEMBER function_body;
| {MEMBER | STATIC} {subprogram_body | call_spec};}
[{MEMBER | STATIC} {subprogram_body | call_spec};]...
END;]
The AUTHID clause determines whether all member methods execute with the privileges of their definer (the default) or invoker, and whether their unqualified references to schema objects are resolved in the schema of the definer or invoker. For more information, see “Invoker Rights Versus Definer Rights”.
Restrictions
- records cannot be attributes
- no private attribute or method
Management
Create
Minimal
Create the object, minimal a member and a method must be declared
CREATE OR REPLACE TYPE address_typ AS OBJECT (
street VARCHAR2(30),
city VARCHAR2(20),
postal_code VARCHAR2(6),
start_date DATE NOT NULL := sysdate,
MAP MEMBER FUNCTION get_city RETURN VARCHAR2,
MEMBER PROCEDURE display_address ( SELF IN OUT NOCOPY address_typ )
);
/
CREATE OR REPLACE TYPE BODY address_typ AS
MAP MEMBER FUNCTION get_city RETURN VARCHAR2 IS
BEGIN
RETURN city;
END;
MEMBER PROCEDURE display_address ( SELF IN OUT NOCOPY address_typ ) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(street || ' - ' || city || ' - ' || postal_code);
END;
END;
/
Not Null
Not null is not permitted in the object type declaration. You can handle it by setting a default value in the constructor.
Example:
CONSTRUCTOR FUNCTION my_obj(date_update date default sysdate) RETURN SELF AS RESULT,
Otherwise, you will get:
PLS-00218: a variable declared NOT NULL must have an initialization assignment
Operations
PLSQL
Manipulation
DECLARE
address address_typ :=NULL;
BEGIN
-- Constructor
address := address_typ (NULL, NULL, NULL);
-- Add one object
address.street := 'Street1';
address.city := 'City1';
address.postal_code := 'PosCo1';
DBMS_OUTPUT.PUT_LINE(address.street || ' - ' || address.city || ' - ' || address.postal_code); -- display details
address := address_typ ('Street2', 'City2', 'PosCo2');
address.display_address();
END;
/
Output :
Street1 - City1 - PosCo1
Street2 - City2 - PosCo2
PL/SQL procedure successfully completed.
SQL - Reference
REF are an hash object identifiers and help in SQL to make joins between object.
To pass a pointer (and not the full object), you may use the ref symbol.
create or replace type my_obj as object
(
secondObject ref my_second_obj,
...
See:
- The ref function is a sql function
Meta
The grammar and other information can be found in the following meta views.
- (ALL|USER)_TYPES
- (ALL|USER)_TYPE_ATTRS;
- (ALL|USER)_TYPE_METHODS
select * from ALL_TYPES;
select * from USER_TYPES;
Security
- Get the authorization (from a admin)
GRANT CREATE TYPE TO "userName" ;
Support
ORA-02303: cannot drop or replace a type with type or table dependents
Wen a type declare an other type and that you want to create it, you will get the above errors. To prevent the above, you may use the force keyword
create or replace type my_obj force as object ...
PLS-00382: expression is of wrong type
When using a ref declaration, your data type becomes a pointer data type and no more an object.
You can get it through the following:
attribute_obj my_type_obj;
procedure set_attribute( p_attribute_obj ref my_type_obj )
AS
BEGIN
-- p_attribute_obj is a ref but attribute_obj is an object
-- You get then a PLS-00382
-- To resolve it, change the declaration of attribute_obj
-- to attribute_obj ref my_type_obj;
attribute_obj := p_attribute_obj;
END set_attribute;