看看oracle的文档吧:Application Developer's Guide - Large Objects (LOBs)
解决方案 »
- conn / as sysdba与OracleDBConsoleorcl的冲突
- Oracle 10g 下载
- 数据库管理
- 动态sql取数据,急!!!
- 关于oracle数据库移植到mssql的问题.
- 急!急!急!监听器找不到了
- oracle老是出现一段时间的死机现象(大家讨论都给分)
- [马上结贴]请问如何知道一个表或View的创建的时间?谢谢。
- select * from (decode('1','1',ALL_INVEST_PLAN_LINE_TAB,'2',YEAR_INVEST_PLAN_LINE_TAB)) a
- “ORA-12154: TNS: 无法处理服务名” 怎么处理?
- 诸位高人,这个问题如何解决?类似链接表。
- 请教。关于触发器的问题
里面的CODE都可以用的
Name Type Nullable Default Comments
---- ----------- -------- ------- --------
A VARCHAR2(2) Y
B VARCHAR2(2) Y
C CLOB Y
D BLOB Y SQL> insert into bb (a,b,c,d) values ('1','2','12344',hextoraw('123'));1 row insertedSQL> select a,b,to_clob(c),dbms_lob.substr(d) from bb;A B TO_CLOB(C) DBMS_LOB.SUBSTR(D)
-- -- ---------- --------------------------------------------------------------------------------
1 2 12344 0123
grant create any directory to scott;
grant create any library to scott;
create or replace directory utllobdir as 'G:\oracle';
create table bfile_tab (bfile_column BFILE);
create table utl_lob_test (blob_column BLOB);set serveroutput on然后执行下面语句就将G:\oracle目录下的Azul.jpg存入到utl_lob_test
表中的blob_column字段中了。
declare
a_blob BLOB;
a_bfile BFILE := BFILENAME('UTLLOBDIR','Azul.jpg');
begin
insert into bfile_tab values (a_bfile)
returning bfile_column into a_bfile;
insert into utl_lob_test values (empty_blob())
returning blob_column into a_blob;
dbms_lob.fileopen(a_bfile);
dbms_lob.loadfromfile(a_blob, a_bfile, dbms_lob.getlength(a_bfile));
dbms_lob.fileclose(a_bfile);
commit;
end;
/
2> (
3> testid integer,
4> testvalue clob,
5> primary key(testid)
6> );
testvalue clob,
*
ORA-22866: 默认字符集具有不同的宽度
请直接和我联系:
[email protected]
qq 3936704
谢谢了!!!!!
2> (
3> testid integer,
4> testvalue clob(10000),
5> primary key(testid)
6> );
testvalue clob(10000),
*
ORA-00907: 缺少右括号
我想你不能建立该表是应为你的oracle版本太低了
compatible=8.1.0
Oracle 8.0 does not support multibyte variable-width character sets in CLOBs.
It's a bug of Oracle Version 8.0只要按照 jiezhi(西域浪子)的作法.
请把这个贴子放入FAQ
$stmt = OCIParse($conn, "select pi_image,pi_imgtype from picture where PI_SAMPLECODE='$id'");
OCIExecute($stmt);
ocicommit($conn);
OCIFetchinto($stmt, $row, OCI_RETURN_LOBS);
OCIFreeStatement($stmt);
OCILogoff($conn);
$type=$row[1];
header("Content-type: image/$type");
echo $row[0];
1. 关于CLOB(Character Large Object)
CLOB可用于存放大文本数据,最多可存储4GB数据,在应用开发中比较常见.java提供的sql.Clob类与之对应.它提供两个方法来读取Clob的数据:
getCharacterStream() 方法返回按unicode编码的输入流(java.io.Reader对象)
getAsciiStream() 方法返回按ASCII编码的输入流(java.io.InputStream对象)
所以如果你的数据库中有可能存储中文字符的话,就要使用前一个方法.
现在举一个实际的例子,让我一步步学习如何使用CLOB.
首先,创建一个含CLOB字段的表:
create table test (id INTEGER, content clob);接下来, 我们通过JSP往这张表里插入一条记录,然后再获取显示它.
插入操作:
<%
Connection con = DriverManager.getConnection(...); //该行从略
String content = request.getParameter("content"); //大文本数据
con.setAutoCommit(false); //*
String sql = "insert into test values(1, empty_clob())";
Statement stmt = con.createStatement();
stmt.executeUpdate(sql);
con.commit(); //*
sql = "select content from test where id=1 for update";
ResultSet rs = stmt.executeQuery(sql);
if (rs.next()) {
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob(1);
clob.putString(1, content);
sql = "update test set content=? where id=" + seq;
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setClob(1, clob);
pstmt.executeUpdate();
}
%>以上需要注意的几点是:
1)clob类型的数据不能直接insert,要先通过empty_clob()方法给它分配一个locator(同理,blob的用empty_blob()函数分配locator).然后把它select出来(此时它当然没有数据,但结果集不是空的),得到一个Clob的对象,修改该对象的内容让它满足我们的需要,再通过update方法更新该行记录.2) 通过select修改含lob类型的记录时一定要锁定该行(通过for update关键字实现),否则oracle会报错.3) 刚插入的记录就select for update, 会出现"违反读取顺序"错误,解决办法是将自动提交功能置为false,即不允许自动提交,然后commit它,再select,就可以了. 这就是上面那段代码里//*两行的作用.下面,我们将刚插入的记录从数据库中读取出来并显示之:
<%
String sql = "select content from test where doc_id=1";
ResultSet rs = stmt.executeQuery(sql);
String content = "";
if (rs.next()) {
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("content");
if (clob != null){
Reader is = clob.getCharacterStream();
BufferedReader br = new BufferedReader(is);
String s = br.readLine();
while (s != null) {
content += s + "<br>";
s = br.readLine();
}
}
out.println(content);
%>
Procedure write_info (
who in varchar2,
description in varchar2,
-- file in varchar2
textfiles IN varchar2,
binaryfile IN varchar2) ;
procedure download(file_name in varchar2);
procedure html;
End;
/
show errors;Create Or Replace Package body updown as
Procedure write_info (
who in varchar2,
description in varchar2,
-- file in varchar2
textfiles IN varchar2,
binaryfile IN varchar2) as
begin
htp.htmlopen;
htp.headopen;
htp.title('File Uploaded');
htp.headclose;
htp.bodyopen;
htp.header(1,'Upload Status');
htp.print('Uploaded ' || textfiles ||' successfully');
htp.print('Uploaded ' || binaryfile ||' successfully'); update fali_doc set name = to_char(sysdate,'YYYYMMDDHHMMSS')
where name = textfiles;
update fali_doc set name = to_char(sysdate,'YYYYMMDDHHMMSSmmss')
where name = binaryfile;
commit;
-- update the file name in the table fali_doc*/
htp.bodyclose;
htp.htmlclose;
commit;
end ;
procedure download(file_name in varchar2) is
content_type varchar2(50) default null;
begin
htp.p('<html>');
htp.p('<head>');-- select mime_type into content_type from doccontent where name=file_name; htp.p('</head>');
htp.p('<body>'); wpg_docload.download_file(file_name);
htp.p('</body>');
htp.p('</html>'); end download;
procedure html is
begin
htp.p(' <html>
<head>
<title>test upload</title>
</head>
<body>
<FORM enctype="multipart/form-data" action="/ga/owa/updown.write_info" method="POST">
<p>Author’s Name:<INPUT type="text" name="who">
<p>Description:<INPUT type="text" name="description"><br>
<p>需要上载的文件1<INPUT type="file" name="textfiles"><br>
<p>需要上载的文件2<INPUT type="file" name="binaryfile"><br>
<p><INPUT type="submit">
</FORM>
</body>
</html> ');
end ;
end ;
/
show errors;