occi 调用存储过程抓不到异常

occi 捕获一场的函数貌似只有 SQLException
请大神指点是不是存储过程写的有问题

ORA-01852: seconds must be between 0 and 59
你的存储过程应该涉及到时间处理吧，秒数的值不符合规范
你是怎么捕获异常的?

这是存储过程代码：
由于代码太长，所以只能分批发送CREATE OR REPLACE PROCEDURE Ps_Uc_Sms_Status_Query
----短信明细查询
(
    p_Mobile2         In Varchar2, --联系方式
    p_Deptno2         In Varchar2, --机构代码
    p_Cust_Id2        In Varchar2, --客户号
    p_Cust_Name2      In Varchar2, --客户姓名
    p_Employeer_Id2   In Varchar2, --工号
    p_Employeer_Name2 In Varchar2, --员工姓名
    p_Begin_Date     In Varchar2, --起始日期
    p_End_Date       In Varchar2, --终止日期
    p_Mark           In Varchar2, --发送状态
    p_Pagenum        In Number, --分页页数
    p_Pagesum        In Number, --每页总数
    p_Task_Id        In Varchar2, --短信批次号
    vsource in varchar2, --系统来源
    vsearcher        in varchar2, --查询人
    vsmstype         in varchar2,--短信类别
    vxxlb            in varchar2, --信息类别
    vsysid in varchar2, --系统id
    vip in varchar2, --系统ip
    vmac in varchar2, --系统mac

    rCursor         Out Types.Ref_Cursor --返回游标
) As
    v_Sql          Varchar2(8000); ----参数设置
    v_Sql_Current  Varchar2(4000); ----3天内归档表
    v_Sql_Hist     Varchar2(4000); ----历史表
    v_Sql_Begin    Varchar2(4000); ----SQL头部文件
    v_Sql_End      Varchar2(4000); ----SQL尾部文件    vorgid pls_integer;
    vsqlauthcnd varchar2(4000) :='';
    vsyscode varchar2(20);
    vcount pls_integer;

    p_Mobile          Varchar2(100); --联系方式
    p_Deptno          Varchar2(4000); --机构代码
    p_Cust_Id         Varchar2(100); --客户号
    p_Cust_Name       Varchar2(1000); --客户姓名
    p_Employeer_Id    Varchar2(100); --工号
    p_Employeer_Name  Varchar2(1000); --员工姓名
