Re: PL/SQL Cursors and Parameters

Hi!

What if you create a plsql function that has enough restrict_pragma to be used in where-conditions?

Lets say you want to achieve something like: cursor someCursor(orgList varchar2) is
select someField
from someTable
where My_Package.CheckOrg(organization, orgList)= 'TRUE';

Then create the Checkorg function, receiving both your list and data from the row "in progress".
The CheckOrg scans the string (like INSTR) for the value in "organization" and returns e.g
'TRUE' when found otherwise 'FALSE'
On second thought it might be possible to construct this with INSTR directly.

Be warned:
1.I have not tried it
2.There may be a performance penalty, but if there is little data this might be a little bit easier than DBMS_SQL at least :-)

I have seen people ask for this feature before in this newsgroup, and I have also been "missing" it
myself; perhaps there is something for Oracle to consider here? :-)))

Frank

<dennishancy_at_eaton.com> wrote in message news:959bve$fp0$1_at_nnrp1.deja.com...
> 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/
Received on Thu Feb 01 2001 - 19:50:16 CET