RE: Execute Immediate and Cursors
- From: Melanie Caffrey <melanie_caffrey@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 14 Jun 2004 16:21:42 -0700 (PDT)
OK.
He can still use a ref cursor with native dynamic sql.
No need for the execute immediate verb, though.
CREATE OR REPLACE PROCEDURE get_my_cursor
(p_column IN <your choice>,
p_table IN <your choice>,
p_predicate IN <your choice>,
p_jobid IN dept.jobid%TYPE,
p_cursor OUT SYS_REFCURSOR)
sql_stmt VARCHAR2(1000);
job_rec dept%ROWTYPE;
BEGIN
sql_stmt := 'SELECT job_name, :c
FROM :t
WHERE :w > :j';
OPEN p_cursor FOR sql_stmt
USING p_column, p_table, p_predicate, p_jobid;
LOOP
FETCH p_cursor INTO job_rec;
EXIT WHEN p_cursor%NOTFOUND;
.
.
.
END LOOP;
CLOSE p_cursor;
-- Or pass it as an OUT parameter, above.
END;
--- Kevin Lange <klange@xxxxxxxxxx> wrote:
> 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
>
-----------------------------------------------------------------
>
----------------------------------------------------------------
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:
