oracle生产环境存储过程调试方案
我们的程序部署到客户生产环境后，业务存储过程会出现一些意外报错的情况，由于之前没有记录存储过程入参，
并且入参都是自定义类型的，给排查问题带来了很大困扰；下面举例说明如何来记录存储过程入参，并记录起来，便于后续调试；
测试的存储过程如下：
procedure t_insbond_confirm(
iCommonObj IN CommonObj,
iTInsbondConfirmP1List IN InTInsbondConfirmP1List,
oTInsbondConfirmP1List OUT pg_basic_common.DS,
oErrorId OUT NUMBER,
oErrorMessage OUT VARCHAR2)
在存储过程执行进来后我们将参数按照如下方式保存
exception
when others then
oErrorId := SQLCODE;
oErrorMessage := sqlerrm;
pkg_log.ERROR(f_getprocname,'t_insbond_confirm','oErrorId='||oErrorId||',oErrorMessage='||oErrorMessage||','||
DBMS_UTILITY.format_error_backtrace ()||chr(13)||'CommonObj(instId => '||
iCommonObj.instId||',userId => '||iCommonObj.userId||',userName => '||
iCommonObj.userName||',userClass => '||iCommonObj.userClass ||',opInstId => '||
iCommonObj.opInstId || ',menuId => ' || iCommonObj.menuId||',menuRightId => ' ||
iCommonObj.menuRightId || ',serviceName => ' || iCommonObj.serviceName || ', clientType => ' ||
iCommonObj.clientType || ', checkUser => ' || iCommonObj.checkUser || ', stationId => ' ||
iCommonObj.stationId|| ', opDetail => ' || iCommonObj.opDetail || ', orderName => ' ||
iCommonObj.orderName || ', orderType => ' || iCommonObj.orderType || ', remarkLong => ' ||
iCommonObj.remarkLong || ')'||chr(13)||'InTInsbondConfirmP1('||vOIns.toString||')');
rollback;
记录的日志文本为：
oErrorId=100,oErrorMessage=ORA-01403: 未找到任何数据,ORA-06512: 在 "XC_PRODUCT.OIS_TRADE_BOND", line 805
CommonObj(instId => 1,userId => 999999,userName => 1000001,userClass => 1,opInstId => 1,menuId => 0,menuRightId => 0,serviceName => test, clientType => 0, checkUser => 0, stationId => 0, opDetail => xxxx, orderName => xxxx, orderType => 1, remarkLong => xxx)
InTInsbondConfirmP1(ins_id=122, clear_speed=0, net_price=99.8, full_price=100, par_value=0, interest=178, bond_yield=.66, rival_id=111, rival_trade=1000, clear_type=0, settlement_fee=10, trade_fee=19.8, settle_type=1, deal_no=99998888, remark=xxxxxx)
上面已经提示了是805行执行出错了，这样只需要将入参数据拷贝到Test窗口稍加改造就能进行现场调试；
declare
-- Non-scalar parameters require additional processing
icommonobj commonobj := CommonObj(instId => 1,
userId => 999999,
userName => 1000001,
userClass => 1,
opInstId => 1,
menuId => 0,
menuRightId => 0,
serviceName => 'test',
clientType => 0,
checkUser =>0,
stationId => 0,
opDetail => 'xxxx',
orderName => 'xxxx',
orderType => 1,
remarkLong => 'xxx');
p1 InTInsbondConfirmP1 := InTInsbondConfirmP1(ins_id => 122,
clear_speed => 0,
net_price => 99.8,
full_price => 100,
par_value => 0,
interest => 178,
bond_yield => .66,
rival_id => 111,
rival_trade => 1000,
clear_type => 0,
settlement_fee => 10,
trade_fee => 19.8,
settle_type => 1,
deal_no =>
'99998888',
remark =>
'xxxxxx');
itinsbondconfirmp1list intinsbondconfirmp1list := intinsbondconfirmp1list(p1);
begin
-- Call the procedure
ois_trade_bond.t_insbond_confirm(icommonobj => icommonobj,
itinsbondconfirmp1list => itinsbondconfirmp1list,
otinsbondconfirmp1list => :otinsbondconfirmp1list,
oerrorid => :oerrorid,
oerrormessage => :oerrormessage);
end;
生产上一旦出现异常，记录异常数据数据至关重要；
