Introduction
This example shows how to insert up to 32000 characters in an Oracle CLOB field using ASP code. I’ll be writing another article to demonstrate the insertion of more than that later on. The example should work with Oracle 8,9 and 10g.
Use this example if up to 32000 is good enough for you because storing up to 4gig is more complex.
<o:p>
The Oracle part<o:p>
<o:p>
1) Create a table and name it TBL_CLOB with 2 fields:
id_int = integer;
clob_txt =clob;
<o:p>
2) Create a stored procedure and name it P_CLOB with the following code:
(P_ID_INT in int,P_CLOB_TXT in varchar2)as
<o:p>
begin
insert into TBL_CLOB values(P_ID_INT,P_CLOB_TXT);
end;
<o:p>
3) Test inserting up to 32000. Use SQL Plus and enter some starts in the CLOB field:
SQL> exec p_clob(1,rpad('*',32000,'*'));
<o:p>
SQL> commit;
<o:p>
SQL> exec p_clob(2,rpad('*',19872,'*'));
<o:p>
SQL> commit;
<o:p>
4) Retrieve the 2 records you just inserted and count the number of characters in the CLOB fields:
SQL> select id_int,dbms_lob.getlength(clob_txt) from tbl_clob;
<o:p>
5) You should get something like this:
ID_INT DBMS_LOB.GETLENGTH(CLOB_TXT)
---------- ----------------------------
1 32000
2 19872 <o:p>
<o:p>
<o:p>
<o:p>
<o:p>
The ASP part
<o:p>
<%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>
<% // change the connection to point to your connection%>
<!--#include file="../Connections/myCon.asp" -->
<%
var MM_editAction = Request.ServerVariables("SCRIPT_NAME");
if (Request.QueryString) {
MM_editAction += "?" + Server.HTMLEncode(Request.QueryString);
}
var MM_abortEdit = false;
var MM_editQuery= " ";
%>
<%
var rsTBL_CLOB = Server.CreateObject("ADODB.Recordset");
rsTBL_CLOB.ActiveConnection = MM_myCon_STRING;
rsTBL_CLOB.Source = "SELECT * FROM TBL_CLOB ORDER BY ID_INT DESC";
rsTBL_CLOB.CursorType = 0;
rsTBL_CLOB.CursorLocation = 2;
rsTBL_CLOB.LockType = 1;
rsTBL_CLOB.Open();
var rsTBL_CLOB_numRows = 0;
%>
<%
if (String(Request("MM_insert")) == "form1") {
// section 1: select the last value of the ID_INT and add 1 to it. (sequence)
var RSGetMaxID = Server.CreateObject("ADODB.Recordset");
RSGetMaxID.ActiveConnection = MM_myCon_STRING;
RSGetMaxID.Source = "SELECT max(ID_INT+1) as ID_INT FROM TBL_CLOB";
RSGetMaxID.CursorType = 0;
RSGetMaxID.CursorLocation = 2;
RSGetMaxID.LockType = 1;
RSGetMaxID.Open();
var rsTBL_CLOB_numRows = 0;
var myID_INT=RSGetMaxID.Fields.Item("ID_INT").Value;
RSGetMaxID.Close();
// if the ID_INT is null, make it = 1
if (myID_INT == null) {myID_INT=1}
// End of section 1
// SECTION 2
// Uses a COMMAND to send the form values to the P_CLOB Stored Procedure
var Command1__P_ID_INT=myID_INT; // the next vaue of the ID_INT
var Command1__P_CLOB_TXT=Request.Form("CLOB_TXT"); // the text entered on the page
var Command1 = Server.CreateObject("ADODB.Command");
Command1.ActiveConnection = MM_myCon_STRING;
Command1.CommandText = "P_CLOB";
Command1.Parameters.Append(Command1.CreateParameter("P_ID_INT", 5, 1,2,Command1__P_ID_INT));
Command1.Parameters.Append(Command1.CreateParameter("P_CLOB_TXT", 200, 1,40000,Command1__P_CLOB_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 of section 2
%>
<%
var Repeat1__numRows = -1;
var Repeat1__index = 0;
rsTBL_CLOB_numRows += Repeat1__numRows;
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Clob: up to 32000 chars</title>
</head>
<body>
<table border="1">
<tr>
<td>ID</td>
<td>CLOB_TXT</td>
</tr>
<% while ((Repeat1__numRows-- != 0) && (!rsTBL_CLOB.EOF)) { %>
<tr>
<td valign="top"><%=(rsTBL_CLOB.Fields.Item("ID_INT").Value)%></td>
<td><%=(rsTBL_CLOB.Fields.Item("CLOB_TXT").Value)%></td>
</tr>
<%
Repeat1__index++;
rsTBL_CLOB.MoveNext();
}
%>
</table>
<form name="form1" method="post" action="<%=MM_editAction%>">
<p>
<input name="ID_INT" type="hidden" id="ID_INT">
</p>
<p>
<textarea name="CLOB_TXT" cols="100" rows="5" id="CLOB_TXT">Look Ma, I'm about to insert more than 4000 Characters ...</textarea>
</p>
<p>
<input type="submit" name="Submit" value="Insert">
</p>
<input type="hidden" name="MM_insert" value="form1">
</form>
</body>
</html>
<%
rsTBL_CLOB.Close();
%>
