padderz <member633_at_dbforums.com> wrote in message news:<3099203.1057915892_at_dbforums.com>...
> 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.
Received on Fri Jul 11 2003 - 10:18:13 CDT
