Solved

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

Posted on 2006-07-20
8
2,102 Views
Last Modified: 2012-06-21
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
Comment
Question by:mikezang
8 Comments
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17151376
You can use dynamic query to do that

Example

qry_string := 'select * from ' || param1;
execute immediate qry_string into var;
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 17151814
If the function rsturns the colelction object then it can be used in select statements

select * from TABLE(<function name>);
0
 
LVL 5

Author Comment

by:mikezang
ID: 17152351
Can you explain more detail or give a smaple?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 16

Accepted Solution

by:
MohanKNair earned 25 total points
ID: 17152407
The below link queries a function which returns a collection object.

http://www.oracle-base.com/dba/miscellaneous/get_pivot.sql
0
 
LVL 3

Assisted Solution

by:hardikbeit
hardikbeit earned 25 total points
ID: 17152410
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
 
LVL 3

Expert Comment

by:hardikbeit
ID: 17152433
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

827 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