我有个JDBC使用CLOB的例子:插入CLOB:String content="内容";
try{
java.sql.PreparedStatement pstmt = null;
ResultSet rs = null;
String query = "";
String key =""; Statement stmt = dbConn.createStatement ();
rs=stmt.executeQuery("SELECT MAX(CNAME) FROM T_CLOB");
if(rs.next()){
key = rs.getString(1);
if( key==null )
key="001";
else{
int intKey=Integer.valueOf(key).intValue();
intKey+=1;
key=Integer.toString(intKey);
}
}
dbConn.setAutoCommit(false);
query = "INSERT INTO T_CLOB(CNAME,OBJ) VALUES(?,empty_clob())";
java.sql.PreparedStatement Pstmt = dbConn.prepareStatement(query);
Pstmt.setString(1,key);
Pstmt.executeUpdate();
Pstmt = null;
query = "SELECT OBJ FROM T_CLOB WHERE CNAME =? FOR UPDATE";
Pstmt = dbConn.prepareStatement(query);
Pstmt.setString(1,key);
rs= Pstmt.executeQuery();
oracle.sql.CLOB clobtt = null;
if(rs.next()){
clobtt = (oracle.sql.CLOB)rs.getClob(1);
}
Writer wr = clobtt.getCharacterOutputStream();
wr.write(content);
wr.flush();
wr.close();
rs.close();
dbConn.commit();
}
catch(Exception e){
//exception handle
}
读CLOB:
try{
PreparedStatement ps = dbConn.prepareStatement("SELECT OBJ FROM T_CLOB WHERE CNAME=?");
ps.setString(1,"5");
ResultSet result = ps.executeQuery();
if(result.next()){
oracle.jdbc.driver.OracleResultSet ors =(oracle.jdbc.driver.OracleResultSet)result;
oracle.sql.CLOB clobtmp = (oracle.sql.CLOB) ors.getClob(1);
ors.close(); if(clobtmp==null || clobtmp.length()==0){
errTmp="CLOB IS NULL";
}
else{
buffer=clobtmp.getSubString((long)1,(int)clobtmp.length()); //buffer即为CLOB所存内容。
}
}
else{
errTmp="NO DATA!";
}
ps.close();
}
catch(Exception e){
//exception handle
}
try{
java.sql.PreparedStatement pstmt = null;
ResultSet rs = null;
String query = "";
String key =""; Statement stmt = dbConn.createStatement ();
rs=stmt.executeQuery("SELECT MAX(CNAME) FROM T_CLOB");
if(rs.next()){
key = rs.getString(1);
if( key==null )
key="001";
else{
int intKey=Integer.valueOf(key).intValue();
intKey+=1;
key=Integer.toString(intKey);
}
}
dbConn.setAutoCommit(false);
query = "INSERT INTO T_CLOB(CNAME,OBJ) VALUES(?,empty_clob())";
java.sql.PreparedStatement Pstmt = dbConn.prepareStatement(query);
Pstmt.setString(1,key);
Pstmt.executeUpdate();
Pstmt = null;
query = "SELECT OBJ FROM T_CLOB WHERE CNAME =? FOR UPDATE";
Pstmt = dbConn.prepareStatement(query);
Pstmt.setString(1,key);
rs= Pstmt.executeQuery();
oracle.sql.CLOB clobtt = null;
if(rs.next()){
clobtt = (oracle.sql.CLOB)rs.getClob(1);
}
Writer wr = clobtt.getCharacterOutputStream();
wr.write(content);
wr.flush();
wr.close();
rs.close();
dbConn.commit();
}
catch(Exception e){
//exception handle
}
读CLOB:
try{
PreparedStatement ps = dbConn.prepareStatement("SELECT OBJ FROM T_CLOB WHERE CNAME=?");
ps.setString(1,"5");
ResultSet result = ps.executeQuery();
if(result.next()){
oracle.jdbc.driver.OracleResultSet ors =(oracle.jdbc.driver.OracleResultSet)result;
oracle.sql.CLOB clobtmp = (oracle.sql.CLOB) ors.getClob(1);
ors.close(); if(clobtmp==null || clobtmp.length()==0){
errTmp="CLOB IS NULL";
}
else{
buffer=clobtmp.getSubString((long)1,(int)clobtmp.length()); //buffer即为CLOB所存内容。
}
}
else{
errTmp="NO DATA!";
}
ps.close();
}
catch(Exception e){
//exception handle
}
解决方案 »
- 求一过滤sql 语句
- plsql developer show问题
- Oracle 如何快速查询 表里的数据? 急~~~~
- [救急,麻烦各位大侠仗义出手,非常感谢!]请问一下oracle 10g oem的登陆问题。
- WIN2003安装完ORACLE 9i以后,必须手工设置环境变量吗?
- 取指定数据行?
- 大哥大姐用ultraedit 编辑pro c的 .pc 文檔,高亮显示错误问题; drabit(square) 一定要来阿
- 卸载ORACLE后,如何在重新安装ORACLE!!急!急!!急!!!
- 在oracle里,怎么设自增的列呀!---急,急!
- plsql连接Oracle中文乱码
- 能不能在存储过程中创建文件目录和删除文件目录和文件?
- 如何将表导出到一个txt文件中,以及从txt文件将其倒入到相应的表中?
dbms_lob.write()
stud_hist CLOB;
BEGIN
--Fetch the LOB locator
SELECT student_history INTO stud_hist
FROM student
WHERE student_id = 1003
FOR UPDATE;
--The LOB is empty to start with. Append 10 characters
--onto the end.
DBMS_LOB.WRITEAPPEND (stud_hist, 10, ‘1234567890’);
--Do the same thing twice more, giving
--us a total of 30 characters.
DBMS_LOB.WRITEAPPEND (stud_hist, 10, ‘1234567890’);
DBMS_LOB.WRITEAPPEND (stud_hist, 10, ‘1234567890’);
END;
The following is the only method available to read the contents of a binary
file into a database BLOB using PL/SQL:1. Create a database directory object to point to the operating system
directory which contains the file (or files) that you wish to read
into a database blob.SQL> create or replace directory carldir as '/tmp';Directory created.2. Create a table that contains a blob column (If one does not already
exist).SQL> create table blobtab(index_col integer, blob_col blob);Table created.3. Insert something into the blob column.SQL> insert into blobtab(index_col, blob_col)
values(1, utl_raw.cast_to_raw('this is only a test'));1 row created.orSQL> insert into blobtab(index_col, blob_col)
values(1, empty_blob());1 row created.SQL> select count(*) from blobtab
;COUNT(*)
----------
14. Run the program. The program is described below.SQL> @rbfile
Size of input file: 154240
After loadfromfile
Size of blob: 154240PL/SQL procedure successfully completed.------rbfile.sql:
--
-- A program that demonstrates that an operating system file containing
-- raw data may be read into a database BLOB column.
--
declare
ablob blob;
abfile bfile := bfilename('CARLDIR', 'cc'); -- Get a pointer to the file.
amount integer;
asize integer;
begin
--
-- Note: Above, the blobtab table was initialized with one entry. This
-- is needed because the PL/SQL BLOB locator (ablob) must point to a
-- specific EXISTING NON-NULL database BLOB. Also, the select statement
-- may only return one entry. If more than one row is returned
-- (more than one row with index_col set equal to 1), then the script
-- will fail.
--
select blob_col into ablob from blobtab where index_col = 1;
dbms_lob.fileopen(abfile);
asize := dbms_lob.getlength(abfile);
dbms_output.put_line('Size of input file: ' || asize);
dbms_lob.loadfromfile(ablob, abfile, asize);
dbms_output.put_line('After loadfromfile');
asize := dbms_lob.getlength(ablob);
dbms_output.put_line('Size of blob: ' || asize);
exception
when others then
dbms_output.put_line('An exception occurred');
dbms_output.put_line(sqlcode || sqlerrm);
end;
/