A Dynamic SQL problem.....Help!!

From: Violin <violin.hsiao_at_mail.pouchen.com.tw>
Date: 31 May 1999 01:29:08 GMT
Message-ID: <3753e635.2594932@news.twsc.pouchen.com.tw>

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 Received on Sun May 30 1999 - 20:29:08 CDT