URGENT: VARRAY as Input/Output variable

[KVN Chary]

I wrote a small package and it successfully compiled. When I test the
package it was giving an error. Please look into the below code. Can
anyone written Varrays as input and output variables in a procedure.
I need to assign values directly to the varray, does it possible? How
?

Thanks in advance.

Brief explanation:-
Java program call the below package by giving two input parameters one
is array another is number. This package should return two arrays one
with address information and another with error codes if any address
not found.

SQLcreate type acct_obj as object (acct_num varchar2(20),
2 name1 varchar2(20)
3 )
4 /

Type created.

SQL>
SQLcreate type acct_arr is VARRAY(6) OF acct_obj
2 /

Type created.

SQL>
SQLcreate type addr_obj as object (
2 name1 varchar2(20),
3 addr_line1 varchar2(30),
4 addr_line2 varchar2(30),
5 addr_line3 varchar2(30),
6 city varchar2(20),
7 state varchar2(2),
8 zip varchar2(7)
9 )
10 /

Type created.

SQL>
SQLcreate type addr_arr is VARRAY(6) OF addr_obj
2 /

Type created.

SQL>
SQLcreate type return_arr is VARRAY(6) of number(6)
2 /

Type created.

SQL>
SQL--=============== =============== ========
SQL--Package Creation
SQL--=============== =============== ========
SQLCREATE OR REPLACE PACKAGE acct_retr AS
2
3 PROCEDURE find_addr (
4 i_number IN NUMBER,
5 i_name1 IN acct_arr,
6 o_addr OUT addr_arr,
7 o_return_code OUT return_arr
8 );
9 END acct_retr;
10 /

Package created.

SQL>
SQLCREATE OR REPLACE PACKAGE BODY acct_retr AS
2
3 error_in_input NUMBER(2) := 2;
4
5 PROCEDURE find_addr (
6 i_number IN NUMBER,
7 i_name1 IN acct_arr,
8 o_addr OUT addr_arr,
9 o_return_code OUT return_arr
10 )
11 IS
12 Invalid_range EXCEPTION;
13 BEGIN
14 IF (NVL(i_number,0 ) <= 0 or i_number 6) THEN
15 RAISE invalid_range;
16 END IF;
17 --
18 -- Retrieve from database and send account address
19 -- array to the calling program with if any error codes
20 --
21 EXCEPTION
22 WHEN invalid_range THEN
23 o_return_code(1 ) := error_in_input;
24 o_addr := NULL;
25 END find_addr;
26
27 END acct_retr;
28 /

Package body created.

SQLdeclare
2 i_number NUMBER(2):= NULL;
3 i_name1 acct_arr;
4 o_addr addr_arr;
5 o_return_code return_arr;
6 begin
7 acct_retr.find_ addr(i_number, i_name1,
8 o_addr, o_return_code);
9 dbms_output.put _line(NVL(o_add r(1).name1, 'No Issuer'));
10 dbms_output.put _line('Return Code '||o_return_cod e(1));
11 end;
12 /
declare
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at "OPS$UBTDEV.ACC T_RETR", line 23
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 7

[Jan]

Re: URGENT: VARRAY as Input/Output variable

before you start to do something with collections in your
procedure/function, you should initialize that object.
E.g. for your OUT parameter o_addr, you should put there:

o_addr:=addr_ar r();


Jan