Re: Creating a cursor dynamically

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 28 Jul 1999 11:58:39 GMT
Message-ID: <37a1efca.3418525@newshost.us.oracle.com>

A copy of this was sent to kelvinw_at_asymetrix.com (if that email address didn't require changing) On Wed, 28 Jul 1999 01:04:39 GMT, you wrote:

>Hi evveryone,
>
>
>I open up a very simple cursor as follows:
>
>Declare
> sCmd varchar2(500);
> iCount int;
> TYPE curvar_type IS REF CURSOR;
> curvar1 curvar_type;
> tname varchar2(80);
>Begin
>
> Open curvar1 For Select table_name from user_tables;
>-- sCmd := 'Open curvar1 For Select table_name from user_tables';
>-- ACMSPExecute( sCmd );
>
> Loop
> FETCH curvar1 into tname;
> EXIT WHEN curvar1%NOTFOUND;
> DBMS_OUTPUT.PUT_LINE( 'table_name: ' || tName );
> END Loop;
>-- sCmd := ' Close curvar1' );
>-- SPExecute( sCmd );
> Close curvar1;
>End;
>/
>
>Where SPExecute() is a stored procedure that open up cursor and
>execute the input command string dynamically.
>
>
>In MSSQL, I can open up a cursor by doing something like
>Select @sCmd = 'Open curvar1 For Select table_name from user_tables'
>Execute( sCmd )
>
>equivalent to Oracle:
>sCmd := 'Open Open curvar1 For Select table_name from user_tables';
>SPExecute( sCmd);
>
>to get a cursor opened dynamically. And close it with
>sCmd := 'Close curvar1';
>SPExecute( sCmd );
>
>Basically, I need to create a cursor on the fly.
>I have tried that method didn't work using the oracle dynamic SQL. I
>wonder if it is possible to do something like that in Oracle. And how
>to do it?
>
>Any help is appreciated.

In Oracle8.0 and below, you would use the DBMS_SQL package to do dynamic sql. I might look like:

create or replace procedure demo( p_fieldname in varchar2,

                                  p_tablename in varchar2 )
is
    l_theCursor     integer;
    l_columnValue   varchar2(2000);
    l_status        integer;

begin

    l_theCursor := dbms_sql.open_cursor;

    dbms_sql.parse( l_theCursor,

                    'select ' || p_fieldname || ' from ' || p_tablename,
                     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 demo;
/

In Oracle8i, release 8.1 and up you can code:

create or replace procedure demo( p_fieldname in varchar2,

                                  p_tablename in varchar2 )
is
    type refCur     is ref cursor;
    l_cursor        refCur;

    l_columnValue varchar2(2000);
begin

    open l_cursor for 'select ' || p_fieldname || ' from ' || p_tablename;

    loop

        fetch l_cursor into l_columnValue;
        exit when l_cursor%notfound;

        dbms_output.put_line( l_columnValue );

    end loop;

    close l_cursor;
end demo;
/

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

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 Jul 28 1999 - 06:58:39 CDT