URGENT: VARRAY as Input/Output variable

From: KVN Chary <kvnchary_at_yahoo.com>
Date: 2 Sep 2003 16:03:59 -0700
Message-ID: <b6ec1ff3.0309021503.17efbe48_at_posting.google.com>

I'm facing one problem with Varrays. I have to use Varrays as input and output parameters in a procedure. When I assign a value to Varray, Oracle errored out as and asking for Initialize the Varray.

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 fill address array and send account address
 19 -- array to the calling program
 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

Thanks for all helping me. Received on Wed Sep 03 2003 - 01:03:59 CEST