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 ???

"Mariano" <mariano.calandra@gmail.com> a écrit dans le message de news: 1182606785.877789.107950@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

On Jun 23, 11:04 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "Mariano" <mariano.calan...@gmail.com> a écrit dans le message de news:1182606785.877789.107...@m36g2000hse.googlegr oups.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 --