CODEWITHSUNDEEP
oracle-databaseplsql
rakesh.sahu
rakesh.sahu

Reputation: 461

Is there any way to use a PLSQL variable in 'FROM' clause?

Hi Is there any way to use a PL sql variable in from clause? i am using below code but i am getting error in select statement that "Table Not Found"

declare 
--var varchar2(20);
cursor cur is
    select table_name from my_tables where table_name like 'RATED%';
    v_rows cur%rowtype;
--V_name varchar2(20);
begin
    open cur;
    loop
        fetch cur into v_rows;  
            exit when cur%NOTFOUND;
            select * from v_rows.table_name;  --here i am getting error.
   end loop;
 end;

Upvotes: 0

Views: 3743

Answers (2)

Frank Schmitt
Frank Schmitt

Reputation: 30775

No, it's not possible. You cannot use variables as table or schema names. You'll have to use native dynamic SQL or DBMS_SQL for this.

Also, you cannot use a plain 'SELECT * from v_rows.table_name' in PL/SQL - you need an INTO clause (a "target" where you put the results of the SELECT).

Here's an example that should put you on the right track:

create table my_tables as select table_name as table_name from user_tables;

declare
  cursor cur is
    select table_name
      from my_tables
       where table_name like 'RATED%';
  v_rows cur%rowtype;
  v_cnt  pls_integer;
  v_SQL varchar2(4000);
begin
  open cur;
  loop
    fetch cur into v_rows;
    exit when cur%NOTFOUND;
    v_SQL := 'select count(*) from ' || v_rows.table_name;
    -- debug output
    dbms_output.put_line(v_SQL);
    EXECUTE IMMEDIATE v_SQL INTO v_cnt;
    dbms_output.put_line(v_rows.table_name || ' : ' || v_cnt);
  end loop;
end;

Upvotes: 1

Uwe Plonus
Uwe Plonus

Reputation: 9954

You can use dynamic sql for such:

excute immediate 'select * from ' || v_rows.table_name;

Please look at the description of execute immediate and DBMS_SQL to learn dynamic sql.

To get results from such queries you have to use explicit cursors (also explained in the description mentioned above).

Upvotes: 1

Related Questions