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  |
 |
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  |
 |
Michel Cadot
Messages: 54200
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
From your previous post:
Michel Cadot wrote on Fri, 25 January 2013 14:56Welcome 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 |
Solomon Yakobson
Messages: 1399
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 |
|
|
|
Goto Forum:
Current Time: Thu May 23 02:53:28 CDT 2013
Total time taken to generate the page: 0.30071 seconds
|
] 
Members
Search
Help
Register
Login
Home
