oracle存储过程查询报错

Question

oracle存储过程执行异常，异常信息“ORA-06502:PL/SQL:numeric or value error:character string buffer too small”，请问这是什么原因呢？

Answer 1

varchar2作为变量类型，可以存储32767个字符。
如果这个异常是由于变量长度不够造成的，那么使用什么类型定义这种变量呢？

Answer 2

Returns a single line of buffered information, excluding a final newline character. You should declare the actual for this parameter as VARCHAR2 (32767) to avoid the risk of "ORA-06502: PL/SQL: numeric or value error: character string buffer too small".

Answer 3

The type of my variable is VARCHAR2,and defined the length 32767.
I am at my wits end.

Answer 4

http://www.itpub.net/thread-860830-1-1.html
看看这个对你有帮助吗

Answer 5

An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 88 to a variable declared NUMBER(2). 

Answer 6

动态拼凑的SQL语句，赋值给变量v_sql(varchar2(32767))
这段动态SQL的字符长度是28681鉴于MSDN回复内容有字节长度限制，分条粘出。
[code=SQ]SELECT SUM(-I_ZongYongL) AS SL, case when k.s_feileimc='工业用水' then '工业调整' when k.s_feileimc='经营服务用水' then '经营调整' 
  when k.s_feileimc='居民生活用水' then '居民调整' when k.s_feileimc='特种行业用水' then '特种调整' when k.s_feileimc='趸售水' then '趸售调整' 
  when k.s_feileimc='行政事业用水' then '行政调整' when k.s_feileimc='其他' then '其它调整' else '' end  AS FLAG 
      FROM IBCS_TL.ZW_TiaoZheng A INNER JOIN IBCS_TL.ZW_TiaoZheng_ZB B ON A.I_TiaoZhengBH=B.I_TiaoZhengBH 
      INNER JOIN (select b.ROWINDEX as ID,a.I_JH,a.I_TIAOJIAH as I_TJH,b.S_FeiLeiMC from 
    (SELECT * FROM ibcs_TL.JG_YongShuiFL where I_Ancestor<>0) a
    left join
    (SELECT ROW_NUMBER() over (ORDER BY S_FeiLeiMC) AS ROWINDEX,S_FeiLeiMC, ID FROM IBCS_TL.JG_YongShuiFL WHERE I_Ancestor=0) b
    on a.I_Ancestor=b.ID) k ON B.I_JH=k.I_JH AND B.I_TiaoJaH=k.I_TJH
      WHERE  k.s_feileimc in ('工业用水','经营服务用水','居民生活用水','特种行业用水'
  ,'趸售水','行政事业用水','其他')  and DL_CaoZuoRQ>=20100401 AND DL_CaoZuoRQ<=20100430 
      AND A.I_Y*100+A.I_M<201004 AND A.I_JLZT=0 
      AND B.I_JLZT=0 AND B.I_LeiXing=1 AND B.I_FeiYongDLBH=580 group by k.s_feileimc union all SELECT SUM(-I_ZongYongL) as SL, case when k.s_feileimc='工业用水' then '工业调整' when k.s_feileimc='经营服务用水' then '经营调整' 
  when k.s_feileimc='居民生活用水' then '居民调整' when k.s_feileimc='特种行业用水' then '特种调整' when k.s_feileimc='趸售水' then '趸售调整' 
  when k.s_feileimc='行政事业用水' then '行政调整' when k.s_feileimc='其他' then '其它调整' else '' end  AS FLAG 
      FROM IBCS_TL.ZW_ChongZheng A INNER JOIN IBCS_TL.ZW_ChongZheng_ZB B ON A.I_ChongZhengBH=B.I_ChongZhengBH
      INNER JOIN (select b.ROWINDEX as ID,a.I_JH,a.I_TIAOJIAH as I_TJH,b.S_FeiLeiMC from 
    (SELECT * FROM ibcs_TL.JG_YongShuiFL where I_Ancestor<>0) a
    left join[/code]

Answer 7

[code=SQ](SELECT ROW_NUMBER() over (ORDER BY S_FeiLeiMC) AS ROWINDEX,S_FeiLeiMC, ID FROM IBCS_TL.JG_YongShuiFL WHERE I_Ancestor=0) b
    on a.I_Ancestor=b.ID) k ON B.I_JH=k.I_JH AND B.I_TiaoJaH=k.I_TJH
      WHERE  k.s_feileimc in ('工业用水','经营服务用水','居民生活用水','特种行业用水'
  ,'趸售水','行政事业用水','其他')  AND A.I_JLZT=0 and TO_CHAR(D_ChongZhengRQ,'yyyyMMdd') BETWEEN
      '20100401' AND '20100430' AND A.I_LeiXing=4 
      AND B.I_JLZT=0 AND B.I_LeiXing=1 AND B.I_FeiYongDLBH=580 
      AND A.I_Y*100+A.I_M<201004 group by k.s_feileimc union all SELECT SUM(-I_ZongYongL) as SL, case when k.s_feileimc='工业用水' then '工业减免' when k.s_feileimc='经营服务用水' then '经营减免' 
  when k.s_feileimc='居民生活用水' then '居民减免' when k.s_feileimc='特种行业用水' then '特种减免' when k.s_feileimc='趸售水' then '趸售减免' 
  when k.s_feileimc='行政事业用水' then '行政减免' when k.s_feileimc='其他' then '其它减免' else '' end  AS FLAG 
      FROM IBCS_TL.ZW_JianMian A INNER JOIN IBCS_TL.ZW_JianMian_ZB B ON A.I_JianMianBH=B.I_JianMianBH
      INNER JOIN (select b.ROWINDEX as ID,a.I_JH,a.I_TIAOJIAH as I_TJH,b.S_FeiLeiMC from 
    (SELECT * FROM ibcs_TL.JG_YongShuiFL where I_Ancestor<>0) a
    left join
    (SELECT ROW_NUMBER() over (ORDER BY S_FeiLeiMC) AS ROWINDEX,S_FeiLeiMC, ID FROM IBCS_TL.JG_YongShuiFL WHERE I_Ancestor=0) b
    on a.I_Ancestor=b.ID) k ON B.I_JH=k.I_JH AND B.I_TiaoJaH=k.I_TJH 
      WHERE  k.s_feileimc in ('工业用水','经营服务用水','居民生活用水','特种行业用水'
  ,'趸售水','行政事业用水','其他')  and DL_CaoZuoRQ>=20100401 AND DL_CaoZuoRQ<=20100430 
      AND B.I_JLZT=0 AND B.I_LeiXing=1 AND B.I_FeiYongDLBH=580 
      and A.I_JLZT=0 AND A.I_Y*100+A.I_M<201004 group by k.s_feileimc union all SELECT SUM(N_YongShuiL) AS SL, case when k.s_feileimc='工业用水' then '工业特抄' when k.s_feileimc='经营服务用水' then '经营特抄' 
  when k.s_feileimc='居民生活用水' then '居民特抄' when k.s_feileimc='特种行业用水' then '特种特抄' when k.s_feileimc='趸售水' then '趸售特抄' 
  when k.s_feileimc='行政事业用水' then '行政特抄' when k.s_feileimc='其他' then '其它特抄' else '' end  AS FLAG 
      FROM IBCS_TL.ZW_ZanShouKuan A INNER JOIN (select b.ROWINDEX as ID,a.I_JH,a.I_TIAOJIAH as I_TJH,b.S_FeiLeiMC from 
    (SELECT * FROM ibcs_TL.JG_YongShuiFL where I_Ancestor<>0) a[/code]

Answer 8

[code=SQ]
left join
    (SELECT ROW_NUMBER() over (ORDER BY S_FeiLeiMC) AS ROWINDEX,S_FeiLeiMC, ID FROM IBCS_TL.JG_YongShuiFL WHERE I_Ancestor=0) b
    on a.I_Ancestor=b.ID) k ON A.I_JH=k.I_JH AND A.I_TJH=k.I_TJH 
      WHERE  k.s_feileimc in ('工业用水','经营服务用水','居民生活用水','特种行业用水'
  ,'趸售水','行政事业用水','其他')  AND A.I_ZT<>-1 AND TO_CHAR(D_KaiZhangRQ,'yyyyMMdd') BETWEEN '20100401' AND
      '20100430'  group by k.s_feileimc  union all SELECT SUM(-I_ZongYongL) AS SL, case when k.s_feileimc='工业用水' then '工业调整' when k.s_feileimc='经营服务用水' then '经营调整' 
  when k.s_feileimc='居民生活用水' then '居民调整' when k.s_feileimc='特种行业用水' then '特种调整' when k.s_feileimc='趸售水' then '趸售调整' 
  when k.s_feileimc='行政事业用水' then '行政调整' when k.s_feileimc='其他' then '其它调整' else '' end  AS FLAG 
      FROM IBCS_TL.ZW_TiaoZheng A INNER JOIN IBCS_TL.ZW_TiaoZheng_ZB B ON A.I_TiaoZhengBH=B.I_TiaoZhengBH 
      INNER JOIN (select b.ROWINDEX as ID,a.I_JH,a.I_TIAOJIAH as I_TJH,b.S_FeiLeiMC from 
    (SELECT * FROM ibcs_TL.JG_YongShuiFL where I_Ancestor<>0) a
    left join
    (SELECT ROW_NUMBER() over (ORDER BY S_FeiLeiMC) AS ROWINDEX,S_FeiLeiMC, ID FROM IBCS_TL.JG_YongShuiFL WHERE I_Ancestor=0) b
    on a.I_Ancestor=b.ID) k ON B.I_JH=k.I_JH AND B.I_TiaoJaH=k.I_TJH
      WHERE  k.s_feileimc in ('工业用水','经营服务用水','居民生活用水','特种行业用水'
  ,'趸售水','行政事业用水','其他')  and DL_CaoZuoRQ>=20100501 AND DL_CaoZuoRQ<=20100531 
      AND A.I_Y*100+A.I_M<201005 AND A.I_JLZT=0 
      AND B.I_JLZT=0 AND B.I_LeiXing=1 AND B.I_FeiYongDLBH=580 group by k.s_feileimc union all SELECT SUM(-I_ZongYongL) as SL, case when k.s_feileimc='工业用水' then '工业调整' when k.s_feileimc='经营服务用水' then '经营调整' 
  when k.s_feileimc='居民生活用水' then '居民调整' when k.s_feileimc='特种行业用水' then '特种调整' when k.s_feileimc='趸售水' then '趸售调整' 
  when k.s_feileimc='行政事业用水' then '行政调整' when k.s_feileimc='其他' then '其它调整' else '' end  AS FLAG 
      FROM IBCS_TL.ZW_ChongZheng A INNER JOIN IBCS_TL.ZW_ChongZheng_ZB B ON A.I_ChongZhengBH=B.I_ChongZhengBH
      INNER JOIN (select b.ROWINDEX as ID,a.I_JH,a.I_TIAOJIAH as I_TJH,b.S_FeiLeiMC from 
    (SELECT * FROM ibcs_TL.JG_YongShuiFL where I_Ancestor<>0) a
    left join
    (SELECT ROW_NUMBER() over (ORDER BY S_FeiLeiMC) AS ROWINDEX,S_FeiLeiMC, ID FROM IBCS_TL.JG_YongShuiFL WHERE I_Ancestor=0) b
    on a.I_Ancestor=b.ID) k ON B.I_JH=k.I_JH AND B.I_TiaoJaH=k.I_TJH
      WHERE  k.s_feileimc in ('工业用水','经营服务用水','居民生活用水','特种行业用水'
  ,'趸售水','行政事业用水','其他')  AND A.I_JLZT=0 and TO_CHAR(D_ChongZhengRQ,'yyyyMMdd') BETWEEN
      '20100501' AND '20100531' AND A.I_LeiXing=4 
      AND B.I_JLZT=0 AND B.I_LeiXing=1 AND B.I_FeiYongDLBH=580 
      AND A.I_Y*100+A.I_M<201005 group by k.s_feileimc union all SELECT SUM(-I_ZongYongL) as SL, case when k.s_feileimc='工业用水' then '工业减免' when k.s_feileimc='经营服务用水' then '经营减免' 
  when k.s_feileimc='居民生活用水' then '居民减免' when k.s_feileimc='特种行业用水' then '特种减免' when k.s_feileimc='趸售水' then '趸售减免' 
  when k.s_feileimc='行政事业用水' then '行政减免' when k.s_feileimc='其他' then '其它减免' else '' end  AS FLAG 
      FROM IBCS_TL.ZW_JianMian A INNER JOIN IBCS_TL.ZW_JianMian_ZB B ON A.I_JianMianBH=B.I_JianMianBH
      INNER JOIN (select b.ROWINDEX as ID,a.I_JH,a.I_TIAOJIAH as I_TJH,b.S_FeiLeiMC from 
    (SELECT * FROM ibcs_TL.JG_YongShuiFL where I_Ancestor<>0) a
    left join
[/code]

Answer 9

单步调试过了，整个存储过程都不报错，在最后OPEN CURSOR的时候提示“ORA-06502:PL/SQL:numeric or value error:character string buffer too small”。

Answer 10

Any help will be appreciated.
Though it is not a neat solution,thank you all the same.
And if you have a neat tips to solve this problem,please post.

Answer 11

varchar2(32767)  最大varchar2(4000)
改成clob

Answer 12

ORA-06502:PL/SQL:numeric or value error:character string buffer too small.
这个错误在某些时候不一定是超长，可能是你在拼字符的时候有类型转换错误。

Answer 13

varchar2定义为变量类型时，最大可存储32767个字符；varchar2定义为表字段类型时，最大是4000。
nclob在存储过程中作为变量使用，粘个例子呗^_^

Answer 14

nclob对象类型在存储过程中怎么使用？

Answer 15

select lengthb(动态sql) from dual
和
select length(动态sql) from dual看看动态sql 是否超长

Answer 16

substr  对应dbms_lob.substrinstr 对应dbms_lob.instr length d对应 dbms_lob.getlength用法一样

Answer 17

nclob类型类似C指针，不实际存储，而是存储地址引用。
那么怎么使用这种类型来操作超长字符串呢？

Answer 18

我知道，是字符串长度超过VARCHAR2变量的最大值