Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56

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

    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.
    Cheers,
    Shev

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

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

    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.

  3. #3
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56

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

    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?
    Last edited by shev; 07-31-03 at 03:59.
    Cheers,
    Shev

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

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

    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).

  5. #5
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56

    Smile

    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.
    Cheers,
    Shev

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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.

  7. #7
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56
    Thanks andrewst.

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

    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.
    Cheers,
    Shev

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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.

  9. #9
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56

    Thumbs up Thanks

    Thanks a lot andrewst!
    Cheers,
    Shev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •