找到一个如何往数据库里面写的
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;
/
select dbms_lob.getlength(blob_column) from UTL_LOB_TEST;
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;
/
select dbms_lob.getlength(blob_column) from UTL_LOB_TEST;
解决方案 »
- 求 Oracle 8i Release3 (Oracle 8.1.7) For Solaris x86 (Solaris Intel)
- 安装oracle双机rac后的共享磁盘中能运行.net程序吗?
- 如何通过时间段 知道这个时间段该员工在哪个部门
- 请教一个应该简单的更新数据库表的SQL语句!
- Oracle菜鸟提问:两条SQL查询语句, 关于SQL查询中数据表的自身连接的用法
- 大家帮忙看看,对Oracle,为什么Hibernate不能refresh
- select 和 update
- 怎样加大oracle的进程限制呀??谢了。
- 协议适配器错误?
- Oracle 9i 客户端问题
- 哪位高手帮我看一下这个过程?编译通过了,可是执行老是出错!真讨厌!
- 我上网搜索过,但找不到答案,也我已经问了很多人,这问题难道没有人能回答一下吗?
如果时9i用utl_file包将数据写入文件。
以前的版本只能用其它语言的程序了。
:(
Prior to Oracle9iR2 you will need to use Java, C, VB, some 3gl language.In 9iR2 -- PLSQL can write binary files using UTL_FILE.In 9iR1 and before, you will need to use Java or some other 3gl that has the
ability to do this.If you have my book "Expert one on one Oracle" -- i do have an example in there
in Pro*C that writes BLOBs to files in an external procedure -- so it works like
a stored procedure call.
string. query_file should contain a query that returns a single lob locator. By
default it reads from Oracle and writes to lob_file. Pass -write to read from
lob_file and write to Oracle. By default it assumes a clob, pass -blob for
binary data.// file LobUtils.java
import java.io.InputStream;
import java.io.OutputStream;
import java.io.IOException;
import java.io.FileInputStream;
import java.io.FileOutputStream;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Blob;
import oracle.sql.BLOB;
import java.sql.SQLException;import java.sql.Clob;
import oracle.sql.CLOB;import java.io.FileReader;
import java.io.BufferedReader;
import java.io.StringWriter;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.Writer;
import java.io.InputStreamReader;public class LobUtils
{
final static int bBufLen = 4 * 8192;
String query;
String connectString;
String outFile;
Connection conn; public LobUtils(String connectString, String query, String outFile) {
this.connectString = connectString;
this.query = query;
this.outFile = outFile;
this.conn = null;
} public static void main(String[] args)
throws FileNotFoundException, IOException, SQLException {
if (args.length < 5) usage();
int ii = 0;
String connString = args[ii++];
String queryFile = null;
String outputFile = null; boolean read = true;
boolean isBinary = false; for (; ii < args.length; ii++) {
if (args[ii].equals("-write"))
read = false; if (args[ii].equals("-blob"))
isBinary = true; if (args[ii].equals("-qf") && ii < args.length - 1)
queryFile = args[++ii]; if (args[ii].equals("-lf") && ii < args.length - 1)
outputFile = args[++ii]; } if (queryFile == null || outputFile == null) usage(); // all set
if (read) {
BufferedReader freader = new BufferedReader(new
FileReader(queryFile));
StringWriter swriter = new StringWriter();
int bufLen = 1024;
char[] cbuf = new char[bufLen];
int length = -1;
while ((length = freader.read(cbuf, 0, bufLen)) != -1) {
swriter.write(cbuf, 0, length);
}
freader.close();
swriter.close();
String query = swriter.toString(); LobUtils lutils = new LobUtils(connString, query, outputFile);
if (isBinary) {
Blob blob = lutils.getBlob();
long wrote = lutils.writeBlobToFile(blob);
System.out.println("Wrote " + wrote + " bytes to file " +
outputFile);
} else {
Clob clob = lutils.getClob();
long wrote = lutils.writeClobToFile(clob);
System.out.println("Wrote " + wrote + " bytes to file " +
outputFile);
}
} else {
BufferedReader freader = new BufferedReader(new
FileReader(queryFile));
StringWriter swriter = new StringWriter();
int bufLen = 1024;
char[] cbuf = new char[bufLen];
int length = -1;
while ((length = freader.read(cbuf, 0, bufLen)) != -1) {
swriter.write(cbuf, 0, length);
}
freader.close();
swriter.close();
String query = swriter.toString(); LobUtils lutils = new LobUtils(connString, query, outputFile);
Clob clob = lutils.getClob();
InputStream creader = new FileInputStream(outputFile);
long wrote = lutils.writeToOraClob(clob, creader);
System.out.println("Wrote " + wrote + " bytes from file " +
outputFile);
}
} public Clob getClob()
throws SQLException {
conn = ConnUtil.getConnection(connectString);
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);
Clob clob = null;
if (rs.next()) {
clob = rs.getClob(1);
}
return clob;
} public Blob getBlob()
throws SQLException {
conn = ConnUtil.getConnection(connectString);
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);
Blob blob = null;
if (rs.next()) {
blob = rs.getBlob(1);
}
return blob;
} public long writeClobToFile(Clob clob)
throws IOException, SQLException {
long wrote = 0;
BufferedWriter fwriter = new BufferedWriter(new FileWriter(outFile));
wrote = readFromClob(clob, fwriter);
fwriter.close();
conn.commit();
conn.close();
return wrote;
} public long writeBlobToFile(Blob blob)
throws IOException, SQLException {
long wrote = 0;
OutputStream fwriter = new FileOutputStream(outFile);
wrote = readFromBlob(blob, fwriter);
fwriter.close();
conn.commit();
conn.close();
return wrote;
} private static void usage() {
System.err.println("Usage: java LobUtils user/passwd@sid [-write]
[-blob] -qf query_file -lf lob_file");
System.exit(1);
} public static long writeToOraBlob(Blob blob, InputStream in)
throws SQLException, IOException {
BLOB oblob = (BLOB)blob;
OutputStream out = oblob.getBinaryOutputStream();
int length = -1;
long wrote = 0;
int chunkSize = oblob.getChunkSize();
byte[] buf = new byte[chunkSize];
while ((length = in.read(buf)) != -1) {
out.write(buf, 0, length);
wrote += length;
}
out.close();
return wrote;
} public long writeToOraClob(Clob clob, InputStream in)
throws SQLException, IOException {
CLOB oclob = (CLOB)clob;
OutputStream out = oclob.getAsciiOutputStream();
int length = -1;
long wrote = 0;
int chunkSize = oclob.getChunkSize();
byte[] buf = new byte[chunkSize];
while ((length = in.read(buf)) != -1) {
out.write(buf, 0, length);
wrote += length;
}
out.close();
conn.commit();
return wrote;
} public static long readFromBlob(Blob blob, OutputStream out)
throws SQLException, IOException {
InputStream in = blob.getBinaryStream();
int length = -1;
long read = 0;
byte[] buf = new byte[bBufLen];
while ((length = in.read(buf)) != -1) {
out.write(buf, 0, length);
read += length;
}
in.close();
return read;
} public static long readFromClob(Clob clob, Writer out)
throws SQLException, IOException {
BufferedReader in = new BufferedReader(new
InputStreamReader(clob.getAsciiStream()));
int length = -1;
long read = 0;
char[] buf = new char[bBufLen];
while ((length = in.read(buf, 0, bBufLen)) != -1) {
out.write(buf, 0, length);
read += length;
}
in.close();
return read;
}
}