replacing a clob data

From: Anupam Pandey <my.oralce_at_gmail.com>
Date: Tue, 15 Feb 2011 12:00:05 +0530
Message-ID: <AANLkTikx0DQocq3GKAgt2-PqrKhdDUYVuYq7KKv6jNv__at_mail.gmail.com>

Hi All,

         I have a application where we store the query in one table which has clob data type .
The table name is a placeholder in the query .We pass the query template to our procedures
but the procedure takes the template as varchar2.

Till now our template was well below 32K so the replace function on the template was working .
Not the template has grown to more than 40K length . Hence the replace is giving a truncated output .

So i tried a way to break the clob into different varchar chunks and the replace the placeholder . But I am
always getting a garbled output from that replace function ( I have attached) .

Let me know your thoughts on it . Am I doint anything wrong here ?

Thanks And regards,

    Anupam

CREATE OR REPLACE FUNCTION fn_replace_clob

( p_clob          IN CLOB,
  p_what          IN VARCHAR2,
  p_with          IN VARCHAR2 ) RETURN CLOB IS

  c_whatLen       CONSTANT PLS_INTEGER := LENGTH(p_what);
  c_withLen       CONSTANT PLS_INTEGER := LENGTH(p_with);

  l_return        CLOB;
  l_segment       CLOB;
  l_pos           PLS_INTEGER := 1-c_withLen;
  l_offset        PLS_INTEGER := 1;

BEGIN   IF p_what IS NOT NULL THEN
    WHILE l_offset < DBMS_LOB.GETLENGTH(p_clob) LOOP

      l_segment := DBMS_LOB.SUBSTR(p_clob,32767,l_offset);
      LOOP
        l_pos := DBMS_LOB.INSTR(l_segment,p_what,l_pos+c_withLen);
        EXIT WHEN (NVL(l_pos,0) = 0) OR (l_pos = 32767-c_withLen);
        l_segment := TO_CLOB( DBMS_LOB.SUBSTR(l_segment,l_pos-1)
                            ||p_with

||DBMS_LOB.SUBSTR(l_segment,dbms_lob.getlength(l_segment)-c_whatLen-l_pos-c_whatLen+1,l_pos+c_whatLen));
         dbms_output.put_line('1a=='||  DBMS_LOB.SUBSTR(l_segment,l_pos-1));
         dbms_output.put_line('2a=='||  p_with);
         dbms_output.put_line('3a=='||

DBMS_LOB.SUBSTR(l_segment,dbms_lob.getlength(l_segment)-c_whatLen-l_pos-c_whatLen+1,l_pos+c_whatLen));

      END LOOP;
      l_return := l_return||l_segment;
      l_offset := l_offset + dbms_lob.getlength(l_segment) - c_whatLen;

    END LOOP;
  END IF;   RETURN(l_return);

END;
/

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Feb 15 2011 - 00:30:05 CST