获取异常的具体出处——dbms_utility.format_error_backtrace

本文介绍Oracle PL/SQL中使用DBMS_UTILITY.FORMAT_ERROR_BACKTRACE函数进行异常追踪的方法。通过示例展示了如何在多层存储过程中捕捉并打印异常传递路径,帮助开发者定位问题。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE 

系统为最近一次生成的异常设置了一个栈,并跟踪它的传递过程,而这个函数使用这个栈,然后返回该异常的整个传递过程。这个函数对错误的定位和实施下一步处理起着至关重要的作用。

例如:

CREATE OR REPLACE PROCEDURE procl IS
BEGIN
  dbms_output.put_line('running proc1');
  RAISE no_data_found;
END;
/

CREATE OR REPLACE PROCEDURE proc2 IS
BEGIN
  dbms_output.put_line('calling proc1');
  proc1;
END;
/

CREATE OR REPLACE PROCEDURE proc3 IS
BEGIN
  dbms_output.put_line(‘calling proc2’);
  proc2;
EXCEPTION
  WHEN no_data_found THEN  
    dbms_output.put_line(‘error stack at top level’);  
    dbms_output.put_line(dbms_utility.format_error_backtrace);
END;
/

现在可以运行 proc3 来看看结果。

Sql>set serveroutput on; 

Sql>begin 

2 dbms_output.put_line(‘proc3->proc2->proc1 backtrace’); 

3 proc3; 

4 end; 

5 / 

Proc3 -> Proc2 -> Proc1 backtrace 

calling proc2 

calling proc1 

running proc1 

Error stack at top level: 

ORA-06512: at "SCOTT.PROC1", line 4 

ORA-06512: at "SCOTT.PROC2", line 5 

ORA-06512: at "SCOTT.PROC3", line 4 

 

事实上,每次异常的产生都将重置这个异常栈,只是最后一次从系统栈出栈的是最外层的程序块,所以可以清楚地看到异常生成的整个过程。上面这个程序的执行过程是这样的:首先用 put_line 打印 Proc3 -> Proc2 -> Proc1 backtrace , 调用 proc3 ,当前程序入栈 => 打印 calling proc2 ,调用proc2 , proc3 入栈 => 打印 calling proc1 ,调用 proc1 , proc2 入栈 => 打印 running proc1 ,生成 no_data_found 异常,该异常被压入异常栈中=>proc2 出栈,并检测到来自第 5 行调用传递过来的异常,将它在此压入异常栈 => proc3 出栈,并检测到来自第 4 行调用传递过来的异常,将它在此压入异常栈,dbms_utility.format_error_backtrace 将异常栈中信息反相打印出来 => 最外层程序出栈, end 。 


以下是正确使用这个函数的一些注意事项: 

1)在当前程序的异常处理模块中调用这个函数。 

2)避免在中间程序中使用异常处理模块。 

这样异常就能被正确地传输到最外层程序中,并打印出这个过程了。

转载于:https://www.cnblogs.com/Cqiang/p/3192336.html

