Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how to deal with table variable in pl/sql

Posted on 2011-02-19
3
Medium Priority
?
557 Views
Last Modified: 2012-08-14
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.
0
Comment
Question by:jl66
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 4

Accepted Solution

by:
msd1305 earned 2000 total points
ID: 34936016
declare
 str varchar2(10) := 'T1';
 cnt number;
begin

  execute immediate 'select count(1) from ' ||  str into cnt;
 
  dbms_output.put_line(cnt);
end;
0
 
LVL 4

Assisted Solution

by:msd1305
msd1305 earned 2000 total points
ID: 34936032
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
 

Author Closing Comment

by:jl66
ID: 34936073
Excellent.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question