Blob and Clob

[fibbery]

我怎么才能将一个Blob或者Clob数据插入到数据库？
因为Blob and Clob数据类型比较特殊，在处理这两种类型的过程中还有哪些需要注意的地方，有经验的朋友请指教！

[luyuen]

DECLARE
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;

[yanyanyan]

我有个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
}

[luyuen]

dbms_lob.writeappend()
dbms_lob.write()

[fibbery]

在SQL*PLUS中有什么办法直接插入BLOB

[foolalien]

关注

[sm8848]

在SQL*PLUS中应该不能直接插入BLOB

[fibbery]

在SQL语句中是怎么插入BLOB的？由于其他语言可以实现BLOB的插入或更新，那么ORACLE一定本身提供了方法插入！

[逍遥小贼]

嗯,关注中!

[more_zou]

plsql developer 也可以

[stonegump]

要直接操作，可以用TOAD.很方便的，跟操作VARCHAR2差不多。

[fibbery]

不太懂，能不能详细点儿

[yanyanyan]

用sqlplus，我不记得从哪来的资料，有兴趣的自己动动手吧。
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.

or

SQL> insert into blobtab(index_col, blob_col)
values(1, empty_blob());

1 row created.

SQL> select count(*) from blobtab
;

COUNT(*)
----------
1

4. Run the program. The program is described below.

SQL> @rbfile
Size of input file: 154240
After loadfromfile
Size of blob: 154240

PL/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;
/