?
原始SQL:
執行出現ORA-03113錯誤,網絡正常,執行其他SQL也正常.
13:04:26 CRY@CRY> select operatetime,billtypename,billid,shopcode,shopname,goodscode,goodsn
ame,' ' 期初數量,sum(irealnumber) irealnumber,DECODE(sum(irealnumber),0,0,round(sum(imoney)/sum(irea
lnumber),2)) iprice,sum(imoney) imoney,sum(orealnumber) orealnumber,DECODE(sum(orealnumber),0,0,roun
d(sum(omoney)/sum(orealnumber),2)) oprice,sum(omoney) omoney,' ' 結存數量 from( select to_char(x.ope
ratetime, 'YYYY-MM-DD') operatetime,x.billtype,
13:04:28 2 fun_getname('BILLTYPEALL',x.billtype) billtypename,
13:04:28 3 x.billid,x.remark,x.originbillid,
13:04:28 4 fun_getname('OPERATIONTYPE',x.otherinouttype) otherinouttypename,
13:04:28 5 x.shopcode,
13:04:28 6 f_getOrganiseName(x.shopcode) shopname,
13:04:28 7 x.storecode,
13:04:28 8 f_getStoreName(x.storecode) storename,
13:04:28 9 FUN_GETTOUNIT(x.billtype,x.billid) unitname,
13:04:28 10 x.goodscode,
13:04:28 11 X.goodsname,
13:04:28 12 x.ShortCode,x.brandcode,x.model,
13:04:28 13 x.irealnumber,
13:04:28 14 abs(DECODE(x.irealnumber,0,0,round(x.imoney/x.irealnumber,4))) iprice,
13:04:28 15 x.imoney,x.orealnumber,
13:04:28 16 abs(DECODE(x.orealnumber,0,0,round(x.omoney/x.orealnumber,4))) oprice,
13:04:28 17 x.omoney
13:04:28 18 from (
13:04:28 19 select a.operatetime,
13:04:28 20 a.billtype,
13:04:28 21 a.billid,fun_getbillremark(a.billid) remark,fun_getbilloriginbillid(a.b
illid) originbillid,
13:04:28 22 a.otherinouttype,
13:04:28 23 a.shopcode,
13:04:28 24 a.storecode,
13:04:28 25 a.goodscode,
13:04:28 26 c.shortcode,c.brandcode,c.model,
13:04:28 27 c.goodsname,
13:04:28 28 sum(DECODE(b.ruleid,1,a.realnumber,2,-a.realnumber,0)) irealnumber,
13:04:28 29 sum(DECODE(b.ruleid,1,
13:04:28 30 round(a.realnumber * a.price,2),
13:04:28 31 2,
13:04:28 32 round(-1 * a.realnumber *a.price,2),
13:04:28 33 0)) imoney,
13:04:28 34 sum(DECODE(b.ruleid,2,a.realnumber,1,-a.realnumber,0)) orealnumber,
13:04:28 35 sum(decode(b.ruleid,2,
13:04:28 36 round(a.realnumber * a.price,2),
13:04:28 37 1,
13:04:28 38 round(-1 * a.realnumber * a.price,2),
13:04:28 39 0)) omoney
13:04:28 40 from TAB_A a, TAB_B b,TAB_C c
13:04:28 41 where a.billtype = b.billtype
13:04:28 42 and a.goodscode=c.goodscode and (a.shopcode = '0603')
13:04:28 43 group by a.operatetime,
13:04:28 44 a.billtype,
13:04:28 45 a.billid,
13:04:28 46 remark,
13:04:28 47 a.otherinouttype,
13:04:28 48 a.shopcode,
13:04:28 49 a.storecode,
13:04:28 50 a.goodscode,
13:04:28 51 c.shortcode,
13:04:28 52 c.goodsname,c.brandcode,c.model
13:04:28 53 ) x
13:04:28 54 where x.billtype in (select billtype from t_billlist where ruleid>0) and
13:04:28 55 x.operatetime >= to_date('2013-6-17','YYYY-MM-DD')
13:04:28 56 and x.operatetime < to_date('2013-6-18','YYYY-MM-DD')
13:04:28 57 ) a group by operatetime,billtypename,billid,shopcode,shopname,goodscode,goodsname or
der by operatetime,billtypename,billid,shopcode,shopname,goodscode,goodsname;
select operatetime,billtypename,billid,shopcode,shopname,goodscode,goodsname,' ' 期初數量,sum(irealn
umber) irealnumber,DECODE(sum(irealnumber),0,0,round(sum(imoney)/sum(irealnumber),2)) iprice,sum(imo
ney) imoney,sum(orealnumber) orealnumber,DECODE(sum(orealnumber),0,0,round(sum(omoney)/sum(orealnumb
er),2)) oprice,sum(omoney) omoney,' ' 結存數量 from( select to_char(x.operatetime, 'YYYY-MM-DD') ope
ratetime,x.billtype,
*
第 1 行出現錯誤:
ORA-03113: 通信通道的文件結尾
進程 ID: 20027
會話 ID: 201 序列號: 149
ERROR:
ORA-03114: 未連接到 ORACLE
已用時間: 00: 00: 02.01
?精簡改SQL如下后報ORA-600錯誤:
13:07:18 CRY@CRY> SELECT operatetime
13:07:19 2 FROM (SELECT to_char(x.operatetime, 'YYYY-MM-DD') operatetime
13:07:19 3 FROM (SELECT operatetime FROM t_storeout GROUP BY operatetime) x
13:07:19 4 WHERE x.operatetime > to_date('2012-01-01', 'YYYY-MM-DD')) a
13:07:19 5 GROUP BY operatetime;
FROM (SELECT operatetime FROM t_storeout GROUP BY operatetime) x
*
第 3 行出現錯誤:
ORA-00600: 內部錯誤代碼, 參數: [rwoirw: check ret val], [], [], [], [], [], [], [], [], [], [], []
已用時間: 00: 00: 01.68
分析此ORA-00600錯誤,發現是參數
"_complex_view_merging
"的影響,將此參數值設為FALSE后正常.
如是解決此ORA-00600錯誤后,此ORA-03113錯誤也就解決了,如下:
13:07:23 CRY@CRY> ALTER SESSION SET "_complex_view_merging"=false;
會話已更改。
已用時間: 00: 00: 00.01
13:08:43 CRY@CRY> select operatetime,billtypename,billid,shopcode,shopname,goodscode,goodsn
ame,' ' 期初數量,sum(irealnumber) irealnumber,DECODE(sum(irealnumber),0,0,round(sum(imoney)/sum(irea
lnumber),2)) iprice,sum(imoney) imoney,sum(orealnumber) orealnumber,DECODE(sum(orealnumber),0,0,roun
d(sum(omoney)/sum(orealnumber),2)) oprice,sum(omoney) omoney,' ' 結存數量 from( select to_char(x.ope
ratetime, 'YYYY-MM-DD') operatetime,x.billtype,
13:08:55 2 fun_getname('BILLTYPEALL',x.billtype) billtypename,
13:08:55 3 x.billid,x.remark,x.originbillid,
13:08:55 4 fun_getname('OPERATIONTYPE',x.otherinouttype) otherinouttypename,
13:08:55 5 x.shopcode,
13:08:55 6 f_getOrganiseName(x.shopcode) shopname,
13:08:55 7 x.storecode,
13:08:55 8 f_getStoreName(x.storecode) storename,
13:08:55 9 FUN_GETTOUNIT(x.billtype,x.billid) unitname,
13:08:55 10 x.goodscode,
13:08:55 11 X.goodsname,
13:08:55 12 x.ShortCode,x.brandcode,x.model,
13:08:55 13 x.irealnumber,
13:08:55 14 abs(DECODE(x.irealnumber,0,0,round(x.imoney/x.irealnumber,4))) iprice,
13:08:55 15 x.imoney,x.orealnumber,
13:08:55 16 abs(DECODE(x.orealnumber,0,0,round(x.omoney/x.orealnumber,4))) oprice,
13:08:55 17 x.omoney
13:08:55 18 from (
13:08:55 19 select a.operatetime,
13:08:55 20 a.billtype,
13:08:55 21 a.billid,fun_getbillremark(a.billid) remark,fun_getbilloriginbillid(a.b
illid) originbillid,
13:08:55 22 a.otherinouttype,
13:08:55 23 a.shopcode,
13:08:55 24 a.storecode,
13:08:55 25 a.goodscode,
13:08:55 26 c.shortcode,c.brandcode,c.model,
13:08:55 27 c.goodsname,
13:08:55 28 sum(DECODE(b.ruleid,1,a.realnumber,2,-a.realnumber,0)) irealnumber,
13:08:55 29 sum(DECODE(b.ruleid,1,
13:08:55 30 round(a.realnumber * a.price,2),
13:08:55 31 2,
13:08:55 32 round(-1 * a.realnumber *a.price,2),
13:08:55 33 0)) imoney,
13:08:55 34 sum(DECODE(b.ruleid,2,a.realnumber,1,-a.realnumber,0)) orealnumber,
13:08:55 35 sum(decode(b.ruleid,2,
13:08:55 36 round(a.realnumber * a.price,2),
13:08:55 37 1,
13:08:55 38 round(-1 * a.realnumber * a.price,2),
13:08:55 39 0)) omoney
13:08:55 40 from t_storeout a, t_billlist b,t_goods c
13:08:55 41 where a.billtype = b.billtype
13:08:55 42 and a.goodscode=c.goodscode and (a.shopcode = '0603')
13:08:55 43 group by a.operatetime,
13:08:55 44 a.billtype,
13:08:55 45 a.billid,
13:08:55 46 remark,
13:08:55 47 a.otherinouttype,
13:08:55 48 a.shopcode,
13:08:55 49 a.storecode,
13:08:55 50 a.goodscode,
13:08:55 51 c.shortcode,
13:08:55 52 c.goodsname,c.brandcode,c.model
13:08:55 53 ) x
13:08:55 54 where x.billtype in (select billtype from t_billlist where ruleid>0) and
13:08:55 55 x.operatetime >= to_date('2013-6-17','YYYY-MM-DD')
13:08:55 56 and x.operatetime < to_date('2013-6-18','YYYY-MM-DD')
13:08:55 57 ) a group by operatetime,billtypename,billid,shopcode,shopname,goodscode,goodsname or
der by operatetime,billtypename,billid,shopcode,shopname,goodscode,goodsname;
未選定行
已用時間: 00: 00: 00.04
?
?
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
