Re: pl/sql simple prob but .....

To use Dynamic Query and DML statements you must use the DBMS_SQL package.

something like this:

create procedure table_count is

   c integer;
   cnt integer;
   cursor c_user_tables is

      select table_name
        from user_tables;

   for t in c_user_tables loop

      c := dbms_sql.open_cursor;
      dbms_sql.parse(c,'select count(*) from '||t.table_name,dbms_sql.v7);
      dbms_sql.define_column(c,1,cnt);
      if dbms_sql.execute_and_fetch(c) = 1 then
         dbms_sql.column_value(c,1,cnt);
         dbms_output.put_line(rpad(t.table_name,30)||' - '||lpad(to_char(cnt),10));
      else
         dbms_output.put_line('ERROR ['||t.table_name||']');
      end if;
      dbms_sql.close_cursor(c);

   when others then

      if dbms_sql.is_open(c) then
         dbms_sql.close_cursor(c);
      end if;

Ravi Rai wrote:
>
> Hi
>
> I am very new to PL/SQL. I wrote this program to see if I can use
> variables for table names. But I always end up in error.
>
> I appreciate any feedback.
> ------------------------
> create or replace procedure rktest as
> cursor tbl_name is
> select table_name
> from user_tables
> order by table_name;
>
> rec_count number;
>
> begin
> dbms_output.put_line(' ************* ');
> DBMS_OUTPUT.PUT_LINE ('');
> DBMS_OUTPUT.PUT_OUTPUT.PUT_LINE ('');
> for lp_val in tbl_name loop
>
> select count (*)
> into rec_count
> from lp_val.table_name#;
>
> DBMS_OUTPUT.PUT_LINE ('Table :'||lp_val.table_name);
> DBMS_OUTPUT.PUT_LINE ( ' recs: ' || rec_count);
> DBMS_OUTPUT.PUT_LINE ('');
> end loop;
> end rktest;
> /
> compilation Error:
> 15/10 PLS-00356: 'LP_VAL.TABLE_NAME' must name a table to which the
> user has access
>
> ----
>
> Thanks
>
> Ravi
 

-- 
_________________________________________________
Robert C. Nix
Oracle Tools Support
rnix_at_us.oracle.com

The thoughts, opinions, remarks, suggestions, ...
expressed herein are my own and in no way should
be taken as a statement from Oracle Corporation.