/* Formatted on 2025/7/23 19:23:00 (QP5 v5.326) */ CREATE OR REPLACE PROCEDURE IMES.SP_MES_GET_STATION_INFO_V1 ( T_STATION_NAME IN VARCHAR2, T_LINE OUT VARCHAR2, T_STAGE OUT VARCHAR2, T_STATION_TYPE OUT VARCHAR2, T_MODEL OUT VARCHAR2, TRES OUT VARCHAR2) IS V_TEST_STATION_NAME VARCHAR2 (60); V_COUNT NUMBER; --modify by penn 2021/8/24 PDCA站点根据customer_station_type 转换为station_type --modify by ellis 2024/01/17 增加机种信息 --modify by dulin 2025/02/26 取消通过IMES.M_STATION.STATION_TYPE串 IMES.M_STATION_TYPE.stationtype_customer 逻辑 BEGIN TRES := 'OK'; SELECT A.LINE, A.STAGE, A.STATION_TYPE, B.MODEL INTO T_LINE, T_STAGE, T_STATION_TYPE, T_MODEL FROM IMES.M_STATION A, IMES.M_LINE B WHERE A.LINE = B.LINE AND A.STATION_NAME = T_STATION_NAME AND A.ENABLED = 'Y' AND B.ENABLED = 'Y' AND ROWNUM = 1; EXCEPTION WHEN NO_DATA_FOUND THEN SELECT SUBSTR (T_STATION_NAME, INSTR (T_STATION_NAME, '_', -1, 1) + 1) INTO V_TEST_STATION_NAME FROM DUAL; ---取客户站点 SELECT SUBSTR ( T_STATION_NAME, INSTR (T_STATION_NAME, '_', 1) + 1, INSTR (T_STATION_NAME, '_', INSTR (T_STATION_NAME, '_', 1) + 1) - INSTR (T_STATION_NAME, '_', 1) - 1) INTO T_LINE FROM DUAL; ----取线体 BEGIN SELECT A.STAGE, A.STATION_TYPE INTO T_STAGE, T_STATION_TYPE FROM IMES.M_STATION_TYPE A WHERE A.stationtype_customer = V_TEST_STATION_NAME AND ENABLED = 'Y' --AND STAGE = T_STAGE --抓取到客户站点只有一笔直接抓出,多个站点直接抓空 AND CLIENT_TYPE = 'PDCA' AND ROWNUM = 1; EXCEPTION WHEN OTHERS THEN T_LINE := T_LINE; T_STAGE := ''; T_STATION_TYPE := ''; TRES := IMES.FN_MES_ERROR_CODE_TRANSLATE ('A0002', 'ZH', ''); END; WHEN OTHERS THEN --TRES := SQLERRM || '<' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || '>'; TRES := IMES.FN_MES_ERROR_CODE_TRANSLATE ( 'B0001', 'ZH', 'SP_MES_GET_STATION_INFO_V1' || SQLERRM || '<' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || '>'); END; /
07-24
CREATE OR REPLACE PROCEDURE IMES.SP_MES_GET_SN_ATTR_INFO(T_SN IN VARCHAR2, T_STATIONNAME IN VARCHAR2, TCONTENT OUT VARCHAR2, TRES OUT VARCHAR2) IS /* create by ellis 2024/08/06 V1.0.0.0 组装综合作业获取SN参数,默认获取工单,料号,工单线体,ROUTE,工单颜色,K&NK */ V_COUNT NUMBER; V_LINE IMES.P_SN_STATUS.LINE%TYPE; V_STAGE IMES.P_SN_STATUS.STAGE%TYPE; V_STATION_TYPE IMES.P_SN_STATUS.STATION_TYPE%TYPE; V_WO IMES.P_SN_STATUS.WORK_ORDER%TYPE; V_MODEL IMES.P_SN_STATUS.MODEL%TYPE; V_WO_LINE IMES.P_SN_STATUS.LINE%TYPE; V_IPN IMES.P_SN_STATUS.IPN%TYPE; V_KOLDATA VARCHAR2(10); V_SQL varchar2(4000); V_WO_COLOR IMES.M_PART.PRODUCTDESCCOLOR1%TYPE; V_CUSTRULE varchar2(200); BEGIN TCONTENT:='{'; IMES.SP_MES_GET_STATION_INFO_V1(T_STATIONNAME, V_LINE, V_STAGE, V_STATION_TYPE, V_MODEL, TRES); IF TRES <>'OK' THEN GOTO ENDP; END IF; SELECT COUNT(1) INTO V_COUNT FROM IMES.P_SN_STATUS WHERE SERIAL_NUMBER=T_SN ; IF V_COUNT =0 THEN TRES := IMES.FN_MES_ERROR_CODE_TRANSLATE('A0001', 'ZH', T_SN); GOTO ENDP; END IF; SELECT WORK_ORDER,MODEL,IPN INTO V_WO,V_MODEL,V_IPN FROM IMES.P_SN_STATUS WHERE SERIAL_NUMBER=T_SN; TCONTENT:=TCONTENT||'"TWO":"'||V_WO||'"'; TCONTENT:=TCONTENT||',"TMODEL":"'||V_MODEL||'"'; TCONTENT:=TCONTENT||',"TIPN":"'||V_IPN||'"'; SELECT COUNT(1) INTO V_COUNT FROM IMES.P_WO_BASE WHERE WORK_ORDER=V_WO; IF V_COUNT =0 THEN TRES := IMES.FN_MES_ERROR_CODE_TRANSLATE('A0714', 'ZH', V_WO); GOTO ENDP; END IF; SELECT LINE INTO V_WO_LINE FROM IMES.P_WO_BASE WHERE WORK_ORDER=V_WO; TCONTENT:=TCONTENT||',"TWOLINE":"'||V_WO_LINE||'"'; SELECT COUNT(1) INTO V_COUNT FROM IMES.M_PART WHERE IPN=V_IPN; IF V_COUNT =0 THEN TRES := IMES.FN_MES_ERROR_CODE_TRANSLATE('A0005', 'ZH', ''); GOTO ENDP; END IF; SELECT DECODE (UPPER (SUBSTR (RADIO, -2, 2)), 'NK', 'NK', 'K'),PRODUCTDESCCOLOR1 INTO V_KOLDATA,V_WO_COLOR FROM IMES.M_PART WHERE IPN=V_IPN; TCONTENT:=TCONTENT||',"TKOLDATA":"'||V_KOLDATA||'"'; TCONTENT:=TCONTENT||',"TWOCOLOR":"'||V_WO_COLOR||'"'; --特定属性 SELECT COUNT(1) INTO V_COUNT FROM IMES.M_BLOCK_CONFIG_TYPE A, IMES.M_BLOCK_CONFIG_VALUE B WHERE A.CONFIG_TYPE_NAME = 'Config_AssemblyWork_SN_ATTR_SQL' AND A.ENABLED = 'Y' AND B.ENABLED = 'Y' AND A.CONFIG_TYPE_ID = B.CONFIG_TYPE_ID AND (B.LINE IS NULL OR B.LINE = '*') AND B.STATION_TYPE = V_STATION_TYPE AND B.MODEL IN ('*', V_MODEL); IF V_COUNT>0 THEN FOR CURR_SQL IN (SELECT B.CONFIG_VALUE ATTR_SQL,B.CONFIG_NAME ATTR_NAME FROM IMES.M_BLOCK_CONFIG_TYPE A, IMES.M_BLOCK_CONFIG_VALUE B WHERE A.CONFIG_TYPE_NAME = 'Config_AssemblyWork_SN_ATTR_SQL' AND A.ENABLED = 'Y' AND B.ENABLED = 'Y' AND A.CONFIG_TYPE_ID = B.CONFIG_TYPE_ID AND (B.LINE IS NULL OR B.LINE = '*') AND B.STATION_TYPE = V_STATION_TYPE AND B.MODEL IN ('*', V_MODEL) ORDER BY MODEL DESC) LOOP V_SQL:= CURR_SQL.ATTR_SQL; IF INSTR(V_SQL, '@SN') > 0 THEN V_SQL := REPLACE(V_SQL, '@SN', '''' || T_SN || ''''); END IF; IF INSTR(V_SQL, '@STATION_NAME') > 0 THEN V_SQL := REPLACE(V_SQL, '@STATION_NAME', '''' || T_STATIONNAME || ''''); END IF; IF INSTR(V_SQL, '@STATION_TYPE') > 0 THEN V_SQL := REPLACE(V_SQL, '@STATION_TYPE', '''' || V_STATION_TYPE || ''''); END IF; IF INSTR(V_SQL, '@LINE') > 0 THEN V_SQL := REPLACE(V_SQL, '@LINE', '''' || V_MODEL || ''''); END IF; IF INSTR(V_SQL, '@MODEL') > 0 THEN V_SQL := REPLACE(V_SQL, '@MODEL', '''' || V_MODEL || ''''); END IF; EXECUTE IMMEDIATE V_SQL INTO V_CUSTRULE; TCONTENT:=TCONTENT||',"'||CURR_SQL.ATTR_NAME||'":"'||V_CUSTRULE||'"'; END LOOP; END IF; <<ENDP>> NULL; TCONTENT:=TCONTENT||'}'; EXCEPTION WHEN OTHERS THEN TRES := IMES.FN_MES_ERROR_CODE_TRANSLATE('B0001', 'ZH', 'SP_MES_GET_SN_ATTR_INFO#'||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ); END; /
最新发布
07-30
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值