Re: oracle 9: collections syntax in proc?

From: Steve Howard <stevedhoward_at_gmail.com>
Date: 26 Jun 2006 09:38:55 -0700
Message-ID: <1151339935.320883.202000@b68g2000cwa.googlegroups.com>

matt_at_mailinator.com wrote:
> hello,
>
> i am new to the oracle world, and am working on a project assigned to
> me.
>
> i have a proc that takes in a bunch of normal strings & numbers
> parameters. now, it is supposed to take in a collection representing a
> list of IDs. if this were a CSV list, it would look like: "34,56,102",
> etc -- a variable-length list of IDs.
>
> ive found these two articles on using collections:
>
> http://www.williamrobertson.net/documents/comma_separated.html
> http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10799/adobjcol.htm
>
> ...which sound cool. but as a newbie, i just dont see enough examples
> of how (exactly) to do this.
>
> sounds like a varray is out, because my list length is variable &
> unknown. that leaves a nested table. to that end william robertson
> says: "The simplest solution is to change your procedure's parameter
> list so that it is by definition passed one." ok... how do i do this?
>
> ive tried things like:
>
> PROCEDURE UpdateFurinishedEquipmentTag (
> p_comments IN VARCHAR2, --typical param
> p_IDCollection IN TABLE OF INTEGER
>
> ...
> )
>
> ....but my compiler (toad) didnt like that:
>
> PLS-00103: Encountered the symbol "TABLE" when expecting one of the
> following:
>
> out <an identifier> <a double-quoted delimited-identifier>
> LONG_ double ref char time timestamp interval date bina
>
>
>
> can anyone help a n00b out?
>
> thanks,
> matt

Hi Matt,

To fix your immediate syntax problem, you need to create your own type, and pass that. In your package spec, something like...

t_IDCollection IS TABLE OF INTEGER INDEX BY BINARY INTEGER;

...and in your proc definition...

p_IDCollection IN t_IDCollection

Below is a simple example...

SQL> create or replace package t626_p is   2 type t_IDCollection is table of integer index by binary_integer;   3 procedure p_imp (p_in in smallint);   4 p_out t_IDCollection;
  5 procedure p_show_values(p_in in t_IDCollection);   6 end;
  7 /

Package created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> create or replace package body t626_p is   2 procedure p_imp(p_in in smallint) is   3 begin

  4      for i in 1..10 loop
  5        p_out(i) := p_in * i;
  6      end loop;
  7      p_show_values(p_out);

  8 end;
  9
 10 procedure p_show_values(p_in in t_IDCollection) is  11 begin

 12      for i in 1..10 loop
 13        dbms_output.put_line(p_in(i));
 14      end loop;

 15 end;
 16 end;
 17 /

Package body created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> exec t626_p.p_imp(10);
10
20
30
40
50
60
70
80
90
100

PL/SQL procedure successfully completed.

SQL> Regards,

Steve Received on Mon Jun 26 2006 - 11:38:55 CDT