Re: Execute Immediate and Cursors

From: Melanie Caffrey <melanie_caffrey_at_yahoo.com>
Date: Mon, 14 Jun 2004 15:59:37 -0700 (PDT)
Message-ID: <20040614225937.14651.qmail@web51906.mail.yahoo.com>

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
-----------------------------------------------------------------

Received on Mon Jun 14 2004 - 17:56:21 CDT