CODEWITHSUNDEEP
oracle-databaseplsql
Sandy
Sandy

Reputation: 449

Variable in cursor select statement

I was trying to fetch the count of multiple tables using PLSQL, having 4 tables - testing, test1, test2, test3.

Table testing having the details of all the tables whose count I need and was using the following program using cursor to achieve the same.

declare
var varchar2(20);
var2 varchar2(20);
cursor C1 is select tab_name from testing;
begin
open C1;
loop
fetch C1 into var;
exit when C1%notfound;
select count(*) into var2 from var;
dbms_output.put_line(var2);
end loop;
end;
/

But getting below error on executing:

select count(*) into var2 from var;
                               *
ERROR at line 10:
ORA-06550: line 10, column 32:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 10, column 1:
PL/SQL: SQL Statement ignored

Upvotes: 0

Views: 1068

Answers (2)

Sandeep
Sandeep

Reputation: 806

set serveroutput on;
declare
var varchar2(20);
var2 varchar2(20);
cursor c is select field1 from testing;
begin
for c1 in c
loop
var2:=c1.field1;
execute immediate 'select count(*) from ' || var2 into var;
-- below query won't work as we are creating a dynamic SQL statement
 --select count(*) into var from var2;
dbms_output.put_line(var);
end loop;
end;

Upvotes: 0

Praveen
Praveen

Reputation: 9345

You have to use execute immediate with dynamic sql.

declare
    var varchar2(50);
    var2 number;
    cursor C1 is select tab_name from testing;
begin
    open C1;
    loop
        fetch C1 into var;
        exit when C1%notfound;
        execute immediate 'select count(*) from ' || var into var2;
        dbms_output.put_line(var2);
    end loop;
end;
/

you can use Implicit Cursors like;

declare
    var number;
begin
    for i in (select tab_name from testing) loop
        execute immediate 'select count(*) from ' || i.tab_name into var;
        dbms_output.put_line(var);
    end loop;
end;
/

Upvotes: 1

Related Questions