Re: Specifying Cursor SQL

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Tue, 20 Sep 2005 12:12:47 +0200
Message-ID: <dgonb0$9p3$1@news.BelWue.DE>

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/oracle8/server.815/a68022/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 Received on Tue Sep 20 2005 - 05:12:47 CDT