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

[Michele (Mike) Alberton]

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]

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

[Jean-Luc Oudart]

Hi Mike,

find an example script that uses dynamic SQL

Regards,
Jean-Luc

[Printaporn_1]

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;
/

[Michele (Mike) Alberton]

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]

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]

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]

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]

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]

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]

Brian,

that's great stuff.

Definitely what I needed, thanks a lot !

Cheers,

Mike