SEO

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.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

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@x.washington.edu
(replace x with u to respond)

DA Morgan wrote:

>> 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?
>
> Look at the examples in Morgan's Library (www.psoug.org) for
> Native Dynamic SQL.

Thank you Daniel,

That's going to take me a while to get through as it's using
formats/methods I'm not at all familiar with, but there's one or two
Oracle people here who might be able to help me through it. I'll post
back if I have any difficulties. Thanks again.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

Tim Marshall wrote:

>>> 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?
>>
>> Look at the examples in Morgan's Library (www.psoug.org) for
>> Native Dynamic SQL.

As I mentioned, this would take me a while to go through, but it's
totally out of my depth.

Is it really so involved to simply pass an SQL select statement to be
used as, for example, a where clause in a cursor? Am I asking the wrong
thing?

I've looked around google for some info on dynamic SQL and just mired
myself into deeper holes. I thought
http://www.csee.umbc.edu/help/oracle...022/dynsql.htm might
be of some help to me, but it's just frustrated me further.

Again, I may be totally lost at sea, but are there any other options,
sites that can be of some help? Oracle education (classes, seminars)
is, unfortunately, not an option for me, at least not locally.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

Tim Marshall wrote:
> Tim Marshall wrote:
>
>>>> 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?
>>>
>>>
>>> Look at the examples in Morgan's Library (www.psoug.org) for
>>> Native Dynamic SQL.
>
>
> As I mentioned, this would take me a while to go through, but it's
> totally out of my depth.
>
> Is it really so involved to simply pass an SQL select statement to be
> used as, for example, a where clause in a cursor? Am I asking the wrong
> thing?
>
> I've looked around google for some info on dynamic SQL and just mired
> myself into deeper holes. I thought
> http://www.csee.umbc.edu/help/oracle...022/dynsql.htm might
> be of some help to me, but it's just frustrated me further.
>
> Again, I may be totally lost at sea, but are there any other options,
> sites that can be of some help? Oracle education (classes, seminars)
> is, unfortunately, not an option for me, at least not locally.

Beats me why some people don't seem to be able to find the original source:

http://tahiti.oracle.com (select the documentation matching your version)

I don't know how you want to process the returned SQL, but perhaps this little
snippet gets you in some direction:

SQL> edit
Wrote file afiedt.buf

1 create or replace function anysql (p_stmt in varchar2) return sys_refcursor
2 as
3 result sys_refcursor;
4 begin
5 open result for p_stmt;
6 return result;
7* end;
SQL> /

Function created.

SQL> variable x refcursor;
SQL> exec :x := anysql('select table_name from user_tables');

PL/SQL procedure successfully completed.

SQL> print :x

TABLE_NAME
------------------------------
BIGTABLE
SVCENT
SVCATTR
TEST

SQL>


However, since the documentation you quoted is for 8.1.5, the above might not work because
sys_refcursor didn't exist back then. You will have to create a type that is a ref cursor.
(Another reason to *always* state the version(s) involved).

For further informations on the topic you might want to check out Tom Kyte's page:

http://asktom.oracle.com

HTH
Holger