RE: Execute Immediate and Cursors
- From: Kevin Lange <klange@xxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 14 Jun 2004 18:02:46 -0500
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
> //www.freelists.org/archives/oracle-l/
> FAQ is at
> //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 //www.freelists.org/archives/oracle-l/
FAQ is at //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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
