RE: Execute Immediate and Cursors

He wants to be able to build the cursor on the fly, including picking
different columns and different table_names.

No DML.  Strictly a multi-row query.

-----Original Message-----
From: Melanie Caffrey [mailto:melanie_caffrey@xxxxxxxxx]
Sent: Monday, June 14, 2004 6:00 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Execute Immediate and Cursors


Kevin?

Do you just need to build a dynamic cursor (meaning,
no other processing necessary, like DML)?

If so, in 8.1.7, Execute Immediate works best with
single-row queries.

However, if your intent is to retrieve a multi-row
query, then why not try something simiar to the
following:

CREATE OR REPLACE PROCEDURE get_my_cursor
                       (p_jobid  IN  dept.jobid%TYPE,
                        p_cursor OUT SYS_REFCURSOR)
BEGIN
   OPEN p_cursor FOR
   SELECT job_name, job_loc
     FROM dept
    WHERE job_id > p_jobid;

   -- OR --

   OPEN p_cursor FOR
      'SELECT job_name, job_loc
         FROM dept
        WHERE job_id > :j' USING p_jobid;
   ...

Personally, I think the first OPEN p_cursor statement
is easier to use, and manage.

hth,
melanie

--- Kevin Lange <klange@xxxxxxxxxx> wrote:
> Evening;
>   Back on 8.1.7, was there an easy way to use the
> Execute Immediate command
> to build a dynamic Cursor ?   
> 
> I have used the DBMS_SQL package in the past but one
> of our developers is
> asking me if it can all be done with Execute
> Immediate.
> 
> Unfortunately, we are not at 9i so we can not use
> the Bulk Collect option.
> 
> 
> Thanks
> 
> Kevin
>
----------------------------------------------------------------
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
>
----------------------------------------------------------------
> To unsubscribe send email to: 
> oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at
> http://www.freelists.org/archives/oracle-l/
> FAQ is at
> http://www.freelists.org/help/fom-serve/cache/1.html
>
-----------------------------------------------------------------
> 

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: