Re: oracle 9: collections syntax in proc?

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

Thanks for the feedback - I'll see if I can come up with some more examples for the article.

You probably need to create a nested table type in SQL, e.g:

CREATE OR REPLACE TYPE INTEGER_TT AS TABLE OF INTEGER; The difference between creating a type in SQL and declaring it in PL/SQL is that SQL can't use PL/SQL types, but a type created with CREATE TYPE in effect extends SQL.

Now you can use INTEGER_TT (or whatever) as a type in PL/SQL.

btw I think you made the right choice regarding varrays - they are not generally useful unless you are working with varray columns in a table or view. Received on Mon Jun 26 2006 - 12:43:54 CDT