Re: Returning an array from a procedure/ function

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?

--
Posted via http://dbforums.com