SQL & PL/SQL

Hi Experts,

I have a text which is more than 4K characters, so I am not able to store it in a table's CLOB column using a simple INSERT statement.

Read this technique by Solomon and have tried like this, but not getting the result:

DECLARE
       query_txt CLOB := to_clob(q'[my_text_goes_here]');
       i NUMBER;
       j NUMBER;
       x CLOB;
BEGIN
       i := 4000;
       j := 1;
     --dbms_output.put_line(Dbms_Lob.getlength(query_txt));
     --WHILE (DBMS_LOB.SUBSTR(query_txt,i,j) IS NOT NULL)
       x := 'insert into query_master values(3,q''[';
       
       LOOP
           --Dbms_Output.put_line(j||' *** '||i||' *** '||DBMS_LOB.SUBSTR(query_txt,i,j));
  
           x := x||DBMS_LOB.SUBSTR(query_txt,i,j);
           j := i+1;
           i := i+4000;
           
           EXIT WHEN DBMS_LOB.SUBSTR(query_txt,i,j) IS NULL;
       END LOOP;
      
     x := x||']'',''aaa,bb'',''Y'',''BK FILED'')';
                               
  EXECUTE IMMEDIATE x; -- USING :query_txt;
     --Dbms_Output.put_line(x);
     --Dbms_Output.put_line(Dbms_Lob.SubStr(x,1000,1));

EXCEPTION
WHEN OTHERS then
     Dbms_Output.put_line(Dbms_Utility.format_error_backtrace||SQLERRM);       
END;

My idea was to make it in chunks of  4000 chars and then pass it to Insert statement.

SQL> desc query_master;

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 QID                                                NUMBER
 QUERY_TEXT                                         CLOB
 QUERY_COLS                                         VARCHAR2(3500)
 IS_ACTIVE                                          CHAR(1)
 WORKSHEET_NAME                                     VARCHAR2(50)

Could you please let me know where I am getting wrong?

Thanks and Regards,

-- Ranit

( on Oracle 11.2.0.3.0 - Exadata )