Home » SQL & PL/SQL » SQL & PL/SQL » Unable to capture the parameter values from a PL/SQL procedure
icon5.gif  Unable to capture the parameter values from a PL/SQL procedure [message #324280] Sun, 01 June 2008 07:19 Go to next message
mohanraj.g@polaris.co.in
Messages: 1
Registered: June 2008
Location: chennai
Junior Member
hi.

i'm trying to capture the parameter values of a PL/SQL procedure by calling inside a anonymous block but i'm getting a "reference to uninitialized collection error" ORA-06531.

Please help me regarding.

i'm using following block for calling the procedure.

declare
err_cd varchar2(1000);
err_txt VARCHAR2(5000);
no_of_recs number;
out_sign_tab search_sign_tab_type:=search_sign_tab_type(search_sign_type(NULL,NULL,NULL,NULL,NULL));
cntr_var number:=0;
begin
rt843pq('DWS','3000552485',out_sign_tab,no_of_recs,err_cd,err_txt);
dbms_output.put_line('The error is ' ||err_cd);
dbms_output.put_line('The error is ' ||err_txt);
dbms_output.put_line('The cntr is ' ||cntr_var);
for incr in 1 .. OUT_SIGN_TAB.count
loop
cntr_var := cntr_var + 1 ;
Dbms_output.put_line(OUT_SIGN_TAB(incr).ref_no||','||OUT_SIGN_TAB(incr).ciref_no||','||OUT_SIGN_TAB(incr).ac_no||','||OUT_SIGN_TAB(in cr).txn_type||','||OUT_SIGN_TAB(incr).objid);
end loop;
end;
/


Error is thrown on "for incr in 1 .. OUT_SIGN_TAB.count" this line

Following is some related information.

the 3rd parameter of the procedure is a out parameter. it is a type of a PL/SQL table (SEARCH_SIGN_TAB_TYPE) which is available in database as follows.


TYPE "SEARCH_SIGN_TAB_TYPE" IS TABLE OF SEARCH_SIGN_TYPE

TYPE "SEARCH_SIGN_TYPE" AS OBJECT
(ref_no VARCHAR2(22),
ciref_no VARCHAR2(352),
ac_no VARCHAR2(22),
txn_type VARCHAR2(301),
objid VARCHAR2(1024))............

Re: Unable to capture the parameter values from a PL/SQL procedure [message #324281 is a reply to message #324280] Sun, 01 June 2008 08:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Then, always post your Oracle version (4 decimals).

In the end, if it is an out parameter why do you initialize it here?
As you don't show what you do inside the called procedure we can't say if there you initialize it, it seems you don't.

Regards
Michel

Re: Unable to capture the parameter values from a PL/SQL procedure [message #324540 is a reply to message #324280] Tue, 03 June 2008 01:00 Go to previous messageGo to next message
ajay2301
Messages: 2
Registered: June 2008
Junior Member
Initialize out_sign_tab in begin block.
Re: Unable to capture the parameter values from a PL/SQL procedure [message #324541 is a reply to message #324540] Tue, 03 June 2008 01:06 Go to previous message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ajay2301 wrote on Tue, 03 June 2008 08:00
Initialize out_sign_tab in begin block.

Wrong, it does not matter it is initialized in declare or code section.
As we have no answer from OP, the error likely comes from what I posted: variable is not initialized in the called procedure.

Regards
Michel

Previous Topic: copy data from excel
Next Topic: NESTED TABLE in table column
Goto Forum:
  


Current Time: Thu Dec 08 04:01:46 CST 2016

Total time taken to generate the page: 0.11958 seconds