Re: oracle 9: collections syntax in proc?

From: William Robertson <williamr2019_at_googlemail.com>
Date: 27 Jun 2006 01:20:06 -0700
Message-ID: <1151396405.981746.10770@75g2000cwc.googlegroups.com>

William Robertson wrote:
> 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.

Reading that back, I'm not sure I made the point that instead of this:

    PROCEDURE UpdateFurinishedEquipmentTag (

        p_idcollection TABLE OF INTEGER  -- Invalid
        ...

    )

you would use this:

    PROCEDURE UpdateFurinishedEquipmentTag (

        p_idcollection INTEGER_TT  -- Your new type
        ...

    ) Received on Tue Jun 27 2006 - 03:20:06 CDT