Re: PL/SQL Cursor

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 16 Jun 1999 20:03:50 GMT
Message-ID: <37690284.1558350@newshost.us.oracle.com>

A copy of this was sent to smaranda_at_benhur.teluq.uquebec.ca (Steve Maranda) (if that email address didn't require changing) On 16 Jun 1999 18:34:07 GMT, you wrote:

> How can i use commpile this cursor.
> I want to use a parameter to query a specific user table.
>
> cursor a(user_name in varchar2) is
> select * from user_name||'.'||table_name;
> b a%ROWTYPE;
>
> I can select data when i use SQL Plus so
> i know it is a syntax problem.
>
>
> Thank you very much.
>

you must use dynamic sql to do that. dbms_sql is the package you are interested in with Oracle8, release 8.0 and before. It might look like:

create or replace procedure dynquery( p_tname in varchar2, p_set in varchar2) is

    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(2000);
    l_status        integer;
    l_query         varchar2(1000) default 'select ename from ' || p_tname ||
                                          ' where job in ( ' ||
                                            p_set || ' )';

begin

    dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );     dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );

    l_status := dbms_sql.execute(l_theCursor);

    loop

        exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
        dbms_sql.column_value( l_theCursor, 1, l_columnValue );
        dbms_output.put_line(  l_columnValue );

    end loop;
    dbms_sql.close_cursor(l_theCursor);

end ;
/

exec dynquery( 'emp', ' ''CLERK'', ''MGR'' ' )

With Oracle8i, release 8.1 and up, it will look like this:

create or replace procedure
...
as
  type refCur is ref cursor;
  myCursor refCur;
begin

    open myCursor for 'select * from ' || user || '.' || tname;

    loop

      fetch myCursor into myRec;
      exit when myCursor%notfound;

    end loop;
end;

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/

Current article is "Fine Grained Access Control", added June 8'th  

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 Wed Jun 16 1999 - 15:03:50 CDT