Home » SQL & PL/SQL » SQL & PL/SQL » Using a variable table name
icon5.gif  Using a variable table name [message #113037] Wed, 30 March 2005 13:21 Go to next message
aj_dee
Messages: 2
Registered: March 2005
Junior Member
Hi!

I need to use a table name in a FROM clause, generated dynamically. I know this is possible in SQL*Plus (&table_parameter), but I'm wonderring if this could be done in a regular SQL query.

I have a query which returns table name and an ID which should be looked up in this table. I want it to be combined with another query which will do the actual fetching.
Re: Using a variable table name [message #113043 is a reply to message #113037] Wed, 30 March 2005 13:58 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Can't do it in statis sql query, need to use dynamic sql or a ref cursor.
Re: Using a variable table name [message #113176 is a reply to message #113037] Thu, 31 March 2005 06:44 Go to previous messageGo to next message
aj_dee
Messages: 2
Registered: March 2005
Junior Member
Martin, what is the meaning of "dynamic sql" ? Using a proc and variables binding?

Could you give an example, please.
Re: Using a variable table name [message #113202 is a reply to message #113037] Thu, 31 March 2005 10:13 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
There are tons of different things you can do, and ways to do it. Using the execute immediate statement, using the dbms_sql package. Using ref cursors. Be careful though anytime you have dynamic sql though in that you don't take whatever a user enters and paste it right in your query, becuase that is a really big security hole.

Below is a tiny example. But I'd suggest you read chapters 6 and 7 of
http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10807/toc.htm

MYDBA@ORCL > 
MYDBA@ORCL > create or replace procedure myproc(p_table_name varchar2)
  2  is
  3  	     sqltxt varchar2(200);
  4  	     l_count pls_integer;
  5  begin
  6  	     sqltxt := 'select count(*) into :result from ' || p_table_name;
  7  	     dbms_output.put_line(sqltxt);
  8  
  9  	     execute immediate sqltxt into l_count;
 10  	     dbms_output.put_line(l_count);
 11  end;
 12  /

Procedure created.

MYDBA@ORCL > 
MYDBA@ORCL > exec myproc('all_objects');
select count(*) into :result from all_objects
39214

PL/SQL procedure successfully completed.

MYDBA@ORCL > exec myproc('all_tables');
select count(*) into :result from all_tables
117

PL/SQL procedure successfully completed.

MYDBA@ORCL > 
MYDBA@ORCL > drop procedure myproc;

Procedure dropped.

MYDBA@ORCL > 
MYDBA@ORCL > set echo off;
Previous Topic: Help Needed :: Query Execution
Next Topic: dbms_stats.create_stat_table
Goto Forum:
  


Current Time: Tue Apr 29 02:01:00 CDT 2025