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

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
>
> RaviTry using dynamic sql.

Put the SQL in a buffer (varchar(1000)). sql_buf := 'select '||tbl_name||' from ...';

open the cursor with the above sql.

Any PL/SQL book should have examples..of simple dynamic sql.

Hope this helps.. Received on Wed Jan 31 1996 - 00:00:00 CET