Re: use an array as input parameter for procedure

On Jun 23, 11:04 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "Mariano" <mariano.calan..._at_gmail.com> a écrit dans le message de news: 1182606785.877789.107..._at_m36g2000hse.googlegroups.com...
> |I need to create a procedure that accept as input parameter an array
> | of integer, how can realize it?
> |
> | PROCEDURE add_allPaz( ??? ) AS
> | BEGIN
> | // understand how many elements there are in array
> | END;
> |
> | What should I use at place of ???
> |
>
> SQL> create or replace type my_array is table of number
> 2 /
>
> Type created.
>
> SQL> create or replace procedure p (p my_array)
> 2 is
> 3 begin
> 4 for i in p.first..p.last loop
> 5 dbms_output.put_line('p('||i||')='||p(i));
> 6 end loop;
> 7 end;
> 8 /
>
> Procedure created.
>
> SQL> exec p(my_array(1,4,7,19,5));
> p(1)=1
> p(2)=4
> p(3)=7
> p(4)=19
> p(5)=5
>
> PL/SQL procedure successfully completed.
>
> Regards
> Michel Cadot

To make passing an array between stored procedures or packaged routine it is normal to declare the array type in a package header so that it can just be referenced in the routines that will need to use the structure.

Oracle provides several pre-defined array type definitions with the dbms_utility package including

type uncl_array IS TABLE OF VARCHAR2(227) INDEX BY BINARY_INTEGER;

type name_array IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;

type dblink_array IS TABLE OF VARCHAR2(128) INDEX BY BINARY_INTEGER;

TYPE index_table_type IS TABLE OF BINARY_INTEGER INDEX BY BINARY_INTEGER; TYPE number_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

Other types are provided in other supplied packages and where suitable these predefined types should be used in your code to help increase clarity of the code through the use of standard structures.

HTH -- Mark D Powell -- Received on Sat Jun 23 2007 - 11:01:18 CDT