Solved

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

Posted on 2006-07-20
8
2,100 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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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

776 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