过程中的动态更新查询 [英] Dynamic Update query in procedure
本文介绍了过程中的动态更新查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试为数据库中所有表中列出的列创建动态更新查询.但是,查询失败,错误为代码:-942消息:ORA-00942:表或视图不存在ORA-06512:在"MANTAS.P_JRSDCN_TR"的第14行.
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.
代码:-
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.
推荐答案
可能没有名为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';
这篇关于过程中的动态更新查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文