Re: dynamic sql
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
- 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