在oracle中怎么对大型字符串数据进行插入，比如long、clob等等，在线等急用

Question

给你个写clob的例子
 create table bfile_tab (bfile_column BFILE);
create table utl_lob_test (blob_column BLOB);
create or replace directory utllobdir as 'e:temp';declare
  2     a_blob  BLOB;
  3     a_bfile BFILE := BFILENAME('UTLLOBDIR'
  4  ,'test.doc');
  5  begin
  6  
  6  insert into bfile_tab values (a_bfile)
  7       returning bfile_column into a_bfile;
  8  
  8  insert into utl_lob_test values (empty_blob())
  9       returning blob_column into a_blob;
 10     dbms_lob.fileopen(a_bfile);
 11     dbms_lob.loadfromfile(a_blob, a_bfile, dbms_lob.getlength(a_bfile));
 12     dbms_lob.fileclose(a_bfile);
 13     commit;
 14  end;
 15  /select dbms_lob.getlength(blob_column) from UTL_LOB_TEST; 

Answer 1

给你一个终极答案。
http://www.oracle.com/technology/sample_code/tech/java/sqlj_jdbc/files/advanced/LOBSample/Readme.html

Answer 2

private void setStringToClob(String clobColumn, String clobString,
            ResultSet rs) throws SQLException, IOException {        CLOB clob = ((OracleResultSet) rs).getCLOB(clobColumn);
        if (clobString == null) {
            clobString = "";
        }
        
        Writer outstream = clob.getCharacterOutputStream();        try {
            outstream.write(clobString);
        } catch (IOException ex) {
            throw ex;
        } finally {
            if (outstream != null) {
                try {
                    outstream.close();
                } catch (IOException ex) {
                    throw ex;
                }
            }
        }
    }

Answer 3

<%@page language="java" contentType="text/html;charset=gb2312" import="java.sql.*,java.io.*,javax.sql.*,javax.naming.*"%>
<% try{
String name=request.getParameter("name").trim();
String pass=request.getParameter("pass").trim();
String strContent=request.getParameter("content").trim();
//strContent=new String(strContent.getBytes("ISO8859-1"),"gb2312");Connection conn=null;
//Statement stmt=null;
ResultSet rs=null;
Context initCxt=new InitialContext();
if(initCxt==null){ throw new Exception("没有匹配的环境变量");}
Context cxt=(Context)initCxt.lookup("java:comp/env");
//获取连接池对象
Object obj=(Object)cxt.lookup("jdbc/gk66orac");
//类型转换
javax.sql.DataSource ds=(javax.sql.DataSource)obj;
if(ds==null){throw new Exception("没有匹配的数据库");}
conn=ds.getConnection();
  PreparedStatement pstmt = conn.prepareStatement("INSERT INTO test(name,content) VALUES(?,EMPTY_CLOB())");
pstmt.setString(1, name); 
  pstmt.execute(); 
  pstmt.close(); 
Statement stmt= conn.createStatement();
String sql="SELECT content FROM test WHERE name='"+name+"' FOR UPDATE";
out.println(sql);  
   ResultSet lobDetails = stmt.executeQuery(sql);
  if( lobDetails.next() ) {        
   Clob sugBookClob = lobDetails.getClob(1);      
Writer clobWriter =((oracle.sql.CLOBsugBookClob).getCharacterOutputStream();
 File sugbookFile = new File(strContent);
        FileReader sugFileReader = new FileReader(sugbookFile);
        char[] cbuffer = new char[10*1024];
   int nread = 0;
while( (nread= sugFileReader.read(cbuffer)) != -1 )
clobWriter.write( cbuffer,0,nread);
 }
if(lobDetails!=null){lobDetails.close();lobDetails=null;}
if(rs!=null){rs.close();rs=null;}
if(stmt!=null){stmt.close();stmt=null;}
if(conn!=null){conn.close();conn=null;}}catch(Exception ex){out.println(ex.getMessage());}
%>

Answer 4

在运行以上语句，出现如下错误：ORA-01002: 读取违反顺序
请问是怎么回事？

Answer 5

<%@page language="java" contentType="text/html;charset=gb2312" import="java.util.*,java.sql.*,java.io.*,java.sql.Clob,oracle.jdbc.pool.OracleDataSource,javax.sql.*,javax.naming.*"%>
<% try{
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
Context initCxt=new InitialContext();
if(initCxt==null){ throw new Exception("没有匹配的环境变量");}
Context cxt=(Context)initCxt.lookup("java:comp/env");
//获取连接池对象
Object obj=(Object)cxt.lookup("jdbc/gk66orac");
//类型转换
javax.sql.DataSource ds=(javax.sql.DataSource)obj;
if(ds==null){throw new Exception("没有匹配的数据库");}
conn=ds.getConnection();
stmt=conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,java.sql.ResultSet.CONCUR_READ_ONLY);
String sql="select * from test where id='1'";
rs=stmt.executeQuery(sql);
String strOut=null;
java.sql.Clob clob1= null; 
if(rs.next())
clob1=(Clob)rs.getClob(2);
if(clob1==null){
out.println("clob为空");
}else{
Reader reader=clob1.getCharacterStream();
StringBuffer strBuf=new StringBuffer();
int len=0;
char[] b=new char[1024];
while((len=reader.read(b))!=-1){
 strBuf.append(b,0,len);
 }
strOut=new String(strBuf);
}
if(rs!=null){rs.close();rs=null;}
if(stmt!=null){stmt.close();stmt=null;}
if(conn!=null){conn.close();conn=null;}}catch(Exception ex){out.println(ex.getMessage());}
%>
在运行以上页面时出现如下错误，请高手给指点一下
javax.servlet.ServletException: oracle.jdbc.driver.ScrollableResultSet.getClob(I)Ljava/sql/Clob;
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:867)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:800)
org.apache.jsp.test_005fview_jsp._jspService(test_005fview_jsp.java:106)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:133)
javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:311)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:301)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:248)
javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
root cause java.lang.AbstractMethodError: oracle.jdbc.driver.ScrollableResultSet.getClob(I)Ljava/sql/Clob;
org.apache.commons.dbcp.DelegatingResultSet.getClob(DelegatingResultSet.java:314)
org.apache.jsp.test_005fview_jsp._jspService(test_005fview_jsp.java:68)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:133)
javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:311)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:301)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:248)
javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
note The full stack trace of the root cause is available in the Tomcat logs.