Dear Admin,

I have a php file that generates SQL file that downloads data from development site. This SQL script is generated by a PHP applet in order to generate scripts for execution in production site (to update production tables with new records for certain product). Now, there is one particular section of the php scripts that reads a clob from one of the table - kbrule and places it in the sql script as a parameter of the function. However, the length of this clob text is 12967. However, max length of a varchar in oracle 11g is 4000 bytes. Hence, the sql script generates an error that that says, input is too long (>2499 characters) - line ignored.

The SQL script contains a call to the procedure for Insert Clob

CREATE OR REPLACE procedure JOM.insertClob(P_IDX in number, P_TXT in varchar2) as g_clob clob;
select RULE_QUERYDATA into g_clob from KBRULE Where RULE_ID = P_IDX for update;
dbms_lob.write(g_clob, length(P_TXT), 1, P_TXT);

Here, P_TXT is unable to hold the 12967 length of the text that is received from a clob column in development table. What is the best way to generate SQL scripts from development site to generate insertclob statements for production site tables.

I'd really appreciate a quick reply
Nikunj Jain
Software Engineer