Re: PL/SQL Package DBMS_SQL Question (Newbie)

In article <d42fa30f.0207310815.71e0820f_at_posting.google.com>, cfvigil_at_cs.com says...
>
>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);
>end;
>
>procedure created;
>
>execute translate_values('MY_TABLE')
>
>-- I get this error stack :
>
>ERROR at line 1:
>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
Hi, Christine:

Try changing:
'update' || v_table ||

to be:
'update ' || v_table || ' ' ||

"update", the table name, and "set" are probably being combined into one command ('updateMY_TABLEset') and the parser is failing because of it.

                                           Rick

                                Rick Wessman
                                Oracle Corporation

     The opinions expressed above are mine and do not necessarily reflect
                         those of Oracle Corporation.