Very Computer

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

btw. the table altdaten is the BIGGER table, but has no foreign keys.

anybody any idea?

thanks in advance

at least 1 element in the column beschluss_text seams to be longer than
varchar2 can be (about 32600)
so you need either cut the column beschluss_text  to that size (which
would eventually influence your result)
or you use DBMS_LOB.INSTR instead of making a varchar out of it and making
a normal instr

ok, thanks. but
(dbms_lob.instr(lower(dbms_lob.substr(beschluss_text)), 'burg', 1, 1) !=
0)

returns:
wrong number or types of arguments in call to 'INSTR'
what i don't understand.

i need the lower, because i want to do somthing like
select * from table where lower(column) like '%foo%',
but column is a clob.

Quote:

> at least 1 element in the column beschluss_text seams to be longer than
> varchar2 can be (about 32600)
> so you need either cut the column beschluss_text  to that size (which
> would eventually influence your result)
> or you use DBMS_LOB.INSTR instead of making a varchar out of it and making
> a normal instr

Quote:

>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:

Table created.

1 row created.

DBMS_LOB.SUBSTR(X)
----------------------
hello world

  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.

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.

Quote:>btw. the table altdaten is the BIGGER table, but has no foreign keys.

>anybody any idea?

>thanks in advance

--

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