Substring CLOB value with value more than 32K

Question

Kindly, I am trying to substring clob value by removing the first 33 characters and the last 2 characters,

I try with the following simple code but it's returned an error: ORA-06502: PL/SQL: numeric or value error

DECLARE
 p_Clob_Input CLOB := ''; --> value more than 32K
 p_Clob_Output CLOB; --> input CLOB value after removing first 33 characters and last 2 characters
BEGIN
 Dbms_Lob.Createtemporary(p_Clob_Output, FALSE);
 Dbms_Lob.Writeappend(p_Clob_Output, Dbms_Lob.Getlength(p_Clob_Input)-35,Dbms_Lob.Substr(p_Clob_Input,Dbms_Lob.Getlength(p_Clob_Input)-2, 33));
END;

Then I try with the following code which is working fine, but still, it will fail in case the length is 32001 or 64001, also I am feeling it's too long a code to achieve the objective,

DECLARE
 p_Clob_Index NUMBER;
 p_Length   NUMBER;
 p_Chunk    VARCHAR2(32000);
 p_Clob_Input CLOB := ''; --> value more than 32K
 p_Clob_Output CLOB; --> input CLOB value after removing first 33 characters and last 2 characters
BEGIN
 Dbms_Lob.Createtemporary(p_Clob_Output, FALSE);
 p_Length   := Dbms_Lob.Getlength(p_Clob_Input);
 p_Clob_Index := 1;
 WHILE p_Clob_Index <= p_Length
 LOOP
  IF p_Clob_Index = 1
  THEN
   IF p_Length > 32000
   THEN
    p_Chunk := Dbms_Lob.Substr(p_Clob_Input, 32000, 33);
   ELSE
    p_Chunk := Dbms_Lob.Substr(p_Clob_Input, p_Length - 2, 33);
   END IF;
  ELSE
   IF p_Clob_Index > p_Length - 32000
   THEN
    p_Chunk := Dbms_Lob.Substr(p_Clob_Input, (p_Length - p_Clob_Index) - 1, p_Clob_Index);
   ELSE
    p_Chunk := Dbms_Lob.Substr(p_Clob_Input, 32000, p_Clob_Index);
   END IF;
  END IF;
  IF p_Clob_Index > p_Length - 32000
  THEN
   p_Clob_Index := p_Length + 1;
  ELSE
   p_Clob_Index := p_Clob_Index + 32000;
  END IF;
  Dbms_Lob.Writeappend(p_Clob_Output, Length(p_Chunk), p_Chunk);
 END LOOP;
END;

Appreciate your support

My DB Version is 11.2.0.4.0

Thanks ...

Answer 1

Your first code block is failing with large values because the second argument to writeappend() is varchar2, so is limited to 32k (in a PL/SQL context, 4k from SQL).

You can use the copy procedure instead, which has CLOB arguments:

DBMS_LOB.COPY (  
  dest_lob    IN OUT NOCOPY CLOB  CHARACTER SET ANY_CS,  
  src_lob     IN            CLOB  CHARACTER SET dest_lob%CHARSET,  
  amount      IN            INTEGER,  
  dest_offset IN            INTEGER := 1,  
  src_offset  IN            INTEGER := 1);  

So you can do:

dbms_lob.createtemporary(p_clob_output, false);
dbms_lob.copy(p_clob_output, p_clob_input, dbms_lob.getlength(p_clob_input) - 35, 1, 34);

db<>fiddle demo

Your variable names (and the assignment you hinted at with "value more than 32K", which also won't work for large literal values) suggests you might be working towards a procedure to do this; which you could do as:

create or replace procedure your_proc (
  p_clob_input in clob,
  p_clob_output out clob,
  p_trim_start number,
  p_trim_end number
) as
begin
  dbms_lob.createtemporary(p_clob_output, false);
  dbms_lob.copy(
    dest_lob => p_clob_output,
    src_lob => p_clob_input,
    amount => dbms_lob.getlength(p_clob_input) - (p_trim_start + p_trim_end),
    dest_offset => 1,
    src_offset => p_trim_start + 1);
end;
/

db<>fiddle

Though it's arguable if that is actually going to make your life much easier than just calling dbms_lob directly.

Answer 2

Just use SUBSTR as it works with CLOB values longer than 32767 characters:

DECLARE
 p_Clob_Input   CLOB := EMPTY_CLOB();
 p_Clob_Output  CLOB;
 p_start_offset PLS_INTEGER := 33;
 p_end_offset   PLS_INTEGER := 2;
BEGIN
 FOR i IN 1 .. 10 LOOP
   p_clob_input := p_clob_input || LPAD('1234567890', 4000, '1234567890');
 END LOOP;
 
 p_clob_output := SUBSTR(
   p_clob_input,
   p_start_offset + 1,
   LENGTH( p_clob_input ) - p_start_offset - p_end_offset
 );

 DBMS_OUTPUT.PUT_LINE( 'Lengths:' );
 DBMS_OUTPUT.PUT_LINE( LENGTH( p_clob_input ) );
 DBMS_OUTPUT.PUT_LINE( LENGTH( p_clob_output ) );
 DBMS_OUTPUT.PUT_LINE( 'Starts:' );
 DBMS_OUTPUT.PUT_LINE( SUBSTR( p_clob_input, 1, 40 ) );
 DBMS_OUTPUT.PUT_LINE( LPAD( ' ', p_start_offset, ' ' ) || SUBSTR( p_clob_output, 1, 40 - p_start_offset ) );
 DBMS_OUTPUT.PUT_LINE( 'Ends:' );
 DBMS_OUTPUT.PUT_LINE( SUBSTR( p_clob_input, 39961 ) );
 DBMS_OUTPUT.PUT_LINE( SUBSTR( p_clob_output, 39961 - p_start_offset ) );
END;
/

Which outputs:

Lengths:
40000
39965
Starts:
1234567890123456789012345678901234567890
                                 4567890
Ends:
1234567890123456789012345678901234567890
12345678901234567890123456789012345678

db<>fiddle here