PL/SQL Variables replacing table name

[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