Solved

how to deal with table variable in pl/sql

Posted on 2011-02-19
3
542 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
  • 2
3 Comments
 
LVL 4

Accepted Solution

by:
msd1305 earned 500 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 500 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now