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

x
?
Solved

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

Posted on 2006-07-20
8
Medium Priority
?
2,108 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
[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
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 16

Accepted Solution

by:
MohanKNair earned 100 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 100 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

705 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