Re: Specifying Cursor SQL

Tim Marshall wrote:
> I am just learning about PL/SQL.
>
> From what I've read so far (John Palinski's book - I don't know how
> well it stands up against other publications, but it's done sterling
> service for me with respect to the Oracle SQL I've been doing the past
> few years), there doesn't seem to be an easy way of passing an SQL
> select statement to a function or procedure.
>
> Here's what I'm trying to do (please note I've prefixed -- with an
> apostrophe for those whose newsreaders format .sig files differently
> from the post's main body):
>
> create or replace function F_DISTRIB
> (SQL_STATEMENT in varchar2(32000))
> return number is
> /*
>
> SQL_STATEMENT is a select statement which results in anything from a
> simple single value to a complex construction that returns multiple values.
>
> I specified varchar2(32000) as I don't think I can just leave things
> open ended with specifying it as just varchar2 without a length
> specification?
>
> */
> '--
> n_Distribution number;
> cursor cDistributions is
> select
> DIST_VALUE
> from
> SOME_TABLE
> where
> SOME_COLUMN IN (SQL_STATEMENT);
> '--
> begin
> '--
> open cDistributions;
> <run through records which add decimal values to n_Distribution>
> close cDistributions;
> '--
> return n_Distribution
> '--
> End
>
>
> The thing I'm trying to do is pass the SQL statement to the cursor to be
> used in the cursor's where clause.
>
> AFAIK, the above is not the way to do it. Could I please ask for some
> site, perhaps, that might explain how to use SQL in a dynamic way as
> I've tried to do up above?
>
> Thanks very much in advance.

Look at the examples in Morgan's Library (www.psoug.org) for Native Dynamic SQL.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)