os version: linux
oracle version: oracle 9204
-----------------------------------------------------------------------------------------
When I execute a sql in procedure ,It disconnect. It’s amazing.see that:
SQL> conn ngcc_best/ngcc_Best@zxin_116
已连接。
SQL> var v_i number;
SQL> exec pro_auto_IsInBefore_te1('18999912345',:v_i);
BEGIN pro_auto_IsInBefore_te1('18999912345',:v_i); END;
*
第 1 行出现错误:
ORA-03113: 通信通道的文件结束
Actually, the result is 1 .the detail content of procedure “pro_auto_IsInBefore_te1” is that:
create or replace procedure pro_auto_IsInBefore_te1
(
p_strcallingno IN varchar2, --主叫号码 号段部分3-15位才能匹配有效
p_IsIn OUT integer --标志
)
as
v_count integer;
Begin
select count(1) into v_count from
(
select strdhhm from t_auto_customerjudge
where ltrim(p_strcallingno, '0') = ltrim(strdhhm, '0')
and type = '1'
and (sysdate - dtjudgedate) * 1440 <= inthmdlimit
and inthmdflag = 1
union all
SELECT strdhhm from t_auto_customerjudge a where
(
substr(ltrim(p_strcallingno, '0'), 1, 3) = ltrim(strdhhm, '0')
or
substr(ltrim(p_strcallingno, '0'), 1, 4) = ltrim(strdhhm, '0')
)
and type = '2'
and (sysdate - dtjudgedate) * 1440 <= inthmdlimit
and inthmdflag = 1
);
if v_count = 0 then
p_IsIn:=0;
else
p_IsIn:=1;
end if;
End ;
/
Table t_auto_customerjudge has a function index.The definition of the index is that:
create index IND_AUTO_CUSTOMERJUDGE_LTRIM on T_AUTO_CUSTOMERJUDGE (LTRIM(STRDHHM,'0')) tablespace TSP_NGCC_BEST;
Table has been analyzed.if you execute the statement independed of the procedure.it do well.
------------------------------------------------------------------------------------------
SQL> select count(1) from
2 (
3 select strdhhm from t_auto_customerjudge
4 where ltrim('18999912345', '0') = ltrim(strdhhm, '0')
5 and type = '1'
6 and (sysdate - dtjudgedate) * 1440 <= inthmdlimit
7 and inthmdflag = 1
8 union all
9 SELECT strdhhm from t_auto_customerjudge a where
10 (
11 substr(ltrim('18999912345', '0'), 1, 3) = ltrim(strdhhm,
'0')
12 or
13 substr(ltrim('18999912345', '0'), 1, 4) = ltrim(strdhhm,
'0')
14 )
15 and type = '2'
16 and (sysdate - dtjudgedate) * 1440 <= inthmdlimit
17 and inthmdflag = 1
18 );
COUNT(1)
----------
0
So,why this happen?
When I check the alert.log,I found that message:
Thu Sep 9 17:56:42 2010
Errors in file /home/oracle/zxindbf/admin/zxin/udump/zxin_ora_2508.trc:
ORA-07445: exception encountered: core dump [evaopn2()+2628] [SIGSEGV] [Address not mapped to object] [0x0] [] []
Thu Sep 9 21:55:12 2010
Errors in file /home/oracle/zxindbf/admin/zxin/udump/zxin_ora_5393.trc:
ORA-07445: exception encountered: core dump [evaopn2()+2628] [SIGSEGV] [Address not mapped to object] [0x0] [] []
Thu Sep 9 21:56:59 2010
Errors in file /home/oracle/zxindbf/admin/zxin/udump/zxin_ora_5437.trc:
ORA-07445: exception encountered: core dump [evaopn2()+2628] [SIGSEGV] [Address not mapped to object] [0x0] [] []
Thu Sep 9 22:01:22 2010
Errors in file /home/oracle/zxindbf/admin/zxin/udump/zxin_ora_5557.trc:
ORA-07445: exception encountered: core dump [evaopn2()+2628] [SIGSEGV] [Address not mapped to object] [0x0] [] []
Thu Sep 9 22:02:36 2010
Errors in file /home/oracle/zxindbf/admin/zxin/udump/zxin_ora_5645.trc:
ORA-07445: exception encountered: core dump [evaopn2()+2628] [SIGSEGV] [Address not mapped to object] [0x0] [] []
It seems that when I execute the procedure “pro_auto_IsInBefore_te1”,the error 07445 will generate.
Then I check the trace file /home/oracle/zxindbf/admin/zxin/udump/zxin_ora_5557.trc:
the important information is that:
-------------------------------------------------------------------------------------------------------
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [evaopn2()+2628] [SIGSEGV] [Address not mapped to object] [0x0] [] []
Current SQL statement for this session:
SELECT count(1) from
(
select strdhhm from t_auto_customerjudge
where ltrim(:b1, '0') = ltrim(strdhhm, '0')
and type = '1'
and (sysdate - dtjudgedate) * 1440 <= inthmdlimit
and inthmdflag = 1
union all
select strdhhm from t_auto_customerjudge where
(
substr(ltrim(:b1, '0'), 1, 3) = ltrim(strdhhm, '0')
or
substr(ltrim(:b1, '0'), 1, 4) = ltrim(strdhhm, '0')
)
and type = '2'
and (sysdate - dtjudgedate) * 1440 <= inthmdlimit
and inthmdflag = 1
)
----- PL/SQL Call Stack -----
object line object
handle number name
0xa3d1627c 9 procedure NGCC_BEST.PRO_AUTO_ISINBEFORE_TE
0xa5b6cd5c 1 anonymous block
So,this sql in procedure makes the error 07455.how It make the error?
After some test,I found that when the function index is in use in the subquery statement:
select strdhhm from t_auto_customerjudge where
(
substr(ltrim(:b1, '0'), 1, 3) = ltrim(strdhhm, '0')
or
substr(ltrim(:b1, '0'), 1, 4) = ltrim(strdhhm, '0')
)
and type = '2'
and (sysdate - dtjudgedate) * 1440 <= inthmdlimit
and inthmdflag = 1
)
It will makes the error.and if the subquery goes “table access full”,the error does not hppend.
Metalink says this may be a bug.But, when we consider a error is a bug,you must make sure that this error happen very common,and creating SR is a good Idea.
So,I change the sql in the procedure.like that :
-------------------------------------------------------------------------
create or replace procedure pro_auto_IsInBefore_te1
(
p_strcallingno IN varchar2, --主叫号码 号段部分3-15位才能匹配有效
p_IsIn OUT integer --标志
)
as
v_count integer;
Begin
select count(1) into v_count from
(
select strdhhm from t_auto_customerjudge
where ltrim(p_strcallingno, '0') = ltrim(strdhhm, '0')
and type = '1'
and (sysdate - dtjudgedate) * 1440 <= inthmdlimit
and inthmdflag = 1
union all
SELECT strdhhm from t_auto_customerjudge a where
(
ltrim(strdhhm, '0') IN (substr(ltrim(p_strcallingno, '0'), 1, 3),
substr(ltrim(p_strcallingno, '0'), 1, 4))
)
and type = '2'
and (sysdate - dtjudgedate) * 1440 <= inthmdlimit
and inthmdflag = 1
);
if v_count = 0 then
p_IsIn:=0;
else
p_IsIn:=1;
end if;
End ;
Now ,It goes well.