PL/SQL and DBMS_SQL problem

From: Ceri <cerit_at_teleordnospamplease.co.uk>
Date: 2000/05/19
Message-ID: <8F3998381cerittownsend@172.17.8.4>#1/1

Hi,

I have only recently started using PL/SQL and I am trying to write a script that tells me how many rows are in each table in the database (for various reasons the analyse data help on the tables is not accurate enough.

THis is what I have for my script so far.

DECLARE
   cursor tab_cur is
     select table_name from dba_tables where owner='PHOENIX';    tab_name dba_tables.table_name%TYPE;
   row_count number(30,0);
   cursor_name INTEGER;
   do INTEGER;  

BEGIN
  open tab_cur;
  loop
    fetch tab_cur into tab_name;
    exit when tab_cur%NOTFOUND;
    cursor_name := dbms_sql.open_cursor;      dbms_sql.parse(cursor_name, 'select count(*) from phoenix.:x', dbms_sql.native);

     dbms_sql.bind_variable(cursor_name, ':x', tab_name);
     dbms_sql.bind_variable(cursor_name, ':y', row_count);
     do :=  dbms_sql.execute(cursor_name);
     dbms_output.put_line('Table' ||tab_name||' has '||to_char(do)||' 
rows');
     dbms_sql.close_cursor(cursor_name);

  end loop;
  close tab_cur;
END ; When i try to run it I get the following errors:

DECLARE
*
ERROR at line 1:

ORA-00903: invalid table name
ORA-06512: at "SYS.DBMS_SYS_SQL", line 491
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at line 15

Could anyone please help me identify what is causing this error. I am running Oracle 8.0.4 on Solaris 2.6, and I am trying to run the script using @script_name in sqlplus.

Thanks for your time

Ceri

-- 
Ceri Townsend		Database Admin
to reply via e-mail
remove nospamplease from e-mail

Received on Fri May 19 2000 - 00:00:00 CDT