Re: PL/SQL and DBMS_SQL problem

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: 2000/05/19
Message-ID: <958748432.24226.0.pluto.d4ee154e@news.demon.nl>#1/1

Ceri <cerit_at_teleordnospamplease.co.uk> schreef in berichtnieuws 8F3998381cerittownsend_at_172.17.8.4...
> 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

corrected code :

 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;
    sqlstr varchar2(1000);

 BEGIN
   open tab_cur;
   loop

     fetch tab_cur into tab_name;
     exit when tab_cur%NOTFOUND;
     cursor_name := dbms_sql.open_cursor;
     sqlstr := 'select count(*) from phoenix.'||tab_name;
     dbms_sql.define_column(cursor_name, 1, y);
      dbms_sql.parse(cursor_name, sqlstr,dbms_sql.native);
     -- bind variables can be used in where clauses only
      do :=  dbms_sql.execute_and_fetch(cursor_name);
      dbms_sql.column_value(cursor_name, 1,y)

      dbms_output.put_line('Table' ||tab_name||' has '||y||'  rows');
      dbms_sql.close_cursor(cursor_name);

   end loop;
   close tab_cur;
 END ; Hth,

Sybrand Bakker, Oracle DBA Received on Fri May 19 2000 - 00:00:00 CDT