how to deal with table variable in pl/sql

Have a table T1.
I got the error:
PL/SQL: ORA-00942: table or view does not exist
when I executed the pl/sql statement:
declare
 str varchar2(10) := 'T1';
 cnt number(1);
begin
select count(1) into cnt from str;
  dbms_output.put_line(cnt);
end;
/

How to deal with it?  In real situation, the table name has to be a variable.
jl66Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
msd1305Connect With a Mentor Commented:
declare
 str varchar2(10) := 'T1';
 cnt number;
begin

  execute immediate 'select count(1) from ' ||  str into cnt;
 
  dbms_output.put_line(cnt);
end;
0
 
msd1305Connect With a Mentor Commented:
Explanation: When you directly write a sql query, you can not use variable as a table name. Because database expects a table name STR .

Execute Immidiate expects sql query as a string and executes it. So not only table name but you can execute any kind of dynamic queries.

just google it to get more explanation :) .

Hope this helps.
0
 
jl66Author Commented:
Excellent.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.