Dynamic Update query in procedure

Question

I'm trying to create an Dynamic UPDATE query for a column listed out in all tables in DB. However, the query is failing with the error Code: -942 Message: ORA-00942: table or view does not exist ORA-06512: at "MANTAS.P_JRSDCN_TR", line 14.

Code:-

CREATE or REPLACE PROCEDURE P_JRSDCN_TR
(
out_error_cd out number,        -- Returns 0 if no error; anything else is an error
out_error_msg out varchar2       -- Returns empty string if no error; otherwise the error and trace
)AUTHID CURRENT_USER
IS
    counter  number(20) :=0;   
CURSOR TAB_COL_CURSOR IS 
SELECT DISTINCT OWNER||'.'||TABLE_NAME as TABLE_NAME,COLUMN_NAME FROM ALL_TAB_COLS WHERE TABLE_NAME IN ('KDD_REVIEW') AND COLUMN_NAME='JRSDCN_CD';
 BEGIN  
     FOR TAB_COL_REC  IN TAB_COL_CURSOR
     LOOP

EXECUTE IMMEDIATE 'UPDATE TAB_COL_REC.TABLE_NAME SET TAB_COL_REC.COLUMN_NAME = P2||SUBSTR(TAB_COL_REC.COLUMN_NAME,3) WHERE SUBSTR(TAB_COL_REC.COLUMN_NAME,1,2)= PL';

counter := counter +SQL%rowcount ;
 If counter >= 50000 then
     counter := 0;
     --commit;
    end if;

-- Done!    
    out_error_cd := 0;
    out_error_msg := '';
    dbms_output.put_line('Turkey Jurisdiction Update completed sucessfully at ' || to_char(sysdate,'MM/dd/yyyy HH24:MI:SS'));
        END Loop;
exception when others then
        rollback;
        out_error_cd := SQLCODE;
        out_error_msg := substr(sqlerrm, 1, 200) || chr(10) || substr(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 1, 3896);        
        dbms_output.put_line('  Code: ' || out_error_cd);
        dbms_output.put_line('  Message: ' || out_error_msg);
        dbms_output.put_line('Turkey Jurisdiction Update FAILED at ' || to_char(sysdate,'MM/dd/yyyy HH24:MI:SS'));
 end;
 /

Appreciate your help on this procedure.

A.B.Cade A.B.Cade · Accepted Answer · 2013-05-22T04:58:31

There is probably no table called TAB_COL_REC.TABLE_NAME

You probably wanted something like this:

EXECUTE IMMEDIATE 'UPDATE ' || TAB_COL_REC.TABLE_NAME || ' SET ' || TAB_COL_REC.COLUMN_NAME || ' = P2||SUBSTR(' || TAB_COL_REC.COLUMN_NAME ||',3) WHERE SUBSTR(' || TAB_COL_REC.COLUMN_NAME || ',1,2)= PL';

Dynamic Update query in procedure

3 Answers