General
cancel
Showing results for 
Search instead for 
Did you mean: 

PL/SQL Variables replacing table name

SOLVED
Go to solution
Michele (Mike) Alberton
Regular Advisor

PL/SQL Variables replacing table name

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
20 REPLIES
Nicolas Dumeige
Esteemed Contributor

Re: PL/SQL Variables replacing table name

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 ....
All different, all Unix
Michele (Mike) Alberton
Regular Advisor

Re: PL/SQL Variables replacing table name

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
Honored Contributor

Re: PL/SQL Variables replacing table name

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
fiat lux
Nicolas Dumeige
Esteemed Contributor

Re: PL/SQL Variables replacing table name

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;



All different, all Unix
Steve Bear_1
Frequent Advisor

Re: PL/SQL Variables replacing table name

use DBMS_SQL package.

Michele (Mike) Alberton
Regular Advisor

Re: PL/SQL Variables replacing table name

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

Cheers

Mike
Sanjay Kumar Suri
Honored Contributor

Re: PL/SQL Variables replacing table name

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

The system will prompt for the table name.

sks
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Brian Crabtree
Honored Contributor
Solution

Re: PL/SQL Variables replacing table name

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
Regular Advisor

Re: PL/SQL Variables replacing table name

Thanks Brian,

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

I appreciated !

Mike
Michele (Mike) Alberton
Regular Advisor

Re: PL/SQL Variables replacing table name

Hi !

Just another question, that works ok for select statement, what if I have to declare a cursor referencing a table that way ?

Thanks !

Mike
Nicolas Dumeige
Esteemed Contributor

Re: PL/SQL Variables replacing table name

Look for dynamic cursor, you find a lot a stuff on this.

For instance : http://www.unix.org.ua/orelly/oracle/bipack/ch02_01.htm

http://www.unix.org.ua/orelly/oracle/advprog/ch11_05.htm

All different, all Unix
Jean-Luc Oudart
Honored Contributor

Re: PL/SQL Variables replacing table name

Hi Mike,

find an example script that uses dynamic SQL

Regards,
Jean-Luc
fiat lux
Printaporn_1
Esteemed Contributor

Re: PL/SQL Variables replacing table name

Hi Mike,

My dynamic SQL

declare
ojn varchar2(100);
dml_str VARCHAR2(200);
t_count number;
t_name varchar2(30);
CURSOR c1 IS
SELECT table_name FROM user_tables WHERE temporary <> 'Y';
BEGIN
open c1;
loop
fetch c1 into ojn;
exit when c1%NOTFOUND;
dml_str := 'select * from '||ojn ||' ';
execute immediate dml_str into t_count;
dbms_output.put_line(ojn || ' Have ' || t_count || ' records');
END LOOP;
end;
/
enjoy any little thing in my life
Michele (Mike) Alberton
Regular Advisor

Re: PL/SQL Variables replacing table name

Thanks to all,

it looks good, my only concern is that I need to define the initial cursor with a variable, so that I think the only way would be to use DBMS_SQL. In most of your examples the cursor driving the LOOP does not include any variable, but in my case that's the issue.

Thanks a lot folks, now I'm definitely in better shape.

Cheers,

Mike

Brian Crabtree
Honored Contributor

Re: PL/SQL Variables replacing table name

Mike,

List out an idea of what you are looking for. Is the list of tables going to come from another table or view, and the variable is going to be used from there?

Give a quick outline of what you are looking for.

Thanks,

brian
Michele (Mike) Alberton
Regular Advisor

Re: PL/SQL Variables replacing table name

Sure,

my tablename is not known at runtime, so that I need to gather it from another table.

The tablename extracted will be driving a cursor.

Basically I need to define something like:

DECLARE

CURSOR myown_crs IS SELECT MYOWNFLD FROM &&TABLENAME;

BEGIN
<>
LOOP
FETCH myown_crs INTO MYOWNFLDVAR;
...
END LOOP;
CLOSE myown_crs;
END;
/

I don't know the tablename. Could I execute immediate the FETCH statement ?

Thanks !

Mike
Michele (Mike) Alberton
Regular Advisor

Re: PL/SQL Variables replacing table name

So far I bypassed the issue getting first the tablename through sqlplus and then passing it as an external variable to PL/SQL, but it's definitely not very elegant.

Mike
Brian Crabtree
Honored Contributor

Re: PL/SQL Variables replacing table name

Try this out:
--------------------
create or replace procedure tmpproc(tblname varchar2) as
type tblcur_type is REF CURSOR;
tblcur tblcur_type;
tmpvar varchar2(100);
sqlstatement varchar2(100);
begin
sqlstatement:='select field from '||tblname;
open tblcur for sqlstatement;
loop
fetch tblcur into tmpvar;
exit when tblcur%notfound;
dbms_output.put_line(tmpvar);
end loop;
close tblcur;
end;
/
------------
Most likely, another procedure would be needed for this as well. One to output the table name into the procedure, and this one to do the extra work. Probably easier than trying to do two things in the same procedure.

Let me know if this helps.

Thanks,

Brian
Michele (Mike) Alberton
Regular Advisor

Re: PL/SQL Variables replacing table name

Cool stuff Brian,

I've never used the OPEN FOR statement, it looks interesting. Let me play with it and let you know.

Thanks !

Mike
Michele (Mike) Alberton
Regular Advisor

Re: PL/SQL Variables replacing table name

Brian,

that's great stuff.

Definitely what I needed, thanks a lot !

Cheers,

Mike