Click here to Skip to main content
11,415,390 members (79,109 online)
Click here to Skip to main content

Inserting up to 32000 characters in an Oracle CLOB field!

, 8 Jul 2006
Rate this:
Please Sign up or sign in to vote.
Example of inserting up to 32000 characters in an Oracle CLOB field by using ASP code.

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.

 

The Oracle part

 

1) Create a table and name it TBL_CLOB with 2 fields:

id_int = integer;

clob_txt =clob;

 

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

 

begin

insert into TBL_CLOB values(P_ID_INT,P_CLOB_TXT);

end;

 

3) Test inserting up to 32000. Use SQL Plus and enter some starts in the CLOB field:

SQL>  exec p_clob(1,rpad('*',32000,'*'));

 

SQL> commit;

 

SQL>  exec p_clob(2,rpad('*',19872,'*'));

 

SQL> commit;

 

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;

 

5) You should get something like this:

    ID_INT DBMS_LOB.GETLENGTH(CLOB_TXT)

---------- ----------------------------

         1                        32000

         2                        19872

 

 

 

 

 

The ASP part

 

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

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

Share

About the Author

Raafat Abdulfattah
Web Developer
Saudi Arabia Saudi Arabia
No Biography provided

Comments and Discussions

 
QuestionFile upload Pin
Elitedude200115-Aug-06 17:08
memberElitedude200115-Aug-06 17:08 
AnswerRe: File upload Pin
Raafat Abdulfattah16-Aug-06 10:04
memberRaafat Abdulfattah16-Aug-06 10:04 
GeneralRe: File upload Pin
Elitedude200116-Aug-06 10:30
memberElitedude200116-Aug-06 10:30 
GeneralRe: File upload Pin
Raafat Abdulfattah17-Aug-06 7:40
memberRaafat Abdulfattah17-Aug-06 7:40 
GeneralRe: File upload Pin
Elitedude200117-Aug-06 21:29
memberElitedude200117-Aug-06 21:29 
GeneralRe: File upload Pin
Raafat Abdulfattah19-Aug-06 2:47
memberRaafat Abdulfattah19-Aug-06 2:47 
Questionwill this work for a blob field with text? Pin
lloydk11-Jul-06 1:49
memberlloydk11-Jul-06 1:49 
AnswerRe: will this work for a blob field with text? Pin
Raafat Abdulfattah11-Jul-06 8:49
memberRaafat Abdulfattah11-Jul-06 8:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

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

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150427.4 | Last Updated 8 Jul 2006
Article Copyright 2006 by Raafat Abdulfattah
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid