MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

How to use object types?

with 5 comments

A tale of Oracle SQL object types, their constructors, and how you use them. This demonstrates what you can and can’t do and gives brief explanations about why.

The following creates a base SAMPLE_OBJECT data type and a sample_table
collection of the base SAMPLE_OBJECT data type.

CREATE OR REPLACE TYPE sample_object IS OBJECT
(id       NUMBER
,name     VARCHAR2(30));
/
 
CREATE OR REPLACE TYPE sample_table IS TABLE OF sample_object;
/

If the base SAMPLE_OBJECT data type were a Java object, the default constructor of an empty call parameter list would allow you to construct an instance variable. This doesn’t work for an Oracle object type because the default constructor is a formal parameter list of the object attributes in the positional order of their appearance in the declaration statement.

The test case on this concept is:

1
2
3
4
5
6

DECLARE
  lv_object_struct SAMPLE_OBJECT := sample_object();
BEGIN
  NULL;
END;
/

Running the program raises the following exception, which points to the object instance constructor from line 2 above:

  lv_object_struct SAMPLE_OBJECT := sample_object();
                                    *
ERROR at line 2:
ORA-06550: line 2, column 37:
PLS-00306: wrong number or types of arguments in call to 'SAMPLE_OBJECT'
ORA-06550: line 2, column 20:
PL/SQL: Item ignored

Changing the instantiation call to the Oracle design default, two null values let you create
an instance of the SAMPLE_OBJECT type. The following shows that concept, which works when the base object type allows null values.

1
2
3
4
5
6

DECLARE
  lv_object_struct SAMPLE_OBJECT := sample_object(NULL, NULL);
BEGIN
  NULL;
END;
/

If you want to have a null parameter constructor for an object type, you must implement a type and type body with an overloaded no argument constructor, like this:

1
2
3
4
5

CREATE OR REPLACE TYPE sample_object IS OBJECT
( id       NUMBER
, name     VARCHAR2(30)
, CONSTRUCTOR FUNCTION sample_object RETURN SELF AS RESULT);
/
1
2
3
4
5
6
7
8
9

CREATE OR REPLACE TYPE BODY sample_object IS
  CONSTRUCTOR FUNCTION sample_object RETURN SELF AS RESULT IS
    sample_obj SAMPLE_OBJECT := sample_object(NULL,NULL);
  BEGIN
    SELF := sample_obj;
    RETURN;
  END sample_object;
END;
/

Unlike Java, the addition of an overloaded constructor doesn’t drop the default constructor. You can also create a single parameter constructor that leverages the sequence like this:

1
2
3
4
5
6

CREATE OR REPLACE TYPE sample_object IS OBJECT
( id       NUMBER
, name     VARCHAR2(30)
, CONSTRUCTOR FUNCTION sample_object RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION sample_object (pv_name VARCHAR2) RETURN SELF AS RESULT);
/
1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE OR REPLACE TYPE BODY sample_object IS
  CONSTRUCTOR FUNCTION sample_object RETURN SELF AS RESULT IS
    sample_obj SAMPLE_OBJECT := sample_object(sample_object_id.NEXTVAL,NULL);
  BEGIN
    SELF := sample_obj;
  END sample_object;
  CONSTRUCTOR FUNCTION sample_object (pv_name VARCHAR2) RETURN SELF AS RESULT IS
    sample_obj SAMPLE_OBJECT := sample_object(sample_object_id.NEXTVAL,pv_name);
  BEGIN
    SELF := sample_obj;
    RETURN;
  END sample_object;
END;
/

You can test the final object type and body with this anonymous block of code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

SET SERVEROUTPUT ON SIZE UNLIMITED
 
DECLARE
  lv_object_struct1 SAMPLE_OBJECT := sample_object();
  lv_object_struct2 SAMPLE_OBJECT := sample_object('User Name');
  lv_object_struct3 SAMPLE_OBJECT := sample_object(1001,'User Name');
BEGIN
  dbms_output.put_line('lv_object_struct1.id   ['||lv_object_struct1.id||']');
  dbms_output.put_line('lv_object_struct1.name ['||lv_object_struct1.name||']');
  dbms_output.put_line('lv_object_struct2.id   ['||lv_object_struct2.id||']');
  dbms_output.put_line('lv_object_struct2.name ['||lv_object_struct2.name||']');
  lv_object_struct2.name := 'Changed Name';
  dbms_output.put_line('lv_object_struct2.id   ['||lv_object_struct2.id||']');
  dbms_output.put_line('lv_object_struct2.name ['||lv_object_struct2.name||']');
  dbms_output.put_line('lv_object_struct3.id   ['||lv_object_struct3.id||']');
  dbms_output.put_line('lv_object_struct3.name ['||lv_object_struct3.name||']');
END;
/

It prints to console:

lv_object_struct1.id   [1]
lv_object_struct1.name []
lv_object_struct2.id   [2]
lv_object_struct2.name [User Name]
lv_object_struct2.id   [2]
lv_object_struct2.name [Changed Name]
lv_object_struct3.id   [1001]
lv_object_struct3.name [User Name]

Hope this helps those looking for a quick syntax example and explanation.

Written by maclochlainn

February 14th, 2012 at 8:14 pm

Posted in Oracle,Oracle 11g,Oracle XE,pl/sql,sql

«
»