SQL query for CLOB

[xtreemnet]

Hi,

I am trying execute the following SQL:

select id, length(rec), dbms_lob.substr(rec, length(rec) ,1)
from ITEMS
WHERE
ID = 'PROD'

The length for 'rec' is 3832 characters. The query fails with message : 'ORA-06502 PL/SQL numeric or values error: Character buffer string too small'

How do I solve this problem?

Thanks.

 

[sbing]

Hi,

Try to make the substr character buffer length slightly larger as in the following:

select id, length(rec), dbms_lob.substr(rec, 4096 ,1)
from ITEMS
WHERE
ID = 'PROD'

or if that doesn't work try giving it the maximum character buffer length:

select id, length(rec), dbms_lob.substr(rec, 32768 ,1)
from ITEMS
WHERE
ID = 'PROD'


scott...

 

[SantaMufasa]

XTreem,

If "rec" is a CLOB column, you can successfully execute any function against "rec" that you would against any other VARCHAR column. If you are using SQL*Plus, you will want to increase the size of SQL*Plus's buffer with the command "SET BUFFER <some large value>" else SQL*Plus uses a default of 4000.

In the example, below, I list the length and last 26 characters of the CLOB column in a table containing the CLOB column:

select length(content),substr(content,-26) from clobber;

LENGTH(CONTENT) SUBSTR(CONTENT,-26)
--------------- --------------------------
         128000 56789012345678901234567890
          64000 opqrstabcdefghijklmnopqrst

So, unless I'm missing something about your inquiry, the above should work for you. Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[xtreemnet]

I just tried the below and still getting the error:

SQL> SET BUFFER 100000
SQL> select id, length(rec), dbms_lob.substr(rec, 32000 ,1)
2 from items
3 where ID = 'PROD';
select id, length(rec), dbms_lob.substr(rec, 32000 ,1)
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1

If use the following the substring returned is null

SQL> select id, length(rec), dbms_lob.substr(rec, 32768 ,1)
2 from items
3 where ID = 'PROD';

ID
--------------------------------------------------------------------------------
LENGTH(REC)
-----------
DBMS_LOB.SUBSTR(REC,32768,1)
--------------------------------------------------------------------------------
PROD
3832


I just realised that the 'rec' column is NCLOB. But I guess it still should have worked.

 

[SantaMufasa]

XTreem,

My dimness on your issue revolves around my not knowing (for sure) what the "dbms_lob_substr()" function does. It was my presumption (perhaps incorrect presumption) that it is an Oracle-supplied packaged procedure to substring a large object.

If that is the case, it is my assertion that you do not need to invoke the "dbms_lob_substr()" function to substring your CLOB/NCLOB expression -- just use SUBSTR() as you normally would.

The error you are seeing (IMHO, and until someone shows/proves otherwise) is a bug in the "dbms_lob_substr()" function.

Let us know what happens if you simply use the substr() function to do what you want.

BTW, could you please confirm what you want "dbms_lob_substr()" to do?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[xtreemnet]

If I use just substr then the substr just returns null:

SQL> select id, length(rec),substr(rec,32768,1)
2 from items
3 WHERE ID = 'PROD';

ID
--------------------------------------------------------------------------------
LENGTH(REC)
-----------
SUBSTR(REC,32768,1)
--------------------------------------------------------------------------------
PROD
3832

I am trying write a function which will extract individual items which are separated by a value marker from this clob.
An example of the data is as below:

'BPM.PARAM?BPM.PROC?BPM.REPORTS?BPM0001M?BPM0003M?BPM0004M?BPM0005M?BPM0006M?BPM0011AM?BPM0011M?BPM0012M?BPM0013M?BPM0031M?BPM0031TM?'

The clob and the position (number) will be passed to the function and the function will return a string. Since teh data is of clob tyep I was trying to use dbms_lob package.

 

[SantaMufasa]

Xtreem,

The length of the CLOB data on the sample record is 3,832 characters; your substr functions says, "Take 1 character beinning at character number 32,768 in the CLOB "REC"."...If you only have 3,832 characters, certainly character number 32,768 is NULL.

Since that is not what you want, please restate what you do want, and we will try to help you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]