PL/SQL Variables replacing table name

[Michele (Mike) Alberton]

Hi,

I would like to run a select from tablevar where....

Now the table_name is not known in advance so that I would like to use a variable to store the table name.


Using shell language, I would like to have something like:

select * from $tablename where....

How can I do that ?

Thanks !

Mike

[Nicolas Dumeige]

Using the shell

#!/bin/ksh

sqlplus -s $login/$password@$ORACLE_SID << EOF >> $LOG
select * from $table where ....
EOF

in a sql script
select * from &1 where ....

[Michele (Mike) Alberton]

I understand but if I do this:


SELECT TABLE_NAME INTO TABLEVAR FROM ALL_ALL_TABLES WHERE...

if I want to use TABLEVAR to reference the table I'm interested in, how can I run

SELECT * FROM TABLEVAR WHERE.... ?

How can I reference TABLEVAR without passing externally the value to &1 ?

Thanks !

Mike

[Jean-Luc Oudart]

One way is to create a function or procedure with the tablename for parameter.

If you can program in PLSQL this is not a big deal.

Regards,
Jean-Luc

[Nicolas Dumeige]

SQL*Plus can help you :

col table_name new_value my_table

-- This one put the table name in my_table
SELECT table_name FROM tabs WHERE your_condition;

-- This one do the job
SELECT * from &my_table;

[Steve Bear_1]

use DBMS_SQL package.

[Michele (Mike) Alberton]

Thanks to all, I'm going to practice a little bit more with DBMS_SQL package.

Cheers

Mike

[Sanjay Kumar Suri]

Example:
SQL>select * from &table;
SQL>save ex01
SQL>@ex01

The system will prompt for the table name.

sks

[Brian Crabtree · Accepted solution]

The following should put you on the right track. I wouldn't use DBMS_SQL unless you have to. 'Execute immediate' was introduced in 8i, and works well for this sort of thing. If you are still on 8.0, then you will need to use DBMS_SQL.

--------------------
set serveroutput on
declare
tablename varchar2(20) := 'tableA';
sqlstatement varchar2(100);
tempvar varchar2(10);
begin
sqlstatement:='select count(*) from '||tablename;
execute immediate sqlstatement into tempvar;
dbms_output.put_line('Tempvar '||tempvar);
end;
/
-----------------

Thanks,

Brian

[Michele (Mike) Alberton]

Thanks Brian,

that's definitely what I was looking for, nice and easy.

I appreciated !

Mike