Re: Dynamic SQL problem in PL/SQL block run from SQL*Plus

From: <tkyte_at_us.oracle.com>
Date: 1997/01/23
Message-ID: <854076979.31704@dejanews.com>#1/1

In article <01bc094c$05f8fee0$b90110ac_at_gastineau1.giveblood.org>,   "Brian Gastineau" <bgastine_at_giveblood.org> wrote:
>
> Any suggestions for why the following PL/SQL block will output the "put_line"
 results to
> the screen but won't output the "parse" results would be appreciated.
>

dbms_sql.parse does only what it's name implies; parse.

It syntax/permission checks the statements and compiles an execution plan for it. It does not run the statement.

You need to bind and fetch rows in addition to parse. At the bottom of this message, I have attached a package that does this. You can call:

SQL> exec dump_pkg.query( 'select * from emp' );

and it will parse, bind, execute, and fetch the cursor.

Hope this helps...

> -------------------
> DECLARE
> CURSOR Table_List is SELECT Table_Name
> FROM All_Tables
> WHERE Owner = 'GASTINEAU';
> cid INTEGER;
> BEGIN
> cid := dbms_sql.open_cursor;
> FOR Current_Table IN Table_List LOOP
> dbms_output.put_line('SELECT * FROM GASTINEAU.' ||

 current_table.table_name);


>     dbms_sql.parse(cid, 'SELECT * FROM GASTINEAU.' || 

 current_table.table_name,

> dbms_sql.v7);
> END LOOP;
> dbms_sql.close_cursor(cid);
> END;
> -------------------------
>
> The screen output is a list of SQL commands of the form
>
> SQL> @mysearch
> 20> /
> SELECT * FROM GASTINEAU.MYTABLE1
> SELECT * FROM GASTINEAU.MYTABLE2
> SELECT * FROM GASTINEAU.MYTABLE3
> ...
> PL/SQL procedure successfully completed.
> SQL>
>
> The SERVEROUTPUT setting is ON in SQL*Plus and the block is being run from the
 GASTINEAU
> account.
>
> My goal is to view the data contained in all tables. If there is a more
 appropriate way
> to access this information, that process would also be helpful.
>
> Thanks
> Brian Gastineau
> bgastine_at_giveblood.org

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.


-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet

Received on Thu Jan 23 1997 - 00:00:00 CST