PL/SQL Package DBMS_SQL Question (Newbie)

What I am trying to do is replace a value(rowlabel) with a new value from a translation table column - in order to provide it with a new updated rowlabel.

TRANSLATION TABLE has 2 columns, one with the old version of the rowlabel and one with the new version. I need to replace the old with the new on numerous tables and am trying to create a procedure to do so.

The update statement works alone when used with a hard coded table value.
 However when used in the procedure, I get the error messages below.

Can you please help me?

Here is an example of my script.

create or replace procedure translate_values(v_table in varchar2) as

v_cur number;
v_stat number;
begin

	v_cur := dbms_sql.open_cursor;
	dbms_sql.parse (v_cur,


			'update' || v_table ||


			'set label_to_char(rowlabel) =' || ' 


                        (select label_to_char(sp_label)
			 from translation_table t
	where t.a_label = label_to_char(v_table.rowlabel)',dbms_sql.native);

	v_stat := dbms_sql.execute(v_cur);
	dbms_sql.close_cursor(v_cur);

ORA-00900: invalid SQL statement
ORA-06512: at "SYS.DBMS_SYS_SQL", line 826
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "MYSCHEMA.TRANSLATE_VALUES", line 10
ORA-06512: at line 1

Thank you kindly,

Christine Received on Wed Jul 31 2002 - 18:15:58 CEST