troubleshooting Question

Using JDBC to insert an Oracle CLOB into a table, file is very large (1 MB or more)

Avatar of trooper411
trooper411 asked on
JSP
20 Comments2 Solutions7846 ViewsLast Modified:
I am attempting to insert records in a table in which one field is a clob.  I have included the inserting logic below in hopes that this will best help explain my issue.  Please note that I am trying to upload a file and insert that file directly into a table in an oracle database, so a file is not contained on the file system.  This logic does work except for large files.  When uploading files closer to 1 MB, the page seems to timeout.  Is there something

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<%@ page errorPage="JavaError.jsp" %>
<%@ page import="java.io.*,oracle.sql.*,java.text.DateFormat,oracle.jdbc.driver.*,java.util.*,com.oreilly.servlet.multipart.*, java.sql.*,bulkload.*,gsf.blk_secure.RunDosCmd" %>
<html>
<%
String userid="username";
MultipartParser mp = new MultipartParser(request,50 * 1024 * 1024);
FilePart part = (FilePart)mp.readNextPart();
if (part.isFile()) {
      String filename = (part).getFileName();
      InputStream ins = (part).getInputStream();
      BufferedReader buffer = new BufferedReader(new InputStreamReader(ins));
      String line = null;
      bulkload.OracleConnection oconn = new bulkload.OracleConnection("jdbc:oracle:thin:@db:1521:cartdev",userid,"password");
      Connection conn = oconn.getConnection();
      PreparedStatement pstmt = null;
      conn.setAutoCommit(false);
      oracle.sql.CLOB clob = null;
      ResultSet rs = null;
      java.sql.Timestamp now = new java.sql.Timestamp(System.currentTimeMillis());

// Create CLOB in the table tblFileCLOBs
      pstmt = conn.prepareStatement("insert into tblFileCLOBs values (?,?,empty_clob(),?)");
      pstmt.setString(1,filename);
      pstmt.setTimestamp(2,now);
      pstmt.setString(3,userid);
      pstmt.executeUpdate();
      conn.commit();

// Retrieve the newly created CLOB
            PreparedStatement pstmt2= conn.prepareStatement("select lobfilecontents from tblFileCLOBs where cfilename = ? and ddate_loaded = ? and cusername = ? for update");
      pstmt2.setString(1,filename);
      pstmt2.setTimestamp(2,now);
      pstmt2.setString(3,userid);
      rs = pstmt2.executeQuery();
      rs.next();
      clob = ((OracleResultSet)rs).getCLOB(1);
      
// Output the StringBuffer to the CLOB      
        
        PrintWriter prnw= new PrintWriter(clob.getCharacterOutputStream(),true);      
      while ((line=buffer.readLine()) != null) {
           out.println(line);
             prnw.println(line);
      }  
      prnw.flush();
      buffer.close();
      ins.close();
        prnw.close();
      conn.commit();
      pstmt.close();
      conn.close();
}

%>
</html>

thanks for any help you can give.  I have also post this message on the Oracle Metalink iTAR system.  I will update this post if I get a response from them.
ASKER CERTIFIED SOLUTION
paskal

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 20 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 20 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros