Inserting more than 4000 Characters into an Oracle CLOB field:

This example was tested on Oracle 9.2 and was able to insert 30,000 Characters.
Please, follow the steps:

1- Create a table named "TEST" with 2 fields:
IDX datatype=NUMBER
TXT datatype=CLOB

2- Create a procedure named "P_TEST" which contains this code:
(P_IDX in int,P_TXT in varchar2)as

begin
insert into TEST values(P_IDX,P_TXT);
end;

3- From Dreamweaver, create a page and name it "clob.asp".

4- Create a connection to your Oracle database and name it "mycon".

5- Paste the bellow code in the "clop.asp" page and test it.

6- Let us know if you managed to insert more than 30,000.

7- Good Luck!


<pre id=code><font face=courier size=2 id=code>
&lt;%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%&gt;

&lt;!--#include file="../Connections/mycon.asp" --&gt;

&lt;%
var MM_editAction = Request.ServerVariables("SCRIPT_NAME";
if (Request.QueryString) {
MM_editAction += "?" + Server.HTMLEncode(Request.QueryString);
}

var MM_abortEdit = false;

var MM_editQuery= " ";
%&gt;

&lt;%
var Recordset1 = Server.CreateObject("ADODB.Recordset";
Recordset1.ActiveConnection = MM_mycon_STRING;
Recordset1.Source = "SELECT * FROM TEST ORDER BY IDX";
Recordset1.CursorType = 0;
Recordset1.CursorLocation = 2;
Recordset1.LockType = 1;
Recordset1.Open();
var Recordset1_numRows = 0;
%&gt;

&lt;%
if (String(Request("MM_insert") == "form1" {
// SECTION #1:
// This section is to select the next value of the field IDX and place it in the variable myIDX.
//(my way of creating a sequence as soon as the user submits the form)
var RSGetMaxID = Server.CreateObject("ADODB.Recordset";
RSGetMaxID.ActiveConnection = MM_mycon_STRING;
RSGetMaxID.Source = "SELECT max(IDX+1) as IDX FROM TEST";
RSGetMaxID.CursorType = 0;
RSGetMaxID.CursorLocation = 2;
RSGetMaxID.LockType = 1;
RSGetMaxID.Open();
var Recordset1_numRows = 0;
var myIDX=RSGetMaxID.Fields.Item("IDX".Value;
RSGetMaxID.Close();
if (myIDX == null) {myIDX=1}
// End of SECTION #1

// SECTION #2
// Uses a COMMAND to send the form values to the TEST Procedure
var Command1__P_IDX=myIDX;
var Command1__P_TXT=Request.Form("TXT";

var Command1 = Server.CreateObject("ADODB.Command";
Command1.ActiveConnection = MM_mycon_STRING;
Command1.CommandText = "P_TEST";
Command1.Parameters.Append(Command1.CreateParameter("P_IDX", 5, 1,2,Command1__P_IDX));
Command1.Parameters.Append(Command1.CreateParameter("P_TXT", 200, 1,40000,Command1__P_TXT));
Command1.CommandType = 4;
Command1.CommandTimeout = 10;
Command1.Prepared = true;
Command1.Execute();
var MM_editRedirectUrl = "clob.asp";
if (MM_editRedirectUrl) {
Response.Redirect(MM_editRedirectUrl);}
}
// END SECTION #2
%&gt;

&lt;%
var Repeat1__numRows = -1;
var Repeat1__index = 0;
Recordset1_numRows += Repeat1__numRows;
%&gt;

&lt;html&gt;
&lt;head&gt;
&lt;meta http-equiv="Content-Type" content="text/html; charset=windows-1252"&gt;
&lt;title&gt;Untitled Document&lt;/title&gt;

&lt;/head&gt;

&lt;body&gt;
&lt;table border="1"&gt;
&lt;tr&gt;
&lt;td&gt;ID&lt;/td&gt;
&lt;td&gt;TXT&lt;/td&gt;
&lt;/tr&gt;
&lt;% while ((Repeat1__numRows-- != 0) && (!Recordset1.EOF)) { %&gt;
&lt;tr&gt;
&lt;td&gt;&lt;%=(Recordset1.Fields.Item("IDX".Value)%&gt;&lt;/td&gt;
&lt;td&gt;&lt;%=(Recordset1.Fields.Item("TXT".Value)%&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;%
Repeat1__index++;
Recordset1.MoveNext();
}
%&gt;
&lt;/table&gt;

&lt;form name="form1" method="post" action="&lt;%=MM_editAction%&gt;"&gt;
&lt;p&gt;
&lt;input name="IDX" type="hidden" id="IDX"&gt;
&lt;/p&gt;
&lt;p&gt;
&lt;textarea name="TXT" cols="50" rows="5" id="TXT"&gt;Look Ma, I'm about to insert more than 4000 Characters ...<img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>&lt;/textarea&gt;
&lt;/p&gt;
&lt;p&gt;
&lt;input type="submit" name="Submit" value="Insert"&gt;
&lt;/p&gt;
&lt;input type="hidden" name="MM_insert" value="form1"&gt;
&lt;/form&gt;
&lt;/body&gt;
&lt;/html&gt;
&lt;%
Recordset1.Close();
%&gt;
</font id=code></pre id=code>

Now, insert more than 32k chars:

If you successfully used my previous example, you may have noticed that you were not able to store more than 32000 characters in the CLOB field and this is due to the fact that PLSQL does not support the insertion of strings greater than 32k at one time.
It is important to understand the mechanism behind storing large texts in a CLOB field so please read the rest of the explanation and it is important to know that there are other ways to do such a thing (like doing a package with 2 stored procedures and so on) but here is what I came up with and with one procedure only:
To work around that limitation and if your text is greater than 32000 characters, you will need to send the text in chunks of data with each chunk should not exceed the 32k limit (a loop thing) and store each chunk in the same CLOB field.
It means we need to call the stored procedure many times until the whole string is inserted (loop).
In the ASP script example, the number of time the stored procedure is called can be calculated as: Math.ceil(string length/32000). Example: if we have a string of length 128200 chars then we need to call the stored procedure 5 times.
The first trip to the stored procedure, we insert the new record and initializes the CLOB field and inserts the first 32k chars in it. We can call the first process as "Writing" and we can call the rest of the remaining trips as "Appending" and this means, we add the rest of the chunks to the data previously stored in it.
I added a parameter in the procedure "P_FLAG" to tell it whether we are inserting a new record or updating on an existing record.
Go through the Oracle part and you will understand what I mean and if you are an ASP web developer, go through the ASP script part.




<b><center>The ORACLE Part</center></b>


Here is the fun part:
1- Create a table and name it "TEST":

create table TEST(IDX int, TXT CLOB);

2- Create a stored procedure and name it "P_TEST". Notice that the procedure takes 3 parameters. The FLAG parameter is used to tell the procedure whether we are inserting a new record or appending to an existing record. 0 means a new record and any number means we need to append to an existing record:

create or replace procedure P_TEST(P_IDX in number,P_TXT in varchar2,P_FLAG in number) as

g_clob clob;

begin

if P_FLAG=0 then
insert into test values (P_IDX, empty_clob() ) returning TXT into g_clob;
dbms_lob.write( g_clob,length(P_TXT), 1,P_TXT);
else
select TXT into g_clob from test where IDX=P_IDX for update;
dbms_lob.writeappend( g_clob, length(P_TXT), P_TXT);
end if;

end;

3- It is a good idea to test the procedure by inserting something and specially by inserting more than 32k characters in the CLOB field. Use your SQL*Plus and do this test:
Let us insert a new record with IDX=1 and the TXT contains 32000 stars:
SQL&gt; exec p_test(1, rpad('*',32000,'*'),0);

PL/SQL procedure successfully completed.

SQL&gt; commit;

Commit complete.

Le us add more stars to our first record:
SQL&gt; exec p_test(1, rpad('*',32000,'*'),1);

PL/SQL procedure successfully completed.

SQL&gt; commit;

Commit complete.

Let us add a new record with IDX=2 and TXT contains 19500 stars:
SQL&gt; exec p_test(2, rpad('*',19500,'*'),0);

PL/SQL procedure successfully completed.

SQL&gt; commit;

Commit complete.

Now let us see the length of each data in our CLOB field:
SQL&gt; select IDX, dbms_lob.getlength(TXT) from TEST order by IDX;

IDX DBMS_LOB.GETLENGTH(TXT)
---------- -----------------------
1 64000
2 19500

Test conclusion:
As you can see that the character length of the record with IDX=1 contains 64000 characters.
This means the test has "PASSED".

<b><center>The ASP script Part</center></b>

I have commented on the important parts of the code. You can analyze it by starting at the line which starts with "P A R T 1" and go through each step to understand how the script interacts with the Oracle stored procedure.


<pre id=code><font face=courier size=2 id=code>
&lt;%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%&gt;
&lt;% /*
The porpose of this ASP script is to demonstrate how to
pass a large text to an ORACLE DB to be stored in a CLOB field
(even texts which are greater than 32000 characters) by using
ASP with JAVASCRIPT.

Please note: Create a Custom Connection to your Oracle and call it "DBConnection".
And your Connection String should be something like this:
"Provider=OraOLEDB.Oracle.1;Password=1234;Persist Security Info=True;User ID=DBO;Data Source=maindb"
(Don't forget to make the changes in the connection string to reflect your Oracle stuff - Password, User ID and Data Source).
*/ %&gt;

&lt;!--#include file="../Connections/DBConnection.asp" --&gt;

&lt;%
var MM_editAction = Request.ServerVariables("SCRIPT_NAME";
if (Request.QueryString) {
MM_editAction += "?" + Server.HTMLEncode(Request.QueryString);
}

var MM_abortEdit = false;

var MM_editQuery= " ";
%&gt;

&lt;%
var Recordset1 = Server.CreateObject("ADODB.Recordset";
Recordset1.ActiveConnection = MM_DBConnection_STRING;
Recordset1.Source = "SELECT * FROM TEST ORDER BY IDX";
Recordset1.CursorType = 0;
Recordset1.CursorLocation = 2;
Recordset1.LockType = 1;
Recordset1.Open();
var Recordset1_numRows = 0;
%&gt;

&lt;%
var Repeat1__numRows = -1;
var Repeat1__index = 0;
Recordset1_numRows += Repeat1__numRows;
%&gt;

&lt;%
// ---------------------- P A R T 2 --------------------
if (String(Request("MM_insert") == "form1"
{
// STEP 5:
// This section is to select the next value of the field IDX and place it
// in the variable myIDX. (my way of creating a sequence as soon as the user
// submits the form)
var RSGetMaxID = Server.CreateObject("ADODB.Recordset";
RSGetMaxID.ActiveConnection = MM_DBConnection_STRING;
RSGetMaxID.Source = "SELECT max(IDX+1) as IDX FROM TEST";
RSGetMaxID.CursorType = 0;
RSGetMaxID.CursorLocation = 2;
RSGetMaxID.LockType = 1;
RSGetMaxID.Open();
var Recordset1_numRows = 0;
var myIDX=RSGetMaxID.Fields.Item("IDX".Value;
RSGetMaxID.Close();
if (myIDX == null) {myIDX=1}
// End of STEP 5

// STEP 6:
// Place the value returned from the RSGetMaxID recordset to a variable
// and set a FLAG variable to 0. This value will be passed to the stored
// procedure.
// When FLAG is = 0 means we need to initialize the stored procedure
// to write a new record.
// Meaning: this part of the stored procedure will be called:
// insert into test values (P_IDX, empty_clob() ) returning TXT into g_clob;
// dbms_lob.write( g_clob,length(P_TXT), 1,P_TXT);
var Command1__P_IDX=myIDX;
var Command1__P_FLAG=0;
// In the next 4 lines, do the same thing as we did in Steps 1,2,3 and 4
// (hey, I spent 10 years coding in VB... I'm new to the Scripting stuff.
// If you can do something better, please do!.
var strLength=Request.Form("sizeOfText";
var numberOfPasses=Math.ceil(strLength/32000);
var strPosition=0;
var str=""+Request.Form("TXT"+"";
// END of STEP 6

// STEP 7:
// The number of times the loop is executed is according to the value of
// the numberOfPasses.
// The first time we enter the loop, we take the first 32000 characters
// of our string with the FLAG at this time is equal to 0 and pass them to
// the stored procedure including the value of the IDX. Then we set the
// strPosition to take the next 32000 characters
// of our string and set the FLAG to be anything other than a zero.
// If the numberOfPasses is greater than 1, the loop will be called again
// and this time the strPosition is positioned to beging at the character
// number 32001 and the variable Command1__P_TXT will take next 32000 characters
// (or to the end of the string if it is less) and pass the value of the IDX including
// the new value of our FLAG which is equal to 1 at this time because we
// need to call the update section inside our stored procedure.
var i;
for(i=0; i &lt; numberOfPasses; i++)
{
Command1__P_TXT=str.substr(strPosition,32000);
var Command1 = Server.CreateObject("ADODB.Command";
Command1.ActiveConnection = MM_DBConnection_STRING;
Command1.CommandText = "P_TEST";
Command1.Parameters.Append(Command1.CreateParameter("P_IDX", 5, 1,2,Command1__P_IDX));
Command1.Parameters.Append(Command1.CreateParameter("P_TXT", 200, 3,1000000,Command1__P_TXT));
Command1.Parameters.Append(Command1.CreateParameter("P_FLAG", 200, 1,1,Command1__P_FLAG));

Command1.CommandType = 4;
Command1.CommandTimeout = 0;
Command1.Prepared = true;
Command1.Execute();
strPosition=strPosition+32000;
Command1__P_FLAG=1;
}
var MM_editRedirectUrl = "clob.asp";
if (MM_editRedirectUrl) {Response.Redirect(MM_editRedirectUrl);}
}
// End of STEP 7
%&gt;



&lt;html&gt;
&lt;head&gt;
&lt;meta http-equiv="Content-Type" content="text/html; charset=windows-1252"&gt;
&lt;title&gt;Untitled Document&lt;/title&gt;

&lt;script&gt;
// ---------------------- P A R T 1 --------------------
function doStuff()
{
// STEP: 1 - Place the content of our text in a variable
var str=document.form1.TXT.value;
// End of STEP 1

// STEP: 2 - Get the length of the variable
var strLength=str.length;
// End of STEP 2

// STEP: 3 - Place the length of the text in the hidden field "sizeOfText"
document.form1.sizeOfText.value=strLength;
// End of STEP 3

// STEP: 4 - Calculate the number of time we need to call the P_TEST
// procedure
// note: step 4 is for the knowledge porpose; you can remove the next 2
// lines if you
// like.
var numberOfPasses=Math.ceil(strLength/32000);
alert("The size of the string is: " + strLength + " and the number of times the P_TEST procedure will be called: " + numberOfPasses);
// End of STEP 4
}
&lt;/script&gt;
&lt;/head&gt;


&lt;body&gt;
&lt;table border="1"&gt;
&lt;tr&gt;
&lt;td&gt;ID&lt;/td&gt;
&lt;td&gt;TXT&lt;/td&gt;
&lt;/tr&gt;
&lt;% while ((Repeat1__numRows-- != 0) && (!Recordset1.EOF)) { %&gt;
&lt;tr&gt;
&lt;td&gt;&lt;%=(Recordset1.Fields.Item("IDX".Value)%&gt;&lt;/td&gt;
&lt;td&gt;&lt;%=(Recordset1.Fields.Item("TXT".Value)%&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;%
Repeat1__index++;
Recordset1.MoveNext();
}
%&gt;
&lt;/table&gt;

&lt;form name="form1" method="post" action="&lt;%=MM_editAction%&gt;" onSubmit="doStuff()""&gt;
&lt;p&gt;
&lt;input name="IDX" type="hidden" id="IDX"&gt;
&lt;/p&gt;
&lt;p&gt;
&lt;textarea name="TXT" cols="50" rows="10" id="TXT"&gt;Look Ma, I'm about to insert more than 4000 characters..! &lt;/textarea&gt;
&lt;/p&gt;
&lt;input type="hidden" name="sizeOfText"&gt;
&lt;p&gt;
&lt;input type="submit" name="Submit" value="Insert"&gt;
&lt;/p&gt;
&lt;input type="hidden" name="MM_insert" value="form1"&gt;
&lt;/form&gt;

&lt;/body&gt;
&lt;/html&gt;
&lt;%
Recordset1.Close();
%&gt;

</font id=code></pre id=code>

Edited by - Raafat on 26 Jul 2005 14:06:56