Home » SQL & PL/SQL » SQL & PL/SQL » Table_name as variable
Table_name as variable [message #217318] Thu, 01 February 2007 12:11 Go to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
I am writing a procedure and I have been able to use table names as variables via the execute immediate commands however I have run into an issue.

I am needing to build a cursor and a loop and am getting errors.

create or replace procedure test.test(
table_name varchar2)
is
CURSOR c1 is select seq_no, rowid from table_name ;

execute immediate
' begin
FOR c1rec in c1 LOOP

update '||table_name||
' set (field1) =
(select b.field1
from testschema.test_table b
where b.seq_no=c1rec.seq_no)
where rowid = c1rec.rowid;

END LOOP;
commit;


end';

Any help apprecciated
Re: Table_name as variable [message #217319 is a reply to message #217318] Thu, 01 February 2007 12:14 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>I am needing to build a cursor and a loop and am getting errors.
Errors? What errors? I don't see any error.
This is a bad, very bad "design".
This flavor of a "solution" does NOT scale.
This is a disaster waiting to implode.
Re: Table_name as variable [message #217320 is a reply to message #217318] Thu, 01 February 2007 12:25 Go to previous messageGo to next message
biohazardbill
Messages: 57
Registered: March 2006
Member
Ok let me rephrase...
since stripping down my procedure to a simple basis looks nasty.

How can one declare a cursor using a table as a variable name.

using execute immediate 'select * from '||table_name;
works

however trying to declare a cursor does not:

using execute immediate 'CURSOR c1 is select seq_no, rowid from '|| table_name ;


Thanks
Re: Table_name as variable [message #217323 is a reply to message #217318] Thu, 01 February 2007 12:39 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>however trying to declare a cursor does not:

>using execute immediate 'CURSOR c1 is select seq_no, rowid from '|| table_name ;

It appears you are confusing SQL with PL/SQL. They are NOT the same language.

EXECUTE IMMEDIATE allows one to "run" valid SQL (usually DDL) from within a PL/SQL procedure.

While a shovel is a great tool for digging a hole in the ground,
it is most effective when when the metal portion comes into contact with the Earth & not the wooden end.

You are using the wrong end of this tool.

This "design" still is an abomination.
Re: Table_name as variable [message #233826 is a reply to message #217323] Fri, 27 April 2007 12:45 Go to previous message
psix666
Messages: 51
Registered: April 2007
Location: Azerbaijan
Member

Hi.

I don't exactly understand what u need but u can use ref cursor to show table_name as variable. Execute immediate or cursors does not allow table names as variables.

create or replace procedure proc_test(P_table_name varchar2) is
type t_cur is ref cursor;
cur t_cur;

v_str varchar2(1000);
begin
v_str := 'select * from '||p_table_name;
open t_cur for v_str;

/* your code here*/
end;
Previous Topic: how do I prevent Oracle from invoking a time-consuming table function unnecesarily?
Next Topic: Calculated Field Need SUM - Add- Subract
Goto Forum:
  


Current Time: Tue Dec 06 12:36:49 CST 2016

Total time taken to generate the page: 0.13266 seconds