CLOB greater than 32767 characters

[xtreemnet]

Hi,

I am extracting data from a text file which is stored as a clob in the table. I wrote a function to extract this information. It works fine for smaller files. It did not return anything for one of the clob which has has a file of size 142kb. I guess its beacuse of the size limit for clob (4000 characters)/ datatype varchar2 limit (32767).

Is there a way to deal with such big file size? or how do I modify my clob to read this file?

Thanks.

 

[oradba101]

Greetings,

Oracle CLOBS are capable og holding 4GB of data.

Regards,

William Chadbourne
Oracle DBA

 

[xtreemnet]

But the variables in which I would like to fetch the clob has limits I guess.

 

[SantaMufasa]

Can you please post an excerpt of the code you are using presently that is causing you the limitations you are encountering? William is correct...you can use CLOB as the variable data type to fetch the data.

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

 

[Thargy]

xtreemnet,

32,767 is the old microsoft limit for integers.

Unless things haven't moved on since I last did VB6, you've hit a limitation of your front-end application, and not oracle.

As I recall, this applied in VB, access VBA and excel too.

Regards

T

Grinding away at things Oracular

 

[xtreemnet]

Hi Mufasa,

Here is my function:

create or replace FUNCTION EXTRACT_NT(rec_string CLOB, srch_str VARCHAR2, strField VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS

ret_str VARCHAR2(4);
len NUMBER;
strText CLOB;
SPosICP number;
EPosICP number;
RetPos number;
SPos number;
EPos number;
RetStr varchar(100);

BEGIN

IF rec_string IS NULL THEN
RETURN NULL;
END IF;
-- check size
len := dbms_lob.getlength(rec_string);
strText := dbms_lob.substr(rec_string,len,1);
SPosICP := instr(dbms_lob.substr(strText),srch_str);
EPosICP := SPosICP + 14;

case strField

when 'R' then
SPos := instr(strText,',', EPosICP, 1);
EPos := instr(strText,',', EPosICP, 2);

when 'U' then
SPos := instr(strText,',', EPosICP, 2);
EPos := instr(strText,',', EPosICP, 3);

when 'H' then
SPos := instr(strText,',', EPosICP, 3);
EPos := instr(strText,',', EPosICP, 4);

when 'S' then
SPos := instr(strText,',', EPosICP, 4);
EPos := instr(strText,',', EPosICP, 5);

when 'B' then
SPos := instr(strText,',', EPosICP, 5);
EPos := instr(strText,',', EPosICP, 6);

when 'T' then
SPos := instr(strText,',', EPosICP, 6);
EPos := instr(strText,',', EPosICP, 7);

when 'P' then
SPos := instr(strText,',', EPosICP, 7);
EPos := instr(strText,',', EPosICP, 8);

end case;

if EPos - SPos > 1 then
RetStr := substr(strText, SPos + 1, EPos - SPos-1);
else
RetStr := '';
end if;
--DBMS_OUTPUT.PUT_LINE(RetStr);
RETURN RetStr;

END;

 

[SantaMufasa]

Xtreemnet,

You should not need to use the "dbms_lob" packaged functions to process your CLOB expressions. Just use the standard LENGTH and SUBSTR functions against your CLOB expressions. In fact, from my tests, the "dbms_lob" functions can produce erroneous results on CLOB expressions, while the standard Oracle functions produce good results. Here are some code tests that use variations of your code, and the tests show the errors of which I am speaking:

desc clobtab
 Name                    Null?    Type
 ----------------------- -------- ----------------
 CONTENT                          CLOB

select length(content) from clobtab;

LENGTH(CONTENT)
---------------
          64000
          64000

2 rows selected.

select dbms_lob.substr(content,length(content),1) x
from clobtab;

X
-


2 rows selected.

select substr(content,length(content),1) x
from clobtab;

X
-
t
t

2 rows selected.

Notice that the "dbms_lob.substr" function produces bogus results, while Oracle's "substr" function produces good results.

Since I am not familiar with your data contents/behaviour, I cannot produce meaningful proof-of-concept results, but perhaps modifying your code to simply use standard Oracle functions (in place of the "dbms_lob" functions) will give you the improved results that you want.

Also, perhaps you could benefit from changing your RETURN-expression data type from "VARCHAR DETERMINISTIC" to simply "CLOB".

Let us know your findings.


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

 

[xtreemnet]

Hi Mufasa,

With use of dbmc_lob the following is the output for the column:

null
MEEN
MEEN
MEEN
TRUS
CTCT
MEEN
MEEN
MEEN

Please note that the record where its returning null involves the file having size of 142kb.

And using substr it gives me 4 characters (which does not tally with expected output) with unprintable square looking characters.

The new function looks like:

create or replace FUNCTION EXTRACT_NT(rec_string CLOB, srch_str VARCHAR2, strField VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS

ret_str VARCHAR2(4);
len NUMBER;
strText CLOB;
SPosICP number;
EPosICP number;
RetPos number;
SPos number;
EPos number;
RetStr varchar(100);

BEGIN

IF rec_string IS NULL THEN
RETURN NULL;
END IF;

-- check size
len := length(rec_string);
strText := substr(rec_string,1,len);
SPosICP := instr(strText,srch_str);
EPosICP := SPosICP + 14;


case strField

when 'R' then
SPos := instr(strText,',', EPosICP, 1);
EPos := instr(strText,',', EPosICP, 2);

when 'U' then
SPos := instr(strText,',', EPosICP, 2);
EPos := instr(strText,',', EPosICP, 3);

when 'H' then
SPos := instr(strText,',', EPosICP, 3);
EPos := instr(strText,',', EPosICP, 4);

when 'S' then
SPos := instr(strText,',', EPosICP, 4);
EPos := instr(strText,',', EPosICP, 5);

when 'B' then
SPos := instr(strText,',', EPosICP, 5);
EPos := instr(strText,',', EPosICP, 6);

when 'T' then
SPos := instr(strText,',', EPosICP, 6);
EPos := instr(strText,',', EPosICP, 7);

when 'P' then
SPos := instr(strText,',', EPosICP, 7);
EPos := instr(strText,',', EPosICP, 8);

end case;

if EPos - SPos > 1 then
RetStr := substr(strText, SPos + 1, EPos - SPos-1);
else
RetStr := '';
end if;
--DBMS_OUTPUT.PUT_LINE(RetStr);
RETURN RetStr;

END;



Please let me know your email in case you want to test the file.

Thanks,

 

[SantaMufasa]

Tek-Tips does not want us to list our e-mail addresses explicitly, but you can find my e-mail address by following my signature link.

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

 

[Thargy]

xtreemnet,

can you please post the results of selecting the length of the clob fields, along with some way of determining which is which.

If the text file has failed to load into the CLOB field in the first place, the received CLOB will be NULL and the function will correctly return a NULL.

You must be certain that the file has correctly loaded, before going ahead and debugging code which examines a null and correctly says "It's a null Jim, but not as we know it."

Regards

T

Grinding away at things Oracular

 

[xtreemnet]

Hi thargtheslayer ,

The following select:

select dbms_lob.getlength(dt_clob)
from rawfile
where id = 162205

Returns:

144943

*******************************************

And the following select:

select dbms_lob.substr(data_clob,dbms_lob.getlength(dt_clob),1)
from rawfile
where id = 162205

returns 'Null'

 

[jimirvine]

There may have been a mix up here guys:
syntax for substr
substr(string,startpos, numberOfCharsToxtract)
syntax for dbms_lob.substr
substr(string, numberOfCharsToxtract,startpos)

Which may explain your 'erroneous results'.
However, I agree with Dave and I tend to stick with the non dbms_lob functions when I can

 

[Thargy]

xtreemnet,

to my mind that appears highly suspicious.

Can you confirm using the non dbms_lob function that you have a clob with a length > zero? If you haven't, then the CLOB filed has not been correctly loaded from the file in the first place.

I know this may appear pedestrian and plodding, but you must be certain of your facts, before proceeding further.

Regards

T

Grinding away at things Oracular

 

[xtreemnet]

Hi theargtheslayer,

select length(dt_clob)
from rawfile
where id = 162205

Returns:

144943

 

[Thargy]

x,

can you confirm that "rawfile" is a table in Oracle? I don't see how it could be anything else, but I want to be certain.

Are you invoking your function from VB or from within Oracle?

I am thinking that if it's being invoked from vb, the CLOB that you're passing in to the function may exceed some internal VB limit, and produce dud results. Passing a CLOB in Oracle should work, but I'd suggest a NOCOPY if you do.

Yours in slight puzzlement

Tharg

Grinding away at things Oracular

 

[xtreemnet]

Hi theargtheslayer,

I am running this from Oracle's free SQL Developer interface. The table is oracle 10g.

For your information if I paste my file in the clob variable the following error is returned:

declare
rec_string CLOB := 'Paste my file here';
ret_str VARCHAR2(4);
len integer;
strText CLOB;
SPosICP number;
EPosICP number;
RetPos number;
SPos number;
EPos number;
RetStr varchar(100);
strField varchar(1) := 'S';

BEGIN
IF rec_string IS NULL THEN
return null;
END IF;
-- check size
len := dbms_lob.getlength(rec_string);
DBMS_OUTPUT.PUT_LINE(len);

END;


Error report:
ORA-06550: line 2, column 22:
PLS-00172: string literal too long
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

I also tried deleting part by part from this file till I reached the size 32767 and the error disappeared. So its certein that the pl/sql clob variable cannot take more than 32767 caharcters.

I did some search on the net and found following information:

Using SQL and PL/SQL to Access LOBs -
Data from CLOB and BLOB columns can be referenced by regular SQL statements, such as: INSERT, UPDATE and SELECT.

There is no piecewise INSERT/UPDATE/fetch routine in PL/SQL.
Therefore the amount of data that can be accessed
from the LOB column is limited by the maximum character buffer size.
In Oracle9i, PL/SQL supports character buffer sizes up to 32767 bytes.
Hence only LOBs of sizes up to 32767 bytes can be accessed by PL/SQL applications.

If you need to access more than 32k, OCI callouts have to be made from the PL/SQL code
to utilize the APIs for piecewise insert and fetch.

Does this apply in my case?

Thanks,

 

[Thargy]

x,

sorry for the delay in responding, I've been on hold over Christmas and the new year.

If you're still struggling, then I suggest the following.

Divide the file up into chunks of say 32,000 characters, and give up the 767 to provide some wriggle room.

Temporarily add another CLOB field to the table, named TEMP_CLOB. Add the first 32,000 characters to the CLOB field directly. Add another 32,000 to TEMP_CLOB. Then say
UPDATE RAWFILE SET CLOB = CLOB||TEMP_CLOB;
UPDATE RAWFILE SET TEMP_CLOB = '';
COMMIT;

Then add another 32,000 characters to TEMP_CLOB and concatenate with CLOB, as above. In this way you can add huge amounts of data, in a size that your system can handle.

Obviously at the end, drop the TEMP_CLOB field. Also check that you've got the expected length for the CLOB.

T


Grinding away at things Oracular

 

[xtreemnet]

Hi

no worries.

I have no privilages to modify the table. I have found another temperrory solution. Instead of passing the whole file, I will find the start position of the srch_str VARCHAR2 parameter in the select query and select the substring of about 500 characters and then extract the required information from the function.

the new funcxtion would like like :

create or replace FUNCTION EXTRACT_NT(rec_string CLOB, strField VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS
ret_str VARCHAR2(4);
len NUMBER;
strText CLOB;
SPosICP number := 1;
EPosICP number;
RetPos number;
SPos number;
EPos number;
RetStr varchar(100);
BEGIN
IF rec_string IS NULL THEN
RETURN NULL;
END IF;
-- check size
len := dbms_lob.getlength(rec_string);
strText := dbms_lob.substr(rec_string,len,1);
EPosICP := SPosICP + 14;

case strField

when 'R' then
SPos := instr(strText,',', EPosICP, 1);
EPos := instr(strText,',', EPosICP, 2);

when 'U' then
SPos := instr(strText,',', EPosICP, 2);
EPos := instr(strText,',', EPosICP, 3);

when 'H' then
SPos := instr(strText,',', EPosICP, 3);
EPos := instr(strText,',', EPosICP, 4);

when 'S' then
SPos := instr(strText,',', EPosICP, 4);
EPos := instr(strText,',', EPosICP, 5);

when 'B' then
SPos := instr(strText,',', EPosICP, 5);
EPos := instr(strText,',', EPosICP, 6);

when 'T' then
SPos := instr(strText,',', EPosICP, 6);
EPos := instr(strText,',', EPosICP, 7);

when 'P' then
SPos := instr(strText,',', EPosICP, 7);
EPos := instr(strText,',', EPosICP, 8);

end case;

if EPos - SPos > 1 then
RetStr := substr(strText, SPos + 1, EPos - SPos-1);
else
RetStr := '';
end if;
--DBMS_OUTPUT.PUT_LINE(RetStr);
RETURN RetStr;
END;

But I would like to avoid using long expressions in the select statement.

So please let me know if there is an alternative solution.

Thanks a lot for your help.