Re: PL/SQL: dbms_sql.execute error

Tom,

Thanks for the reply.
Your example shows how to use dbms_sql package to execute and fetch a dynamic query.

I learned a lot !

When I assigned a status varilabe to the dbms_sql.execute ( ,,, )

    the error went away but I didn't see any results.     I used your code fetch the result set using the dbms_sql methods.

> [snip] mine
>
> > 9 for csr_alteruser_rec in csr_alteruser loop
> > 10 dbms_output.put_line ( csr_alteruser_rec.ssql);
> > 11 dbms_sql.parse(cid, csr_alteruser_rec.ssql, dbms_sql.v7);
> > 12 dbms_sql.execute( cid ); -- NO ASSIGNMENT TO A VARIABLE
> > 13 -- NO CODE TO
FETCH RESULT SET
> > 13 end loop;
>

Yours:
> begin
> dbms_sql.parse( l_theCursor,
> 'select count(*) from ' || p_tname,
> dbms_sql.native );
>
> dbms_sql.define_column( l_theCursor, 1, l_columnValue );
>
> l_status := dbms_sql.execute(l_theCursor);
>
> if ( dbms_sql.fetch_rows(l_theCursor) > 0 )
> then
> dbms_sql.column_value( l_theCursor, 1, l_columnValue );
> end if;
>
> dbms_sql.close_cursor(l_theCursor);

Thanks, Paul

Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:37b0c783.1370911_at_newshost.us.oracle.com...
> A copy of this was sent to "Paul Chu" <chupaul_at_earthlink.net>
> (if that email address didn't require changing)
> On Sun, 8 Aug 1999 18:36:53 -0700, you wrote:
>
> >Hi all,
> >
> >Help, I'm running 7.3.4 and sql*plus 3.3 and testing a script to
> >dynamically create select 'table_name', rowcount(*) from table_name
stmts
> >and execute them.
> >
> >Problem is that sql plus returns an error on execute method :
> >If I comment out the line then the script runs fine.
>
> [snip]
>
> > 9 for csr_alteruser_rec in csr_alteruser loop
> > 10 dbms_output.put_line ( csr_alteruser_rec.ssql);
> > 11 dbms_sql.parse(cid, csr_alteruser_rec.ssql, dbms_sql.v7);
> > 12 dbms_sql.execute( cid );
> > 13
> > 13 end loop;
>
> [snip]
>
> >declare
> >*
> >ERROR at line 1:
> >ORA-06550: line 12, column 2:
> >PLS-00221: 'EXECUTE' is not a procedure or is undefined
> >ORA-06550: line 12, column 2:
> >PL/SQL: Statement ignored
> >
>
> It says "execute is not a procedure or is undefined". you are getting
this
> because execute is not a procedure -- its a function and you are calling
it as
> though it were a procedure. Here is an example that does what you are
trying to
> do:
>
> create or replace function countem( p_tname in varchar2 ) return
number
> is
> l_theCursor integer default dbms_sql.open_cursor;
> l_columnValue number default NULL;
> l_status integer;
> begin
> dbms_sql.parse( l_theCursor,
> 'select count(*) from ' || p_tname,
> dbms_sql.native );
>
> dbms_sql.define_column( l_theCursor, 1, l_columnValue );
>
> l_status := dbms_sql.execute(l_theCursor);
>
> if ( dbms_sql.fetch_rows(l_theCursor) > 0 )
> then
> dbms_sql.column_value( l_theCursor, 1, l_columnValue );
> end if;
>
> dbms_sql.close_cursor(l_theCursor);
>
> return l_columnValue;
> end countem;
> /
>
>
> begin
> for x in ( select owner || '.' || table_name tname
> from all_tables
> where owner = 'SCOTT' )
> loop
> dbms_output.put_line( x.tname || ' ' || countem( x.tname ) );
> end loop;
> end;
> /
>
> >???Not sure why in line1: declare is causing an error also
> >Appreciate a direct email as well as a newsgroup post.
> >
> >Thanks, Paul
> >
> >
> >
>
>
> --
> 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 Mon Aug 09 1999 - 17:53:53 CDT