Inserting up to 32000 characters in an Oracle CLOB field!

Raafat Abdulfattah

Rate me:

3.00/5 (4 votes)

7 Jul 20063 min read

78.8K

232

Example of inserting up to 32000 characters in an Oracle CLOB field by using ASP code.

Download source files - 2 Kb

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>

The ASP part

<o:p>

<%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>
<% // change the connection to point to your connection%>

<%
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>

<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();
%>

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Written By

Raafat Abdulfattah

Web Developer

Saudi Arabia

This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Inserting up to 32000 characters in an Oracle CLOB field!

Introduction

License

Comments and Discussions