How to pass array of strings in stored procedure (Oracle)?

shev · #1 (**permalink**) 07-30-03, 04:33

Hi all,

How to write a stored procedure in Oracle which reads an array of strings and then loops each element in the array?

Thanks in advance.

andrewst · #2 (**permalink**) 07-30-03, 07:18

Quote:

Originally posted by shev
Hi all,

How to write a stored procedure in Oracle which reads an array of strings and then loops each element in the array?

Thanks in advance.

There are lots of options, but here's a very simple example:

Code:

SQL> declare
  2    type array is table of varchar2(30) index by binary_integer;
  3    a array;
  4    procedure p( array_in array )
  5    is
  6    begin
  7      for i in 1..array_in.count loop
  8        dbms_output.put_line( array_in(i) );
  9      end loop;
 10    end;
 11  begin
 12    a(1) := 'Apple';
 13    a(2) := 'Banana';
 14    a(3) := 'Pear';
 15    p( a );
 16  end;
 17  /
Apple
Banana
Pear

PL/SQL procedure successfully completed.

shev · #3 (**permalink**) 07-31-03, 02:55

Thanks andrewst.

What other properties can be used besides "array_in.count"? Where can I find a list of them?

Also, is the type defined different from varray?

andrewst · #4 (**permalink**) 07-31-03, 06:25

Quote:

Originally posted by shev
Thanks andrewst.

What other properties can be used besides "array_in.count"? Where can I find a list of them?

Also, is the type defined different from varray?

You should study this part of the PL/SQL Reference:

http://technet.oracle.com/docs/produ...colls.htm#1059

Look for "Collection Methods" and "Choosing Which PL/SQL Collection Types to Use".

VARRAYS have a size limit, which index-by and nested tables don't. I find I tend to use index-by tables most of the time, mainly out of habit since they existed before all the other collection types.

You may also want to check out the book "Oracle PL/SQL Programming" by Steven Feuerstein (O'Reilly).

shev · #5 (**permalink**) 08-05-03, 00:02

Thanks andrewst.

I have further question on the PL/SQL collection type.

How can I check if the input index-by table is null or not?
I have looked into the documentation and I can only find ways to check nullity of nested table and varray but not index-by table. It simply adds the line "IF ... IS NULL THEN...". I have tried to apply it in my code but seems not working properly. Could you please give me some advice?

Also, what is the meaning of "atomically null"?

Please don't mind if I am asking simple question.

andrewst · #6 (**permalink**) 08-05-03, 05:14

Quote:

Originally posted by shev
Thanks andrewst.

I have further question on the PL/SQL collection type.

How can I check if the input index-by table is null or not?
I have looked into the documentation and I can only find ways to check nullity of nested table and varray but not index-by table. It simply adds the line "IF ... IS NULL THEN...". I have tried to apply it in my code but seems not working properly. Could you please give me some advice?

Also, what is the meaning of "atomically null"?

Please don't mind if I am asking simple question.

You mean the whole collection is empty, or is not empty but all values are NULL?

To test if it is empty you can say:

IF array.COUNT = 0 THEN ...

I don't know of any way to test whether all values in a non-empty index-by table are NULL, except by looping and testing each one.

Atomically null means that you can test the collection for null as an "atomic" item, i.e. IF collection IS NULL. Doesn't work for index-by tables.

shev · #7 (**permalink**) 08-05-03, 06:02

Thanks andrewst.

I am not clear about how to define if collection is empty or null.
Go back to the example you gave me.

Quote:

SQL> declare
2 type array is table of varchar2(30) index by binary_integer;
3 a array;
4 procedure p( array_in array )
5 is
6 begin
7 for i in 1..array_in.count loop
8 dbms_output.put_line( array_in(i) );
9 end loop;
10 end;
11 begin
12 a(1) := 'Apple';
13 a(2) := 'Banana';
14 a(3) := 'Pear';
15 p( a );
16 end;
17 /
Apple
Banana
Pear

PL/SQL procedure successfully completed.

If line 12 to 14 were omitted, shall I treat "a" as an empty collection?

Thank you.

andrewst · #8 (**permalink**) 08-05-03, 06:11

Quote:

Originally posted by shev
Thanks andrewst.

I am not clear about how to define if collection is empty or null.
Go back to the example you gave me.

If line 12 to 14 were omitted, shall I treat "a" as an empty collection?

Thank you.

Yes, it will be empty. a.COUNT will be zero, and no space will be allocated to the collection.

shev · #9 (**permalink**) 08-05-03, 06:13

Thanks a lot andrewst!

Internet Services

Web Development

Digital Marketing

Consumer Tech