RE: Execute Immediate and Cursors

From: Kevin Lange <klange_at_ppoone.com>
Date: Mon, 14 Jun 2004 18:02:46 -0500
Message-ID: <ED1256BD4F253C44B1627B2D365A334F0208B41F@ppoone1.ppoone.com>

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_at_yahoo.com] Sent: Monday, June 14, 2004 6:00 PM
To: oracle-l_at_freelists.org
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_at_ppoone.com> 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_at_freelists.org
  > 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_at_freelists.org 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_at_freelists.org
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
-----------------------------------------------------------------

Received on Mon Jun 14 2004 - 18:00:08 CDT