各位大虾:小弟今天在执行这个存储过程是出现这个错误该怎么解决(如题)
我在调试过程中发现当执行到: v_sql := v_sql || ' and ((ServiceChainIndex = 0) And (EndReason<>''7'') And (EndReason<>''515'')) OR ((ServiceChainIndex = 1) AND (EndReason <> ''11'') AND';
时v_sql的值为 (long value)在这之前还是有值的
全部执行完出项这个错误ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在 "SYS.DBMS_OUTPUT", line 148
ORA-06512: 在 line 2我将缓冲区大小设置为最大 32000 还是暴这个错误,请各位大虾帮忙解决下。
等待。create or replace procedure Log0001_TotalCallIn
(
P_StartTime varchar2,
p_StopTime varchar2,
v_cur out PackageSYS_TotalCllIn.cur_SYS_TotalCllIn,
p_TimeType int
)
is
v_sql varchar2(4000) :='';
begin
v_sql := 'Select A.StartTime as 时间分组,nvl(A.Counts,0) as 呼入总次数,nvl(B.Counts,0) as 成功总次数,nvl(C.Counts,0) as 失败总次数,';
v_sql := v_sql || ' nvl(D.Counts,0) as 转人工总次数,nvl(E.Counts,0) as 转人工成功次数,';
v_sql := v_sql || ' (case when nvl(D.Counts,0)=0 then 0 else nvl(E.Counts,0)*100/D.Counts end) as 转人工接通率,(nvl(D.Counts,0)-nvl(E.Counts,0)) as 转人工失败次数 from';
v_sql := v_sql || ' (select to_char(StartTime,''yyyy-mm-dd hh24'') as StartTime,count(*) as Counts from SRRecord where';
v_sql := v_sql || ' StartTime>='''||to_date(p_StartTime,'yyyy-mm-dd hh24:mi:ss')||''' and StartTime<='''||to_date(p_StopTime,'yyyy-mm-dd hh24:mi:ss')||'''';
v_sql := v_sql || ' and ServiceChainIndex = 0 group by to_char(StartTime,''yyyy-mm-dd hh24'') ) A left outer join ';
v_sql := v_sql || ' (select to_char(StartTime,''yyyy-mm-dd hh24'') as StartTime,count(*) as Counts from SRRecord where';
v_sql := v_sql || ' StartTime>='''||to_date(p_StartTime,'yyyy-mm-dd hh24:mi:ss')||''' and StartTime<='''||to_date(p_StopTime,'yyyy-mm-dd hh24:mi:ss')||'''';
v_sql := v_sql || ' and (ServiceChainIndex = 1) AND (EndReason = ''11'') AND (ISO <> ''T'')';
v_sql := v_sql || ' group by to_char(StartTime,''yyyy-mm-dd hh24'') ) B on A.StartTime=B.StartTime left outer join';
v_sql := v_sql || ' (select to_char(StartTime,''yyyy-mm-dd hh24'') as StartTime,count(*) as Counts from SRRecord where';
v_sql := v_sql || ' StartTime>='''||to_date(p_StartTime,'yyyy-mm-dd hh24:mi:ss')||''' and StartTime<='''||to_date(p_StopTime,'yyyy-mm-dd hh24:mi:ss')||'''';
v_sql := v_sql || ' and ((ServiceChainIndex = 0) And (EndReason<>''7'') And (EndReason<>''515'')) OR ((ServiceChainIndex = 1) AND (EndReason <> ''11'') AND';
v_sql := v_sql || ' (ISO <> ''T'') And (SPType is null)) group by to_char(StartTime,''yyyy-mm-dd hh24'') ) C on A.StartTime=C.StartTime left outer join';
v_sql := v_sql || ' (select to_char(StartTime,''yyyy-mm-dd hh24'') as StartTime,count(distinct SRID) as Counts from SRRecord where';
v_sql := v_sql || ' StartTime>='''||to_date(p_StartTime,'yyyy-mm-dd hh24:mi:ss')||''' and StartTime<='''||to_date(p_StopTime,'yyyy-mm-dd hh24:mi:ss')||'''';
v_sql := v_sql || ' and ServiceChainIndex = 0 and SRID in (Select distinct SRID from SRRecord where';
v_sql := v_sql || ' StartTime>='''||to_date(p_StartTime,'yyyy-mm-dd hh24:mi:ss')||''' and StartTime<='''||to_date(p_StopTime,'yyyy-mm-dd hh24:mi:ss')||'''';
v_sql := v_sql || ' and (EndReason = 21 or EndReason = 22 or EndReason = 23 or EndReason = 24 or EndReason = 25';
v_sql := v_sql || ' or EndReason = 26 or EndReason = 27 or EndReason = 28 or EndReason = 29) and ISO<>''T'')';
v_sql := v_sql || ' group by to_char(StartTime,''yyyy-mm-dd hh24'') ) D on A.StartTime=D.StartTime left outer join';
v_sql := v_sql || ' (select to_char(StartTime,''yyyy-mm-dd hh24'') as StartTime,count(distinct SRID) as Counts from SRRecord where';
v_sql := v_sql || ' StartTime>='''||to_date(p_StartTime,'yyyy-mm-dd hh24:mi:ss')||''' and StartTime<='''||to_date(p_StopTime,'yyyy-mm-dd hh24:mi:ss')||'''';
v_sql := v_sql || ' and ServiceChainIndex = 0 and SRID in (Select distinct SRID from SRRecord where';
v_sql := v_sql || ' StartTime>='''||to_date(p_StartTime,'yyyy-mm-dd hh24:mi:ss')||''' and StartTime<='''||to_date(p_StopTime,'yyyy-mm-dd hh24:mi:ss')||'''';
v_sql := v_sql || ' and (EndReason = 21 ) and ISO<>''T'') group by to_char(StartTime,''yyyy-mm-dd hh24'')';
v_sql := v_sql || ' ) E on A.StartTime=E.StartTime order by A.StartTime ';
DBMS_OUTPUT.put_line(v_sql);
open v_cur for v_sql;
end Log0001_TotalCallIn;
我在调试过程中发现当执行到: v_sql := v_sql || ' and ((ServiceChainIndex = 0) And (EndReason<>''7'') And (EndReason<>''515'')) OR ((ServiceChainIndex = 1) AND (EndReason <> ''11'') AND';
时v_sql的值为 (long value)在这之前还是有值的
全部执行完出项这个错误ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在 "SYS.DBMS_OUTPUT", line 148
ORA-06512: 在 line 2我将缓冲区大小设置为最大 32000 还是暴这个错误,请各位大虾帮忙解决下。
等待。create or replace procedure Log0001_TotalCallIn
(
P_StartTime varchar2,
p_StopTime varchar2,
v_cur out PackageSYS_TotalCllIn.cur_SYS_TotalCllIn,
p_TimeType int
)
is
v_sql varchar2(4000) :='';
begin
v_sql := 'Select A.StartTime as 时间分组,nvl(A.Counts,0) as 呼入总次数,nvl(B.Counts,0) as 成功总次数,nvl(C.Counts,0) as 失败总次数,';
v_sql := v_sql || ' nvl(D.Counts,0) as 转人工总次数,nvl(E.Counts,0) as 转人工成功次数,';
v_sql := v_sql || ' (case when nvl(D.Counts,0)=0 then 0 else nvl(E.Counts,0)*100/D.Counts end) as 转人工接通率,(nvl(D.Counts,0)-nvl(E.Counts,0)) as 转人工失败次数 from';
v_sql := v_sql || ' (select to_char(StartTime,''yyyy-mm-dd hh24'') as StartTime,count(*) as Counts from SRRecord where';
v_sql := v_sql || ' StartTime>='''||to_date(p_StartTime,'yyyy-mm-dd hh24:mi:ss')||''' and StartTime<='''||to_date(p_StopTime,'yyyy-mm-dd hh24:mi:ss')||'''';
v_sql := v_sql || ' and ServiceChainIndex = 0 group by to_char(StartTime,''yyyy-mm-dd hh24'') ) A left outer join ';
v_sql := v_sql || ' (select to_char(StartTime,''yyyy-mm-dd hh24'') as StartTime,count(*) as Counts from SRRecord where';
v_sql := v_sql || ' StartTime>='''||to_date(p_StartTime,'yyyy-mm-dd hh24:mi:ss')||''' and StartTime<='''||to_date(p_StopTime,'yyyy-mm-dd hh24:mi:ss')||'''';
v_sql := v_sql || ' and (ServiceChainIndex = 1) AND (EndReason = ''11'') AND (ISO <> ''T'')';
v_sql := v_sql || ' group by to_char(StartTime,''yyyy-mm-dd hh24'') ) B on A.StartTime=B.StartTime left outer join';
v_sql := v_sql || ' (select to_char(StartTime,''yyyy-mm-dd hh24'') as StartTime,count(*) as Counts from SRRecord where';
v_sql := v_sql || ' StartTime>='''||to_date(p_StartTime,'yyyy-mm-dd hh24:mi:ss')||''' and StartTime<='''||to_date(p_StopTime,'yyyy-mm-dd hh24:mi:ss')||'''';
v_sql := v_sql || ' and ((ServiceChainIndex = 0) And (EndReason<>''7'') And (EndReason<>''515'')) OR ((ServiceChainIndex = 1) AND (EndReason <> ''11'') AND';
v_sql := v_sql || ' (ISO <> ''T'') And (SPType is null)) group by to_char(StartTime,''yyyy-mm-dd hh24'') ) C on A.StartTime=C.StartTime left outer join';
v_sql := v_sql || ' (select to_char(StartTime,''yyyy-mm-dd hh24'') as StartTime,count(distinct SRID) as Counts from SRRecord where';
v_sql := v_sql || ' StartTime>='''||to_date(p_StartTime,'yyyy-mm-dd hh24:mi:ss')||''' and StartTime<='''||to_date(p_StopTime,'yyyy-mm-dd hh24:mi:ss')||'''';
v_sql := v_sql || ' and ServiceChainIndex = 0 and SRID in (Select distinct SRID from SRRecord where';
v_sql := v_sql || ' StartTime>='''||to_date(p_StartTime,'yyyy-mm-dd hh24:mi:ss')||''' and StartTime<='''||to_date(p_StopTime,'yyyy-mm-dd hh24:mi:ss')||'''';
v_sql := v_sql || ' and (EndReason = 21 or EndReason = 22 or EndReason = 23 or EndReason = 24 or EndReason = 25';
v_sql := v_sql || ' or EndReason = 26 or EndReason = 27 or EndReason = 28 or EndReason = 29) and ISO<>''T'')';
v_sql := v_sql || ' group by to_char(StartTime,''yyyy-mm-dd hh24'') ) D on A.StartTime=D.StartTime left outer join';
v_sql := v_sql || ' (select to_char(StartTime,''yyyy-mm-dd hh24'') as StartTime,count(distinct SRID) as Counts from SRRecord where';
v_sql := v_sql || ' StartTime>='''||to_date(p_StartTime,'yyyy-mm-dd hh24:mi:ss')||''' and StartTime<='''||to_date(p_StopTime,'yyyy-mm-dd hh24:mi:ss')||'''';
v_sql := v_sql || ' and ServiceChainIndex = 0 and SRID in (Select distinct SRID from SRRecord where';
v_sql := v_sql || ' StartTime>='''||to_date(p_StartTime,'yyyy-mm-dd hh24:mi:ss')||''' and StartTime<='''||to_date(p_StopTime,'yyyy-mm-dd hh24:mi:ss')||'''';
v_sql := v_sql || ' and (EndReason = 21 ) and ISO<>''T'') group by to_char(StartTime,''yyyy-mm-dd hh24'')';
v_sql := v_sql || ' ) E on A.StartTime=E.StartTime order by A.StartTime ';
DBMS_OUTPUT.put_line(v_sql);
open v_cur for v_sql;
end Log0001_TotalCallIn;
解决方案 »
- oracle 如何调试 带输入参数,输出结果集的存储过程
- 我安装datastage 8.5 + oracle11g 但是老包字符不能是ZHS16GBK
- execute immediate 执行sql语句中存在特殊字符的问it
- 当文本字节超过4096只能用BOLB类型吗?文本存入BOLB类型后能直接看到明码吗?
- oracle的安装问题,请大家帮忙
- ORACLE 9I 存储过程中的string类型变量最大长度为多少?有没有限制
- 數据文件太大不能啟動的問題
- sqlload的问题:如何让oracle正确导入带有千分位的数字呢?
- oracle安装问题??
- orace分组
- 利用STREAMS流技术如何监控系统表
- 如何永久修改report里的XML PROLOG VALUE??
我一般的做法是将拼接的结果insert到一个表,你可以试试
create table rs (sqlstr varchar2(4000));。。
v_sql := v_sql || ' ) E on A.StartTime=E.StartTime order by A.StartTime ';
--DBMS_OUTPUT.put_line(v_sql);
insert into rs values(v_sql);
commit;
open v_cur for v_sql;
......
......
这样试试看看有用不?
我要是像您这么牛我早就不接触开发了。。
上线 还有10来天。
循环v_sql,每行小于255字节就换行打印。。