I found out just using the EXTEND with parameters to copy "x" element and
then reference the copied element works fine.
"Jeff Smith" <jsmit234_at_ford.com> wrote in message
news:b3d8lr$7d318_at_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 Mar 24 2003 - 10:59:18 CST
