import java.sql.*;
import java.io.*;
import java.util.*;
import oracle.jdbc.driver.*;import oracle.sql.*;public class ClobExample {
public ClobExample() {
} public static void main(String[] args) throws Exception {
// Register the Oracle JDBC driver
try {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
// Connect to the database
// Change the connect string after @ sign to match yours. same thing for username, // password
Connection conn =
DriverManager.getConnection("jdbc:oracle:thin:@database ip:1521:oracle",
user, pwd);
System.out.println("Connected");
conn.setAutoCommit(false);
CLOB clob = null;// Create a Statement
Statement stmt = conn.createStatement();// Drop the table if it exists
try {
stmt.execute("drop table ClobTable1");
System.out.println("Table droped ...");
}
catch (SQLException e) {
System.out.println("Table does not exist");
}// Create the table
stmt.execute("create table ClobTable1 (name varchar2(20), data CLOB)");
System.out.println("Table created ...");// create a clob entry in the table
stmt.execute("insert into ClobTable1 values ('test', empty_clob())");
stmt.execute("commit");
String cmd = "select * from ClobTable1 for update";
ResultSet rset = stmt.executeQuery(cmd);
while (rset.next()) {
clob = ( (OracleResultSet) rset).getCLOB(2); }
readFromFile(clob);
stmt.execute("commit");
//confirm whether the text file was inserted by returning the clob //length rset = stmt.executeQuery("select * from ClobTable1 where NAME='test'");
// Get the first row
if (rset.next()) {
clob = ( (OracleResultSet) rset).getCLOB(2);
String st = rset.getString(2);
if (clob != null) {
System.out.println("clob length = " + clob.length());
}
}
writeToFile(clob);
}
catch (SQLException sqle) {
System.out.println("SQL Exception occured: " + sqle.getMessage());
sqle.printStackTrace();
}
catch (FileNotFoundException e) {
System.out.println("File Not Found");
}
catch (IOException ioe) {
System.out.println("IO Exception" + ioe.getMessage());
}
} static void readFromFile(CLOB clob) throws Exception {
File file = new File("G:\\code2.htm"); // replace with your actual file
FileInputStream in = new FileInputStream(file);
//OutputStream out = clob.getAsciiOutputStream();
OutputStream out = clob.getAsciiOutputStream();
int chunk = clob.getChunkSize();
System.out.print("The chunk size is " + chunk);
byte[] buffer = new byte[1024];
int length; while ( (length = in.read(buffer)) != -1) {
out.write(buffer, 0, length);
}
in.close();
out.close();
} static void writeToFile(CLOB clob) throws Exception {
int chunk = clob.getChunkSize();
byte[] buffer = new byte[1024];
int length; FileOutputStream outFile = null;
outFile = new FileOutputStream("G:\\test.htm");
InputStream instream = clob.getAsciiStream();// Fetch data
while ( (length = instream.read(buffer)) != -1) {
outFile.write(buffer, 0, length);
}// Close input and output streams
instream.close();
outFile.close();
}}
解决方案 »
- pl/sql 实现base64解码
- 求sql
- 如何取得正确的字符串
- 根据select出来的月数据,怎么再得到年累计数据?
- oracle sql 海量效率问题,高手进,100分
- 数据库创建用户出错!
- 如何完全删除一个库(不是简单的把库从目录树中删除),包括硬盘上的库文件,还有服务中的项目也要删除。
- 一个用户的default tablespace 和 temporary tablespace 有什么区别啊
- 急,用户及它的所有对象被删除,能否还能恢复
- 在win2000下装Oracle8i的问题
- 请教在oracle中如何得到客户提交的事务的信息?
- 关于TYPE IS TABLE OF 的问题。高手请教。
///////////////////////////////////////////////////
import java.io.*;import oracle.jdbc.*;
import oracle.jdbc.driver.*;
import java.sql.*;public class OraClob {
public static void main(String[] args) {
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn=DriverManager.getConnection("jdbc:oracle:oci8:@sid", "username", "password");
conn.setAutoCommit(false);
String sql = "insert into clobdata values(2, empty_clob())";
//Statement stmt = conn.createStatement ();
PreparedStatement stmt = conn.prepareStatement(sql);
//Statement stmt = conn.createStatement();
//stmt.executeUpdate(sql);
stmt.execute(sql);
//conn.commit();
String sql2="select content from clobdata where id=2 for update";
ResultSet rs = stmt.executeQuery(sql2);
System.out.println("inserting...");
if(rs.next()){
File originVR=new File("/home/oracle/pro.txt");
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("content");
/*先建立一个由文件而来的输入流,然后把它一行一行的读入到clob中
*
* */
BufferedReader in = new BufferedReader(new InputStreamReader(
new BufferedInputStream(
new FileInputStream(originVR))));
/*
* 把输入流中的内容转入clob的输出流
* */
StringBuffer strBuf=new StringBuffer();
//String bc=null;
System.out.println("swapping the chars from file to clob");
//int i=0;
long =1;
String tmp="";
while((tmp = in.readLine())!=null){
// write this line of chars into a string buffer
System.out.println(tmp);
//clob.putString(,tmp);
clob.putString(,tmp);
+=tmp.length();
}
// clob.putString(1,"hello world this si the first test of the jdbc of clob");
// clob.pubString(1,in.readLine());
// clob.putString(1,in.readLine());
// put the stringBuffer's contents chars to the clob object
//clob.putString(1,strBuf.toString());
System.out.println("finished the swaping work!");
sql = "update clobdata set content=? where id=2";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setClob(1, clob);
pstmt.executeUpdate();
System.out.println("finished the insert work from the file to clob in oracle .");
System.out.println("the clob area is longth :"+clob.length());
in.close();
conn.close();
//outClob.close();
}
}catch(SQLException e){
System.out.println("mistake when operating the database!");
System.out.println(e.getMessage());
}
catch(FileNotFoundException e){
System.out.println("the file can't be find!");
System.out.println(e.getMessage());
}catch(ClassNotFoundException e){
System.out.println("the oracle jdbc is not found!");
System.out.println(e.getMessage());
}catch (IOException e){
System.out.println("the file stream can't cractly excute!");
}catch (Exception e){
System.out.println("other exception happens!");
System.out.println(e.getMessage());
}
}
}
///////////////////////////////////////////////////
http://www.csdn.net/develop/Read_Article.asp?Id=16007
http://www.csdn.net/develop/Read_Article.asp?Id=20873
我在程序运行的过程中报异常如下:
java.io.IOException: ORA-22990: LOB 定位器无法扩展事务处理
ORA-06512: 在"SYS.DBMS_LOB", line 708
ORA-06512: 在line 1
这是在读取CLOB导出成文件的时候报错,且只有含有中文字符时报错,如果导出的文件不含有中文字符,就可以正常运行,请问是什么原因?
public void clobRead(String outfile) throws Exception {
ora_stmt = ora_conn.createStatement ();
/* 设定不自动提交 */
boolean defaultCommit = ora_conn.getAutoCommit();
ora_conn.setAutoCommit(false);
try {
/* 查询CLOB对象 */
ResultSet rs = ora_stmt.executeQuery("SELECT * FROM TEST_CLOB WHERE ID='111'");
while (rs.next()) {
/* 获取CLOB对象 */
oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob("CLOBCOL");
/* 以字符形式输出 */
BufferedReader in = new BufferedReader(clob.getCharacterStream());
BufferedWriter out = new BufferedWriter(new FileWriter(outfile));
int c;
while ( (c = in.read()) != -1) {
out.write(c);
}
out.close();
in.close();
}
System.out.println("导出成功");
}
catch (Exception ex) {
ora_conn.rollback();
throw ex;
}
/* 恢复原提交状态 */
ora_conn.setAutoCommit(defaultCommit);
}