Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.tools -> Re: ? PL/SQL: numeric or value error: character string buffer too small
In article <3B432561.73E4E02A_at_dasburo.de>, Alexander says...
>
>hi,
>
>i have two tables, that contain clobs.
>
>while this statement is ok:
>select nummer from altdaten where
>instr(lower(dbms_lob.substr(BEMERKUNG_FUER_KREDI)), 'boy') > 0;
>
>this statement:
>select id from beschluss where
>instr(lower(dbms_lob.substr(beschluss_text)), 'boy') > 0;
>returns
>ERROR at line 1:
>ORA-06502: PL/SQL: numeric or value error: character string buffer too
>small
>ORA-06512: at line 1
>
the table beschluss must have some clobs that exceed 4000 bytes in length:
ops$tkyte_at_ORA8I.WORLD> create table t ( x clob ); Table created.
ops$tkyte_at_ORA8I.WORLD> insert into t values ( 'hello world' ); 1 row created.
ops$tkyte_at_ORA8I.WORLD> select dbms_lob.substr(x) from t;
DBMS_LOB.SUBSTR(X)
ops$tkyte_at_ORA8I.WORLD> declare
2 l_clob clob;
3 begin
4 insert into t values ( empty_clob() ) returning x into l_clob;
5
6 dbms_lob.writeappend( l_clob, 4001, rpad('*',4001,'*') );
7 end;
8 /
PL/SQL procedure successfully completed.
ops$tkyte_at_ORA8I.WORLD>
ops$tkyte_at_ORA8I.WORLD> select dbms_lob.substr(x) from t;
ERROR:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1
dbms_lob.substr defaults to returning the entire string as a varchar2-- but SQL can only handle 4000 bytes as a varchar2 hence when you exceed it, the exception is raised.
The best approach for you will be to use interMedia text. Then you can query:
select id from beschluss where contains( beschluss_text,'boy') > 0;
it'll be case insenstive, work on upto 4gig of clob /blob data (or files whatever) and be infinitely fast as compared to an instr of a lower of a substr.
>btw. the table altdaten is the BIGGER table, but has no foreign keys.
>
>anybody any idea?
>
>thanks in advance
-- Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/ Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle CorpReceived on Wed Jul 04 2001 - 09:40:32 CDT
![]() |
![]() |