Using a variable table name [message #113037] |
Wed, 30 March 2005 13:21  |
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 #113202 is a reply to message #113037] |
Thu, 31 March 2005 10:13  |
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;
|
|
|