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.

SQL> create type acct_obj as object (acct_num varchar2(20),
2 name1 varchar2(20)
3 )
4 /

Type created.

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

Type created.

SQL>
SQL> create 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>
SQL> create type addr_arr is VARRAY(6) OF addr_obj
2 /

Type created.

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

Type created.

SQL>
SQL> --======================================
SQL> --Package Creation
SQL> --======================================
SQL> CREATE 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>
SQL> CREATE 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.

SQL> declare
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_addr(1).name1, 'No Issuer'));
10 dbms_output.put_line('Return Code '||o_return_code(1));
11 end;
12 /
declare
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at "OPS$UBTDEV.ACCT_RETR", line 23
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 7

[Jan]

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_arr();
Jan