Can I use a parameter in SELECT FROM as table name?

I am making a common functuion, one parameter is a array table name, I need to select some columns from that table. Can I use as below?

Select *
FROM param1
LVL 5
mikezangAsked:
Who is Participating?
 
MohanKNairConnect With a Mentor Commented:
The below link queries a function which returns a collection object.

http://www.oracle-base.com/dba/miscellaneous/get_pivot.sql
0
 
sathyagiriCommented:
You can use dynamic query to do that

Example

qry_string := 'select * from ' || param1;
execute immediate qry_string into var;
0
 
MohanKNairCommented:
If the function rsturns the colelction object then it can be used in select statements

select * from TABLE(<function name>);
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
mikezangAuthor Commented:
Can you explain more detail or give a smaple?
0
 
hardikbeitConnect With a Mentor Commented:
Hi,

'Execute immediate' allows to execute a dynamic SQL statement. This statement is a string. bind-var-n cannot be the literal null. Use a dummy variable instead whose value is null.

One Example is:
create or replace function count_in_table
  (attr in varchar2, attrval in varchar2, tbl in varchar2)
  return number
is
  cnt number;
begin
  execute immediate 'select count(1) from ' || tbl || ' where ' || attr || ' = :a' into cnt using attrval;
  return cnt;
end;

In this command attr: which column you want to filter and attrval: column value for filtering process.

Let me know in case of any query.

With regards, Hardik
0
 
hardikbeitCommented:
Hi,

For more details related EXECUTE IMMIDIATE or Dynamic SQL:
http://www.utexas.edu/its/unix/reference/oracledocs/v92/B10501_01/appdev.920/a96624/11_dynam.htm

With regards, Hardik
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.