PL/SQL - Object datatype

> Procedural Languages > PL/SQL - (Procedure Language|PL) SQL

1 - About

The Object datatype in PL/SQL

Disambiguation: See Oracle Database - Objects

Advertising

3 - 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".

4 - Restrictions

  • records cannot be attributes
  • no private attribute or method

5 - Management

5.1 - Create

5.1.1 - 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;
/

5.1.2 - 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
Advertising

5.2 - Operations

5.2.1 - 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.

5.2.2 - 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:

5.3 - 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;
Advertising

5.4 - Security

  • Get the authorization (from a admin)
GRANT CREATE TYPE TO "userName" ;

6 - Support

6.1 - 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  ...

6.2 - 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;

7 - Documentation / Reference

lang/plsql/object.txt · Last modified: 2017/09/06 22:18 by gerardnico