Re: ? PL/SQL: numeric or value error: character string buffer too small

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 4 Jul 2001 07:40:32 -0700
Message-ID: <9hv9t002hap@drn.newsguy.com>

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)

---

hello world

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 Corp 

Received on Wed Jul 04 2001 - 09:40:32 CDT