Re: A Dynamic SQL problem.....Help!!

Prior to Oracle8i, release 8.1 when you can code:

  open emp_cur for 'select * from emo where ' || p_some_variable;

you cannot dynamically open a ref cursor. dbms_sql cursors are not compatible with ref cursors.

A copy of this was sent to violin.hsiao_at_mail.pouchen.com.tw (Violin) (if that email address didn't require changing) On 31 May 1999 01:29:08 GMT, you wrote:

>Hello,
>If I create a procedure for SELECT statement in static SQL.
>Like this:
>create or replace procedure emp_list(
> dept in number,
> emp_cur in out emp_pkg.empcurtyp)
>is
>begin
> open emp_cur for
> select empno,ename,job from emp where deptno = dept;
>end;
>/
>create or replace package emp_pkg as
> type emptyp is record
> (empno number,
> ename varchar2(10),
> job varchar2(9));
> type empcurtyp is ref cursor return emptyp;
>end;
>/
>
>When executing emp_list,it works fine.
>
>If the SELECT must be a dynamic SQL .
>Like this:
>cursor_name := dbms_sql.open_cursor;
>dbms_sql.parse(cursor_name,
> 'select empno,ename,job from emp where deptno = :x',dbms_sql.native);
>dbms_sql.bind_variable(cursor_name, ':x', dept);
>rows_processed := dbms_sql.execute(cursor_name);
>dbms_sql.close_cursor;
>
>How to recreate the procedure?
>I try this with compile errors:
>begin
> open emp_cur for
> cursor_name := dbms_sql.open_cursor;
> dbms_sql.parse(cursor_name,
> 'select empno,ename,job from emp where deptno = :x',dbms_sql.native);
> dbms_sql.bind_variable(cursor_name, ':x', dept);
> rows_processed := dbms_sql.execute(cursor_name);
> dbms_sql.close_cursor;
>exception
>when others then
> dbms_sql.close_cursor(cursor_name);
>end;
>/
>
>LINE/COL ERROR
>-------- ----------------------------------------------------------------
>9/6 PLS-00103: Encountered the symbol "CURSOR_NAME" when expecting
> one of the following:
> select
>
>How to create the procedure with dynamic SELECT and return the result?
>Thank you for any tips.
>
>Violin.
>violin.hsiao_at_mail.pouchen.com.tw

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon May 31 1999 - 11:54:09 CDT