• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2114
  • Last Modified:

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
0
mikezang
Asked:
mikezang
2 Solutions
 
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
 
mikezangAuthor Commented:
Can you explain more detail or give a smaple?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MohanKNairCommented:
The below link queries a function which returns a collection object.

http://www.oracle-base.com/dba/miscellaneous/get_pivot.sql
0
 
hardikbeitCommented:
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now