高分征求解答,如何将Oracle数据库中的图片批量导出,在线等

st_snake_china 2004-10-12 03:13:33
oracle中有一个表,有2列,其中第1列为数字,第2列是图片,如何将图片保存到文件并以第1列的数字命名?记录大概有几万条
...全文
481 12 打赏 收藏 转发到动态 举报
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
boyst 2004-10-13
  • 打赏
  • 举报
回复
隨便用個什麼開發軟件,delphi最方便,聯上去不到十個語句就可以解決問題拉
yxsalj 2004-10-13
  • 打赏
  • 举报
回复
mark
st_snake_china 2004-10-12
  • 打赏
  • 举报
回复
st_snake_china 2004-10-12
  • 打赏
  • 举报
回复
顶,utl_file包怎么用阿?
st_snake_china 2004-10-12
  • 打赏
  • 举报
回复
我用的是oracle10,具体语句怎么样的?有没有example
bzszp 2004-10-12
  • 打赏
  • 举报
回复
Here's a little java client utility that I use for 8.1.7. Look at the usage
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;
}
}



bzszp 2004-10-12
  • 打赏
  • 举报
回复
dbms_lob没有这个功能
如果时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.
st_snake_china 2004-10-12
  • 打赏
  • 举报
回复
下班之前再顶顶
st_snake_china 2004-10-12
  • 打赏
  • 举报
回复
st_snake_china 2004-10-12
  • 打赏
  • 举报
回复
那帮我看看怎么讲数据库里面的图片保存为文件好不好
bzszp 2004-10-12
  • 打赏
  • 举报
回复
呵呵,上面那个是我以前测试的一个例子。
st_snake_china 2004-10-12
  • 打赏
  • 举报
回复
找到一个如何往数据库里面写的
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;

17,078

社区成员

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