topic Re: PL/SQL Variables replacing table name in Operating System - HP-UX

PL/SQL Variables replacing table name

Michele (Mike) Alberton — Thu, 25 Mar 2004 09:19:21 GMT

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

Re: PL/SQL Variables replacing table name

Nicolas Dumeige — Thu, 25 Mar 2004 09:32:45 GMT

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 ....

Re: PL/SQL Variables replacing table name

Michele (Mike) Alberton — Thu, 25 Mar 2004 09:37:28 GMT

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

Re: PL/SQL Variables replacing table name

Jean-Luc Oudart — Thu, 25 Mar 2004 09:45:09 GMT

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

Re: PL/SQL Variables replacing table name

Nicolas Dumeige — Thu, 25 Mar 2004 10:04:58 GMT

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;

Re: PL/SQL Variables replacing table name

Steve Bear_1 — Thu, 25 Mar 2004 11:35:52 GMT

use DBMS_SQL package.

Re: PL/SQL Variables replacing table name

Michele (Mike) Alberton — Fri, 26 Mar 2004 04:25:43 GMT

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

Cheers

Mike

Re: PL/SQL Variables replacing table name

Sanjay Kumar Suri — Fri, 26 Mar 2004 04:41:21 GMT

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

The system will prompt for the table name.

sks

Re: PL/SQL Variables replacing table name

Brian Crabtree — Mon, 29 Mar 2004 18:22:42 GMT

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

Re: PL/SQL Variables replacing table name

Michele (Mike) Alberton — Tue, 30 Mar 2004 02:23:12 GMT

Thanks Brian,

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

I appreciated !

Mike

Re: PL/SQL Variables replacing table name

Michele (Mike) Alberton — Tue, 30 Mar 2004 02:57:04 GMT

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

Re: PL/SQL Variables replacing table name

Nicolas Dumeige — Tue, 30 Mar 2004 03:03:48 GMT

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

Re: PL/SQL Variables replacing table name

Jean-Luc Oudart — Tue, 30 Mar 2004 03:26:42 GMT

Hi Mike,

find an example script that uses dynamic SQL

Regards,
Jean-Luc

Re: PL/SQL Variables replacing table name

Printaporn_1 — Wed, 31 Mar 2004 00:55:31 GMT

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

Re: PL/SQL Variables replacing table name

Michele (Mike) Alberton — Wed, 31 Mar 2004 01:42:27 GMT

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

Re: PL/SQL Variables replacing table name

Brian Crabtree — Wed, 31 Mar 2004 16:53:54 GMT

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

Re: PL/SQL Variables replacing table name

Michele (Mike) Alberton — Wed, 31 Mar 2004 17:34:02 GMT

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

Re: PL/SQL Variables replacing table name

Michele (Mike) Alberton — Wed, 31 Mar 2004 17:36:22 GMT

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

Re: PL/SQL Variables replacing table name

Brian Crabtree — Thu, 01 Apr 2004 03:19:17 GMT

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

Re: PL/SQL Variables replacing table name

Michele (Mike) Alberton — Thu, 01 Apr 2004 04:18:55 GMT

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