Oracle user defined object types

From: Jeff Smith <jsmit234_at_ford.com>
Date: Mon, 24 Feb 2003 09:02:35 -0500
Message-ID: <b3d8lr$7d318@eccws12.dearborn.ford.com>

I have a question regarding using oracle's user-defined object types.

I create an object type of TABLE. I want to use table instead of Varray because
I don't want to be forced to use dense arrays.

I then build another object which is a table of the above created table. This is now an object of a collection, and is exactly what I want.

However, my problem is that I insert into the object just fine, if I do it all at once. If I insert 2 rows into the collection object, I have no problem.

But if I want to exend the object, and then attempt add a 3rd row, after I have
already inserted the original 2 rows, it indicates that there it is out of range.
However, using EXTEND seems to work because the object.count method reports that there are 3 elements.

Does anyone know what I am doing wrong, or is this not possible?

Oracle 8i version: 8.1.7.3.0

SQL> CREATE OR REPLACE TYPE objTable AS OBJECT(

  2     STATUSID VARCHAR2(8),
  3     ID_FLG   VARCHAR2(1)

4 )
5 /

Type created.

SQL> CREATE or replace TYPE StatusType AS TABLE OF objTable; 2 /

Type created.

SQL> SQL> DECLARE
  2 TMPJEFFOBJ StatusType;
  3 stmp varchar2(8);
  4
  5 BEGIN
  6
  7 TMPJEFFOBJ := StatusType( objTable('ID', 'Y'), objTable('BB',
'C') );

  8 dbms_output.put_line( 'Original count: ' || to_char(tmpjeffobj.count) );

  9     DBMS_OUTPUT.PUT_LINE( tmpjeffobj(2).STATUSID );
 10     dbms_output.put_line( 'After attempting to change element 2 count: '

|| to_char(tmpjeffobj.
ount) );
11

 12     tmpjeffobj.extend;  /* THIS ADDS AN ELEMENT */
 13     dbms_output.put_line( 'NEW COUNT: ' || to_char(tmpjeffobj.count) );
 14     --tmpjeffobj(3).ID_FLG := 'Y';

15
16 end;
17 /
Original count: 2
BB
After attempting to change element 2 count: 2 NEW COUNT: 3 PL/SQL procedure successfully completed.

Works fine, but now trying to initialize the new element throws error:

SQL> DECLARE
  2 TMPJEFFOBJ StatusType;
  3 stmp varchar2(8);
  4
  5 BEGIN
  6
  7 TMPJEFFOBJ := StatusType( objTable('ID', 'Y'), objTable('BB',
'C') );

  8 dbms_output.put_line( 'Original count: ' || to_char(tmpjeffobj.count) );

  9     DBMS_OUTPUT.PUT_LINE( tmpjeffobj(2).STATUSID );
 10     dbms_output.put_line( 'After attempting to change element 2 count: '

|| to_char(tmpjeffobj.c
ount) );
11

 12     tmpjeffobj.extend;  /* THIS ADDS AN ELEMENT */
 13     dbms_output.put_line( 'NEW COUNT: ' || to_char(tmpjeffobj.count) );
 14     tmpjeffobj(3).ID_FLG := 'Y';

15
16 end;
17 /
Original count: 2
BB
After attempting to change element 2 count: 2 NEW COUNT: 3
DECLARE
*
ERROR at line 1:
ORA-06530: Reference to uninitialized composite ORA-06512: at line 14

Thanks in advance,
Jeff Received on Mon Feb 24 2003 - 08:02:35 CST