Begin
    p_Mobile      := f_filteString(p_Mobile2);
    p_Deptno      := f_filteString(p_Deptno2);
    p_Cust_Id     := f_filteString(p_Cust_Id2);
    p_Cust_Name   := f_filteString(p_Cust_Name2);
    p_Employeer_Id := f_filteString(p_Employeer_Id2);
    p_Employeer_Name := f_filteString(p_Employeer_Name2);     Ps_debug('Ps_Uc_Sms_Status_Query',' 查询条件：[联系方式]' || p_Mobile || '[机构代码]' || p_Deptno ||
                                        '[客户号]' || p_Cust_Id || '[客户姓名]' || p_Cust_Name || '[工号]' || p_Employeer_Id || '[员工姓名]' ||
                                        p_Employeer_Name || '[起始日期]' || p_Begin_Date || '[终止日期]' || p_End_Date || '[发送状态]' ||
                                        p_Mark || '[分页页数]' || p_Pagenum || '[每页总数]' || p_Pagesum || '[短信批次号]' || p_Task_Id||
                                        '[系统id]'||vsysid||'[查询人]'||vsearcher||'[短信类别]'||vsmstype||'[信息类别]'||vxxlb||'[系统来源]'||vsource||'[ip]'||vip||'[mac]'||vmac );
    --1.拼接权限条件,如果是本系统来的，根据查询人权限判断，如果不是，根据系统直接判断
    select count(1) into vcount
    from UC_APP_EXTEND_INFO
    where ip = vip and mac = vmac and app_id= vsysid;
    if vcount <1 then
        open rCursor for 'select * from dual where 1=0';
        return;
    end if;
    select count(1) into vcount from uc_app where id = vsysid;
    if vcount <>1 then
        open rCursor for 'select * from dual where 1=0';
        return;
    end if;
    select app_code into vsyscode
    from uc_app where id = vsysid;
    if vsyscode <>'ucmg' then
        vsqlauthcnd := vsqlauthcnd ||' and t1.SOURCE ='''||vsysid||''' ';
    else
        select count(*) into vcount
        from uc_user_role
        where emp_id = vsearcher and role_id = (select id from uc_role where role_code = types.unite_zongbu);
        if vcount >0 then --总部管理员可以查所有
            vsqlauthcnd := '';
        else
            select count(*) into vcount
            from uc_user_role
            where emp_id = vsearcher and role_id = (select id from uc_role where role_code = types.unite_gs);
            if vcount >0 then --公司管理员可以查该公司

                 select parent_id into vorgid
                 from CSSWEB_DICTIONARY
                 where key_code in (
                 select PROVINCE_ID
                 from webcall_customservice where employee_id = vsearcher);

                 vsqlauthcnd := ' and exists (select 1 from webcall_customservice where employee_id = t1.empid and
                 province_id in ( select key_code from CSSWEB_DICTIONARY where parent_id ='''||vorgid||'''))';

            else
                select count(*) into vcount
                from uc_user_role
                where emp_id = vsearcher and role_id = (select id from uc_role where role_code = types.unite_fzjg);
                if vcount >0 then --分支机构管理员可以查该分支机构
                    select PROVINCE_ID into vorgid
                    from webcall_customservice where employee_id = vsearcher;
                    vsqlauthcnd := ' and exists (select 1 from webcall_customservice where employee_id = t1.empid and province_id ='''||vorgid||''')';
                else
                    select count(*) into vcount
                    from uc_user_role
                    where emp_id = vsearcher and role_id = (select id from uc_role where role_code = types.unite_yyb);
                    if vcount >0 then --营业部管理员可查该营业部
                        select INNER_BRANCHID into vorgid
                        from webcall_customservice where employee_id = vsearcher;
                        vsqlauthcnd := ' and exists (select 1 from webcall_customservice where employee_id = t1.empid and INNER_BRANCHID ='''||vorgid||''')';
                    else --其他只能查自己
                        vsqlauthcnd := ' and empid = '''||vsearcher||'''';
                    end if;
                end if;
            end if;
        end if;
    end if;    --2 拼接查询条件
    if vsource is not null then
        vsqlauthcnd := vsqlauthcnd || ' and t1.source = '''||vsource||'''';
    end if;
    v_Sql  :=  vsqlauthcnd ;    If p_Mobile Is Not Null
    Then
        v_Sql := v_Sql || ' and t1.mobile  =' || '''' || p_Mobile || '''';
    End If;
    If p_Deptno Is Not Null
    Then
        v_Sql := v_Sql || f_getDeptCondition(p_Deptno);
    End If;
    If p_Cust_Id Is Not Null
    Then
        v_Sql := v_Sql || ' and  t1.CUST_ID  =' || '''' || p_Cust_Id || '''';
    End If;
    If p_Task_Id Is Not Null
    Then
        v_Sql := v_Sql || ' and  t1.task_id  =' || '''' || p_Task_Id || '''';
    End If;
    If p_Cust_Name Is Not Null
    Then
        v_Sql := v_Sql || ' and t1.cust_name = ' || '''' || p_Cust_Name || '''';
    End If;
    If p_Employeer_Name Is Not Null
    Then
        v_Sql := v_Sql || ' and t1.user_name = ' || '''' || p_Employeer_Name || '''';
    End If;
    If p_Employeer_Id Is Not Null
    Then
        v_Sql := v_Sql || ' and t1.EMPID = ' || '''' || p_Employeer_Id || '''';
    End If;
    If p_Begin_Date Is Not Null
    Then
        v_Sql := v_Sql || ' and t1.create_time >= To_Date(' || '''' || p_Begin_Date || '''' || ',' ||
                         '''yyyy-mm-dd hh24:mi:ss''' || ' ) ';
    End If;    If p_End_Date Is Not Null
    Then
        v_Sql := v_Sql || ' and  t1.create_time <= to_date( ' || '''' || p_End_Date || '''' || ',' ||
                         '''yyyy-mm-dd hh24:mi:ss''' || ') ';
    End If;    if vsmstype is not null  then
        v_sql := v_sql ||' and t1.SMS_TYPE = '''||vsmstype || '''';
    end if;    if vxxlb is not null then
        v_sql := v_sql || ' and t1.useful='''||vxxlb||''' ';
    end if;    If p_Mark = '0' ----[ '0', '待发送' ]
    Then
        v_Sql := v_Sql || ' and t1. =0 order by create_time desc )  ';
    Elsif p_Mark = '3' ----[ '3', '未回' ]
    Then
        v_Sql := v_Sql || ' and t1. = 1  order by create_time desc ) where
                                            (report is null or response is null )   ';
    Elsif p_Mark = '1' ----[ '1', '成功' ]
    Then
        v_Sql := v_Sql || ' and t1. = 1 order by create_time desc ) where  response=''0'' and
                                     report in (''0'',''15'') ';
    Elsif p_Mark = '2' ----[ '2', '失败' ]
    Then
        v_Sql := v_Sql || ' order by create_time desc ) where ( not ( =0
                                                or (=1 and (report  in (''0'',''15'') or (response is null or report is null)))
                                                    ) )  ';
    Else
        v_Sql := v_Sql || ' order by create_time desc )  ';
    End If;

----发送状态：[ '0', '未发送' ], [ '1', '未回' ], [ '3', '成功' ], [ '4', '失败' ]
    ----SQL头部拼写
    v_Sql_Begin := 'select  id,Cust_Name,mobile,Content,to_char(create_time,''yyyy-mm-dd hh24:mi:ss'') as create_time,
                        case when =0 then ''未发送''
                             when =1 and (report = ''0'' or report = ''15'') then ''成功''
                           when =1 and (response is null or report is null) then ''未回''
                             else ''发送失败''
                    end as status,
                    User_Name,Empid,Deptno,
                    (select key_name from cssweb_dictionary cd where cd.key_code = cota.deptno) as deptName,
                    case when =1 and (report = ''0'' or report = ''15'') then ''成功''
                             when  = 1 and response is null then ''平台无回执''
                             when  = 1 and response = ''0'' and report is null then ''运营商无回执''
                             when  = 1 and response = ''0'' and report is not null and report = ''2'' then ''通道不存在''
                             when  = 1 and response = ''0'' and report is not null and report not in (''0'',''15'',''2'') then ''运营商失败''
                             when  = 2 then ''号码有误''
                             when  = 6 then ''黑名单''
                             when  = 0 then ''待发送''
                             when  = 7 then ''重复发送''
                             when  =1 and (response = ''10'' or response = ''-10'') then ''平台黑名单''
                             when  =1 and (response = ''14'' or response = ''-14'') then ''平台重复发送''
                             else ''其他错误''
                 end as des,
                 (select t.key_name from uc_dictionary t where t.key_type=''cust_type'' and t.key_code=cota.cust_type and rownum=1) as CUST_TYPE,
                 (select t.app_name from uc_app t where t.id=cota.Source and rownum=1) as SOURCE,
                 sms_Type,subject,mms_Resource,(select t.key_name from uc_dictionary t where t.key_type=''policy'' and t.key_code=cota.policy and rownum=1) as policy,
                 (select t.xxlb_name from UC_XXLB t where t.xxlb_code=cota.useful and rownum=1) as useful,
                 task_id,
                 to_char(send_time,''yyyy-mm-dd hh24:mi:ss'') as send_time
                    from (
                select cot.*,rownum as r_id from (                Select  id,Cust_Id,Cust_Name,Content,Useful,mobile,
                create_time,Mark,User_Name,Empid,
                Deptno,Cust_Type,Source,Task_Id,
                Report,
                Response,sms_Type,subject,mms_Resource,policy,send_time
                 from (';
    ----小备份作为其次
    v_Sql_Current := '
         select Id,
             Cust_Id,
             Cust_Name,
             Content,
             Useful,
             Mobile,
             Create_Time,
             Mark,
             User_Name,
             Empid,
             Deptno,
             Cust_Type,
             Source,
             Task_Id,
             sms_Type,subject,mms_Resource,policy,
             Report,
             Response,send_time from (
            Select  id, Cust_Id,Cust_Name,Content,Useful,mobile,
                create_time,Mark,User_Name,Empid,
                Deptno,Cust_Type,Source,Task_Id,
                sms_Type,subject,mms_Resource,policy,send_time,
                (Select Max(b.Report)
                From   History_Sms_Response r
                Left   Join History_Sms_Report b
                On     r.Seq = b.Seq
                Where  r.seq = T1.seq
                ) As Report,
             (Select Max(r.Response)
                From   History_Sms_Response r
                Left   Join History_Sms_Report b
                On     r.Seq = b.Seq
                Where  r.seq = T1.seq
                ) As Response
          From history_sms_send_current t1
                Where 1=1 ';    v_Sql_Current := v_Sql_Current || v_Sql;

            ----历史数据作为基础
    v_Sql_Hist := ' union all  select Id,
             Cust_Id,
             Cust_Name,
             Content,
             Useful,
             Mobile,
             Create_Time,
             Mark,
             User_Name,
             Empid,
             Deptno,
             Cust_Type,
             Source,
             Task_Id,
             sms_Type,subject,mms_Resource,policy,
             Report,
             Response,send_time from (
         Select  id,Cust_Id,Cust_Name,Content,Useful,mobile,
                create_time,Mark,User_Name,Empid,
                Deptno,Cust_Type,Source,Task_Id,Report,Response,
                sms_Type,subject,mms_Resource,policy,send_time
                From   History_Sms_Send_Bak t1
          Where 1=1';    v_Sql_Hist := v_Sql_Hist || v_Sql;
    ----SQL尾部拼写
    v_Sql_End := ' ) ) cot
                     where rownum<='||p_Pagenum||'*'||p_Pagesum||' ) cota
                        where cota.r_id > (' || p_Pagenum || '- 1) * ' || p_Pagesum;    /*Dbms_Output.Put_Line(v_Sql_Begin);
     Dbms_Output.Put_Line(v_Sql_Current);
    Dbms_Output.Put_Line(v_Sql_Hist);
    Dbms_Output.Put_Line(v_Sql_end);*/
    Open rCursor For v_Sql_Begin || v_Sql_Current || v_Sql_Hist|| v_Sql_End;
    --  Execute Immediate v_All_Sql;
Exception
    When Others Then
        Dbms_Output.Put_Line(Sqlerrm);
        Ps_Uc_Etl_Logs(Systimestamp, Sql%Rowcount, 'PS_UC_SMS_STATUS_QUERY',
                                     '过程报错: ' || Sqlcode || ' : ' || Sqlerrm || Chr(13) || '[错误代码]' || Sqlcode || Chr(13) ||
                                        ' [错误信息]' || Sqlerrm || ' 查询条件：[联系方式]' || p_Mobile || '[机构代码]' || p_Deptno ||
                                        '[客户号]' || p_Cust_Id || '[客户姓名]' || p_Cust_Name || '[工号]' || p_Employeer_Id || '[员工姓名]' ||
                                        p_Employeer_Name || '[起始日期]' || p_Begin_Date || '[终止日期]' || p_End_Date || '[发送状态]' ||
                                        p_Mark || '[分页页数]' || p_Pagenum || '[每页总数]' || p_Pagesum || '[短信批次号]' || p_Task_Id, '0',
                                     To_Char(Sysdate, 'YYYYMMDD'), To_Char(Sysdate, 'YYYY-MM-DD'));
      raise;
End;

调试易

occi 调用存储过程抓不到异常

解决方案 »

occi 调用存储过程 抓不到异常

解决方案 »

occi 调用存储过程抓不到异常