Re: dynamic sql

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


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

>hi
>
[Quoted] >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 CET

Original text of this message