Returning an array from a procedure/ function

#1

I'm struggling to work out how to return an array(table) from a pl/sql
function/procedure. I have tried various methods using both functions
and procedures.

I currently have the following code:

* Package *

CREATE OR REPLACE PACKAGE MyPackage as
TYPE ARRAY IS TABLE OF VARCHAR2(250);
PROCEDURE MyProcedure(MyArray IN OUT ARRAY);
END;
/

CREATE OR REPLACE PACKAGE BODY MyPackage IS
PROCEDURE MyProcedure(MyArray IN OUT ARRAY) is
BEGIN
<snipped>
END;
END;
/

* Procedure Call *
DECLARE
TYPE ARRAY IS TABLE OF VARCHAR2(250);
MyArray ARRAY := LOGIN_AREA('','','','','','','','','','','','','', '','');
BEGIN
MySchema.MyPackage.MyProcedure(MyArray);
END;

***

Do excuse the poor quality of the above code. I haven't been doing
this very long. All suggestion and critism greatly recieved.

Cheers Simon

#2

"Simon Davies" <a_person_uk (AT) yahoo (DOT) co.uk> wrote

Quote:

I'm struggling to work out how to return an array(table) from a pl/sql
function/procedure. I have tried various methods using both functions
and procedures.

I currently have the following code:

* Package *

CREATE OR REPLACE PACKAGE MyPackage as
TYPE ARRAY IS TABLE OF VARCHAR2(250);
PROCEDURE MyProcedure(MyArray IN OUT ARRAY);
END;
/

CREATE OR REPLACE PACKAGE BODY MyPackage IS
PROCEDURE MyProcedure(MyArray IN OUT ARRAY) is
BEGIN
snipped
END;
END;
/

* Procedure Call *
DECLARE
TYPE ARRAY IS TABLE OF VARCHAR2(250);
MyArray ARRAY :=
LOGIN_AREA('','','','','','','','','','','','','', '','');
BEGIN
MySchema.MyPackage.MyProcedure(MyArray);
END;

***

Do excuse the poor quality of the above code. I haven't been doing
this very long. All suggestion and critism greatly recieved.

Cheers Simon

i follow the syntax, but what is the error your getting? or what is being
returned? are you printing out the
content of the PL/SQL table to see if the values are return?

im assuming your coming from an Object oriented backgroudn because

MyArray ARRAY := LOGIN_AREA('','','','','','','','','','','','','', '','');

as a constructor. its not necessary. Arrays are wrapped in Oracle.
Initialization is taken care of for you.

please provide more detail.

#3

"Ryan" <rgaffuri (AT) cox (DOT) net> wrote

Quote:

i follow the syntax, but what is the error your getting? or what is being
returned? are you printing out the
content of the PL/SQL table to see if the values are return?

im assuming your coming from an Object oriented backgroudn because

MyArray ARRAY := ARRAY('','','','','','','','','','','','','','','' );

as a constructor. its not necessary. Arrays are wrapped in Oracle.
Initialization is taken care of for you.

OK. So I now have the following declaration:

MyArray ARRAY;

Quote:

please provide more detail.

The error message I am getting is below:

ORA-06550: line 1, column 7544:
PLS-00306: wrong number or types of arguments in call to 'MYPROC'

The error is occuring on the page which call the function returning the array.

Cheers,

Simon.

#4

a_person_uk (AT) yahoo (DOT) co.uk (Simon Davies) wrote in message news:<6238724c.0307070148.b56dc11 (AT) posting (DOT) google.com>...

Quote:

"Ryan" <rgaffuri (AT) cox (DOT) net> wrote

im assuming your coming from an Object oriented backgroudn because

MyArray ARRAY := ARRAY('','','','','','','','','','','','','','','' );

as a constructor. its not necessary. Arrays are wrapped in Oracle.
Initialization is taken care of for you.

OK. I have a function which returns an array working. However the code
doesn't work when I use dynamically sized arrays (using
array.extend

. This is because the array being returned is a
different size to the array which I am assigning the result of the
function to. How can I get around this problem?

Cheers,

Simon.

varray is a static array
pl/sql table is a dynamic sparse array. However, you can still get
null pointer exceptions with it.. which is annoying.

are yo using a varray? switch to a pl/sql table. initializations is
virtually the same. it never needs to be extends. Its all wrapped. I
rarely see varray used and only when you want to store the array in a
table which you cant do with pl/sql tables.

this should fix your problem. if not provide a code snipped and the
error message.

im right your a c/c++ programmer right?

#5

rgaffuri (AT) cox (DOT) net (Ryan Gaffuri) wrote in message news:<1efdad5b.0307100339.2d5ee7e6 (AT) posting (DOT) google.com>...

Quote:

varray is a static array
pl/sql table is a dynamic sparse array. However, you can still get
null pointer exceptions with it.. which is annoying.

are yo using a varray? switch to a pl/sql table. initializations is
virtually the same. it never needs to be extends. Its all wrapped. I
rarely see varray used and only when you want to store the array in a
table which you cant do with pl/sql tables.

I am using a pl/sql table:

TYPE ARRAY IS TABLE OF VARCHAR2(250);

Quote:

this should fix your problem. if not provide a code snipped and the
error message.

So why if I try to assign the vale of one array to another do I
encounter problems unless the arrays have been initialised in the
manner I mentioned earlier in thread.

Quote:

im right your a c/c++ programmer right?

Yep.

Simon.

#6

padderz <member633 (AT) dbforums (DOT) com> wrote

Quote:

Confused yet?

OK, as of 8i there are THREE types of collection in PL/SQL: nested
tables, VARRAYs and INDEX-BY tables (formerly PL/SQL tables).

What you have declared (with TYPE ARRAY IS TABLE OF VARCHAR2 (250)) is a
nested table and you are correct that this must be initialised with (at
least) a null table for most uses. However unless you wish to access the
array directly in SQL (i.e. SELECT from it) or exchange the array with
an external language (e.g. Java) then it is probably easier to use an
INDEX-BY table, which is distinguished by adding the INDEX BY
BINARY_INTEGER (or alternatively VARCHAR2 (n) in 9.2 and above) to the
declaration. Hence your package would be declared something like...

CREATE OR REPLACE PACKAGE mypackage AS

TYPE array IS TABLE OF VARCHAR2(250)
INDEX BY BINARY_INTEGER;

PROCEDURE myprocedure (
myarray IN OUT array);

END;
/

CREATE OR REPLACE PACKAGE BODY mypackage IS

PROCEDURE myprocedure (
myarray IN OUT array) IS
BEGIN
myarray (1) := 'Some Value';
END;

END;
/

One last thing - when you call this procedure you must use the TYPE
declared in the package and not declare your own - the two types are not
considered the same. Hence your calling block might be...

DECLARE
myarray mypackage.array;
BEGIN
mypackage.myprocedure (myarray);
DBMS_OUTPUT.PUT_LINE (myarray (1));
END;
/

Does this help?

padderz is right. forgot abotu the index by binary integers and didnt
realize you were using two different types.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Returning an array from a procedure/ function

comp.databases.oracle.misc comp.databases.oracle.misc

Returning an array from a procedure/ function - 07-04-2003 , 10:46 AM

Re: Returning an array from a procedure/ function - 07-06-2003 , 08:52 PM

Re: Returning an array from a procedure/ function - 07-07-2003 , 03:30 AM

Re: Returning an array from a procedure/ function - 07-10-2003 , 06:39 AM

Re: Returning an array from a procedure/ function - 07-11-2003 , 03:05 AM

Re: Returning an array from a procedure/ function - 07-11-2003 , 10:18 AM