URGENT: VARRAY as Input/Output variable

From: KVN Chary <kvnchary_at_yahoo.com>
Date: 2 Sep 2003 16:16:14 -0700
Message-ID: <b6ec1ff3.0309021516.768f14d3_at_posting.google.com>

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

Received on Wed Sep 03 2003 - 01:16:14 CEST