Re: PL/SQL Cursors and Parameters

In article <959bve$fp0$1_at_nnrp1.deja.com>,   dennishancy_at_eaton.com wrote:
> In my PL/SQL procedure, I have a varchar2 variable called orgList, and
> populate it with a string that looks like the following:
>
> ('0789','1132,'1126','1131')
>
> Later, I pass orgList to a cursor as a parameter. My intent is to do
> something like the following:
>
> cursor someCursor(orgList varchar2) is
> select someField
> from someTable
> where organization in orgList;
>
> Also have a line like this declared:
>
> someVariable someCursor%ROWTYPE;
>
> When I compile this, I get this list of errors:
>
> PLS-00103: Encountered the symbol "ORGLIST" when expecting one of the
> following: (
>
> (-- referring to the line "where organization in orgList --)
>
> PLS-00103: Encountered the symbol SOMEVARIABLE
>
> (-- referrring to the line someVariable someCursor%ROWTYPE --)
>
> Any idea what I'm doing wrong? Is there a better solution? Thanks.
>
> Dennis Hancy
> Eaton Corporation
> Cleveland, OH
>
> Sent via Deja.com
> http://www.deja.com/
>

You are in need of a dynamic cursor.

Oracle is attempting to complile the cursor and the variable is not defined. Hence the error.

You can construct a cursor in text, parse it, and execute it with dynamic cursors. Check the manuals. Depending on the version of Oracle, you will be using either dbms_sql package or the dynamic SQL.

--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rii.com
              Usual disclaimers


Sent via Deja.com
http://www.deja.com/