Home » SQL & PL/SQL » SQL & PL/SQL » Re: ORA-06530: Reference to uninitialized composite (split from hijacked thread by bb)
Re: ORA-06530: Reference to uninitialized composite (split from hijacked thread by bb) [message #576252] Sat, 02 February 2013 00:23 Go to next message
Trinityr
Messages: 2
Registered: January 2013
Junior Member
Hi,

CREATE OR REPLACE TYPE ATTRIBUTE_OBJECT AS OBJECT
( 
ATTRIBUTE_NAME       VARCHAR2(2000),
ATTRIBUTE_VALUE      varchar2(2000)
);

CREATE OR REPLACE TYPE ATTRIBUTE_ARRAY
AS TABLE OF ATTRIBUTE_OBJECT;

CREATE OR REPLACE TYPE ATTRIBUTE_ARRAY_TABLE
AS TABLE OF ATTRIBUTE_ARRAY;

CREATE OR REPLACE PROCEDURE proc_test (
                                        sid varchar2,
                                        outp out attribute_array_table

                                      )

as
V_ARRAY ATTRIBUTE_ARRAY := ATTRIBUTE_ARRAY();
V_POSSERVICEIDKEY           VARCHAR2(20) := 'POSServiceID';
V_SERVICETYPEKEY            VARCHAR2(20) := 'CustName';
V_LOOP_NO                   NUMBER;
V_SERVICE_ID                 VARCHAR2(30);
v_orgname                   VARCHAR2(20);

i number;
CURSOR C is

select fi.item_code,org.organisation_name

from   fsb_item fi,opportunity opp , organisation org
where  fi.item_code = '091HYDE623016121351'[color=coral] -- hardcoded the input parameter for testing[/color]
and    fi.opportunity_id = opp.opportunity_id
and    opp.organisation_id = org.organisation_id;

BEGIN

 i :=0;

 OPEN C;
 LOOP
   FETCH C INTO  V_SERVICE_ID,v_orgname;

    V_ARRAY.EXTEND;
    i := i+1;
    V_ARRAY(i) := ATTRIBUTE_OBJECT(V_POSSERVICEIDKEY,V_SERVICE_ID); -- issue - 


    V_ARRAY.EXTEND;
     i := i+1;
    V_ARRAY(i) := ATTRIBUTE_OBJECT(V_SERVICETYPEKEY,v_orgname); -- issue -

    outp.EXTEND;
      V_LOOP_NO := V_LOOP_NO + 1;
      outp(V_LOOP_NO) := V_ARRAY;

      dbms_output.put_line('ARRAY TABLE COUNT : '||outp.COUNT);
  END LOOP;
 CLOSE C;

  END;


The output of the query is 2 rows of 2 columns each.
array_table(1) would be row1 with 2 columns - as an array
array_table(2) also the same.

When I display the count, of ATTRIBUTE_ARRAY_TABLE , it displays 2 ..

when I test using Soap UI,
In attribute_array_table , I have 2 arrays but each array showing only 1 value.. i.e the first value whichever I pass.. where I have written the comment as issue..

Pleas help : SOAP UI output displayed:

<SearchSDDOrderDetailsOutput>
<ResponseHeader>
<Status>0</Status>
<ErrorCode/>
<ErrorMessage/>
</ResponseHeader>
<OrderInformation>
<Attribute>
<Key>POSServiceID</Key>
<Value>091HYDE623016115664</Value>
</Attribute>
</OrderInformation>
<OrderInformation>
<Attribute>
<Key>POSServiceID</Key>
<Value>091HYDE623016115695</Value>

</Attribute>
</OrderInformation>
</SearchSDDOrderDetailsOutput>
</de:searchSDDOrderResponse>


As highlighted I should get another key value pair whic I passed intio Attribute_Array..

Please Help..


[mod-edit: code tags added by bb; next time please add them yourself;
split from thread http://www.orafaq.com/forum/t/54324/0/unread/43710/#msg_576254 ]

[Updated on: Sat, 02 February 2013 01:20] by Moderator

Report message to a moderator

Re: ORA-06530: Reference to uninitialized composite [message #576254 is a reply to message #576252] Sat, 02 February 2013 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 58489
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous post:

Michel Cadot wrote on Fri, 25 January 2013 14:56
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.


With any SQL or PL/SQL question, please, Post a working AND COMPLETE Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Use SQL*Plus and copy and paste your session, the WHOLE session, inline in test mode, no image, no attehced file.

Regards
Michel

[Updated on: Sat, 02 February 2013 01:10]

Report message to a moderator

Re: ORA-06530: Reference to uninitialized composite (split from hijacked thread by bb) [message #576283 is a reply to message #576252] Sat, 02 February 2013 06:34 Go to previous message
Solomon Yakobson
Messages: 1948
Registered: January 2010
Senior Member
There are many issues with your code. Infinite loop - you need EXIT WHEN C%NOTFOUND. After fixing it (I'll use emp table instead of your table since I don't have it):

CREATE OR REPLACE PROCEDURE proc_test (
                                        sid varchar2,
                                        outp out attribute_array_table
                                      )
as
V_ARRAY ATTRIBUTE_ARRAY := ATTRIBUTE_ARRAY();
V_POSSERVICEIDKEY           VARCHAR2(20) := 'POSServiceID';
V_SERVICETYPEKEY            VARCHAR2(20) := 'CustName';
V_LOOP_NO                   NUMBER;
V_SERVICE_ID                 VARCHAR2(30);
v_orgname                   VARCHAR2(20);
i number;
CURSOR C is
select empno,ename
from   emp;
BEGIN
 i :=0;
 OPEN C;
 LOOP
   FETCH C INTO  V_SERVICE_ID,v_orgname;
   EXIT WHEN C%NOTFOUND;
    V_ARRAY.EXTEND;
    i := i+1;
    V_ARRAY(i) := ATTRIBUTE_OBJECT(V_POSSERVICEIDKEY,V_SERVICE_ID);
    V_ARRAY.EXTEND;
     i := i+1;
    V_ARRAY(i) := ATTRIBUTE_OBJECT(V_SERVICETYPEKEY,v_orgname);
    outp.EXTEND;
      V_LOOP_NO := V_LOOP_NO + 1;
      outp(V_LOOP_NO) := V_ARRAY;
      dbms_output.put_line('ARRAY TABLE COUNT : '||outp.COUNT);
  END LOOP;
 CLOSE C;
  END;
/


Now:

SQL> CREATE OR REPLACE PROCEDURE proc_test (
  2                                          sid varchar2,
  3                                          outp out attribute_array_table
  4                                        )
  5  as
  6  V_ARRAY ATTRIBUTE_ARRAY := ATTRIBUTE_ARRAY();
  7  V_POSSERVICEIDKEY           VARCHAR2(20) := 'POSServiceID';
  8  V_SERVICETYPEKEY            VARCHAR2(20) := 'CustName';
  9  V_LOOP_NO                   NUMBER;
 10  V_SERVICE_ID                 VARCHAR2(30);
 11  v_orgname                   VARCHAR2(20);
 12  i number;
 13  CURSOR C is
 14  select empno,ename
 15  from   emp;
 16  BEGIN
 17   i :=0;
 18   OPEN C;
 19   LOOP
 20     FETCH C INTO  V_SERVICE_ID,v_orgname;
 21     EXIT WHEN C%NOTFOUND;
 22      V_ARRAY.EXTEND;
 23      i := i+1;
 24      V_ARRAY(i) := ATTRIBUTE_OBJECT(V_POSSERVICEIDKEY,V_SERVICE_ID);
 25      V_ARRAY.EXTEND;
 26       i := i+1;
 27      V_ARRAY(i) := ATTRIBUTE_OBJECT(V_SERVICETYPEKEY,v_orgname);
 28      outp.EXTEND;
 29        V_LOOP_NO := V_LOOP_NO + 1;
 30        outp(V_LOOP_NO) := V_ARRAY;
 31        dbms_output.put_line('ARRAY TABLE COUNT : '||outp.COUNT);
 32    END LOOP;
 33   CLOSE C;
 34    END;
 35  /

Procedure created.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2      v_outp attribute_array_table := attribute_array_table();
  3  BEGIN
  4      proc_test('A',v_outp);
  5  END;
  6  /
DECLARE
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at "SCOTT.PROC_TEST", line 28
ORA-06512: at line 4


SQL>


And error clearly points to outp.EXTEND, not to V_ARRAY(i) := ATTRIBUTE_OBJECT(V_SERVICETYPEKEY,v_orgname) as you stated. So what's wrong, parameter v_outp I passed is initialized collection, right? But look at parameter mode. It is OUT which means we are assembling collection from scratch to pass it back to caller. So, if you want SP to add elements to collection passed as argument you must change parameter mode to IN OUT (just don't forget to ckeck if IN OUT collection passed to SP is initialized and initialize it if it isn't). Otherwise you need to initialize collection in SP:

CREATE OR REPLACE PROCEDURE proc_test (
                                        sid varchar2,
                                        outp out attribute_array_table
                                      )
as
V_ARRAY ATTRIBUTE_ARRAY := ATTRIBUTE_ARRAY();
V_POSSERVICEIDKEY           VARCHAR2(20) := 'POSServiceID';
V_SERVICETYPEKEY            VARCHAR2(20) := 'CustName';
V_LOOP_NO                   NUMBER;
V_SERVICE_ID                 VARCHAR2(30);
v_orgname                   VARCHAR2(20);
i number;
CURSOR C is
select empno,ename
from   emp;
BEGIN
 i :=0;
 outp := attribute_array_table();
 OPEN C;
 LOOP
   FETCH C INTO  V_SERVICE_ID,v_orgname;
   EXIT WHEN C%NOTFOUND;
    V_ARRAY.EXTEND;
    i := i+1;
    V_ARRAY(i) := ATTRIBUTE_OBJECT(V_POSSERVICEIDKEY,V_SERVICE_ID);
    V_ARRAY.EXTEND;
     i := i+1;
    V_ARRAY(i) := ATTRIBUTE_OBJECT(V_SERVICETYPEKEY,v_orgname);
    outp.EXTEND;
      V_LOOP_NO := V_LOOP_NO + 1;
      outp(V_LOOP_NO) := V_ARRAY;
      dbms_output.put_line('ARRAY TABLE COUNT : '||outp.COUNT);
  END LOOP;
 CLOSE C;
  END;
/

Procedure created.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2      v_outp attribute_array_table := attribute_array_table();
  3  BEGIN
  4      v_outp.extend;
  5      proc_test('A',v_outp);
  6  END;
  7  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: NULL index table key value
ORA-06512: at "SCOTT.PROC_TEST", line 31
ORA-06512: at line 5


SQL>


So we fixed one issue but ran into another. Variable V_LOOP_NO is not initialized. Finally, after fixing it:

CREATE OR REPLACE PROCEDURE proc_test (
                                        sid varchar2,
                                        outp out attribute_array_table
                                      )
as
V_ARRAY ATTRIBUTE_ARRAY := ATTRIBUTE_ARRAY();
V_POSSERVICEIDKEY           VARCHAR2(20) := 'POSServiceID';
V_SERVICETYPEKEY            VARCHAR2(20) := 'CustName';
V_LOOP_NO                   NUMBER := 0;
V_SERVICE_ID                 VARCHAR2(30);
v_orgname                   VARCHAR2(20);
i number;
CURSOR C is
select empno,ename
from   emp;
BEGIN
 i :=0;
 outp := attribute_array_table();
 OPEN C;
 LOOP
   FETCH C INTO  V_SERVICE_ID,v_orgname;
   EXIT WHEN C%NOTFOUND;
    V_ARRAY.EXTEND;
    i := i+1;
    V_ARRAY(i) := ATTRIBUTE_OBJECT(V_POSSERVICEIDKEY,V_SERVICE_ID);
    V_ARRAY.EXTEND;
     i := i+1;
    V_ARRAY(i) := ATTRIBUTE_OBJECT(V_SERVICETYPEKEY,v_orgname);
    outp.EXTEND;
      V_LOOP_NO := V_LOOP_NO + 1;
      outp(V_LOOP_NO) := V_ARRAY;
      dbms_output.put_line('ARRAY TABLE COUNT : '||outp.COUNT);
  END LOOP;
 CLOSE C;
  END;
/

Procedure created.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2      v_outp attribute_array_table := attribute_array_table();
  3  BEGIN
  4      v_outp.extend;
  5      proc_test('A',v_outp);
  6  END;
  7  /
ARRAY TABLE COUNT : 1
ARRAY TABLE COUNT : 2
ARRAY TABLE COUNT : 3
ARRAY TABLE COUNT : 4
ARRAY TABLE COUNT : 5
ARRAY TABLE COUNT : 6
ARRAY TABLE COUNT : 7
ARRAY TABLE COUNT : 8
ARRAY TABLE COUNT : 9
ARRAY TABLE COUNT : 10
ARRAY TABLE COUNT : 11
ARRAY TABLE COUNT : 12
ARRAY TABLE COUNT : 13
ARRAY TABLE COUNT : 14

PL/SQL procedure successfully completed.

SQL>


SY.

[Updated on: Sat, 02 February 2013 06:40]

Report message to a moderator

Previous Topic: Upper function for long type
Next Topic: Logic to find table names and data in oracle
Goto Forum:
  


Current Time: Tue Jul 22 19:11:11 CDT 2014

Total time taken to generate the page: 0.08354 seconds