471,083 Members | 1,211 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,083 software developers and data experts.

URGENT: VARRAY as Input/Output variable

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
Jul 19 '05 #1
1 7339
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
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by PedroSilva | last post: by
8 posts views Thread by uc_sk | last post: by
reply views Thread by KVN Chary | last post: by
4 posts views Thread by Gregory Edigaroff | last post: by
1 post views Thread by dasilva109 | last post: by
3 posts views Thread by Tony Marston | last post: by
77 posts views Thread by Hans Schneider | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.