Re: dynamic sql

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 1997/02/07
Message-ID: <32fa8ba8.2661036@nntp.mediasoft.net>#1/1

On Thu, 06 Feb 97 11:29:30 GMT, Denver.Schouw_at_Baps.com (Denver Schouw) wrote:

>hi
>
>if the 'parsed statement' in dbms_sql.parse is a function call that returns a 
>value,how do i retreive this value?
>
>thank you

At the bottom is a small
generic package that can take any sql select statement, prepares it, binds it and fetches the data out and prints it. The loop at the bottom that reads:  

    loop

        exit when ( dbms_sql.fetch_rows(p_theCursor) <= 0 );
        sep := '';
        for i in 1 .. colCnt loop
            dbms_sql.column_value( p_theCursor, i, columnValue );
            dbms_output.put( sep || columnValue );
            sep := ',';
        end loop;
        dbms_output.new_line;

    end loop;  

would be replaced with YOUR logic to process the data. basically, this packaage allows you to open the query and get a column count and dump the data....    

create or replace package dump_pkg
as

    procedure query( p_theQuery in varchar2 ); end dump_pkg;
/
show errors
--
--

create or replace package body dump_pkg
as
--

function bind_outputs( p_theCursor in integer ) return number is

    columnValue        varchar2(1);
    colCnt            number default 0;
    status            integer;

begin

    for i in 1 .. 255 loop

        begin
            dbms_sql.define_column( p_theCursor, i, columnValue, 2000 );
            colCnt := colCnt + 1;
        exception
            when others then
                if ( sqlcode = -1007 ) then
                    exit;
                else
                    raise;
                end if;
        end;

    end loop;
    status := dbms_sql.execute(p_theCursor);     return colCnt;
end bind_outputs;
--
--
--

function init( theQuery in varchar2 ) return integer is

    theCursor integer;
begin

    if ( upper( substr( ltrim( theQuery ), 1, 6 ) ) <> 'SELECT' ) then

        raise_application_error( -20000, 'INVALID_QUERY' );     end if;
--

 --

    theCursor := dbms_sql.open_cursor;
    dbms_sql.parse( theCursor, theQuery, dbms_sql.native );
--

    return theCursor;
end init;
--
--
--

procedure query( p_theQuery in varchar2 ) is

    p_theCursor number default init( p_theQuery );     columnValue varchar2(2000);
    colCnt number default 0;
    tmpcursor number default p_theCursor;

    reccnt      number default 0;
    sep            varchar2(1) default '';

begin
--

    colCnt := bind_outputs( p_theCursor );
--

    loop

        exit when ( dbms_sql.fetch_rows(p_theCursor) <= 0 );
        sep := '';
        for i in 1 .. colCnt loop
            dbms_sql.column_value( p_theCursor, i, columnValue );
            dbms_output.put( sep || columnValue );
            sep := ',';
        end loop;
        dbms_output.new_line;

    end loop;
    dbms_sql.close_cursor(tmpCursor);
end query;
--
--

end dump_pkg;
/
show errors  

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com

• Check out Oracle Governments web site! ----- Follow the link to "Tech Center" and then downloadable Utilities for some free software...

  ---

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Fri Feb 07 1997 - 00:00:00 CST