PL/SQL: dbms_sql.execute error

From: Paul Chu <chupaul_at_earthlink.net>
Date: Sun, 8 Aug 1999 18:36:53 -0700
Message-ID: <7olbg4$h6k$1@ash.prod.itd.earthlink.net>

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.

SQL> declare
  2 cid integer;
  3
  3 cursor csr_alteruser is
  4 select 'select ' || '''' || table_name || ''', count(*) from ' ||   5 table_name as ssql from all_tables where owner='SCOTT';   6
  6
  6 begin
  7 dbms_output.enable;
  8
  8 cid := dbms_sql.open_cursor;
  9
  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;
 14
 14 dbms_sql.close_cursor(cid);
 15
 15 exception
 16 when others then
 17 dbms_sql.close_cursor(cid);
 18 dbms_output.put_line ( SQLERRM );  19 end;
 20 /
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

???Not sure why in line1: declare is causing an error also Appreciate a direct email as well as a newsgroup post.

Thanks, Paul Received on Sun Aug 08 1999 - 20:36:53 CDT