RE: PDF's file in oracle database
- From: "Ian Cary (C)" <Ian.Cary@xxxxxxxxxxxxxxxxxxxx>
- To: <zettira@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 5 Sep 2005 11:35:45 +0100
I've implemented 3 possible solutions to your problem in the past so hopefully
one of them should do the trick.
Cheers,
Ian
1) SQL*Loader
load data
infile *
truncate into table pdf_files
FIELDS TERMINATED BY ','
(file_name,
pdf_data LOBFILE(file_name) TERMINATED BY EOF)
begindata
full path of first pdf_file to load
.....
.....
full path of last pdf_file to load
2) PL/SQL
N.B. need to CREATE DIRECTORY IN_DIR as 'location of files'
create or replace procedure load_blob (filnam in varchar2) as
blob_loc blob;
bfile_loc bfile;
begin
insert into test_blob_data2(filename,
filedata)
values ('location of files'||filnam,
empty_blob()) returning filedata into blob_loc;
bfile_loc := bfilename('IN_DIR',filnam);
dbms_lob.fileopen(bfile_loc);
dbms_lob.loadfromfile (blob_loc,bfile_loc,dbms_lob.getlength(bfile_loc));
dbms_lob.fileclose(bfile_loc);
end;
/
show err
3) Java
This is slightly different as I had to load data into the BLOB from an FTP
stream - hence the use of JAVA.
create or replace and compile java source named "ftp_get_blob" as
import java.io.*;
import sun.net.ftp.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
public class ftp_get_blob {
public static void get(String host,
String username,
String password,
String srcfil) throws Exception {
FtpClient client = null;
try {
client = new FtpClient(host);
client.login(username,password);
client.binary();
Connection oracon =
DriverManager.getConnection("jdbc:default:connection:");
InputStream is = client.get(srcfil);
BufferedInputStream bis = new BufferedInputStream(is);
PreparedStatement empblob = oracon.prepareStatement("update
test_blob_data set filedata=empty_blob() where filename = ?");
empblob.setString(1,srcfil);
empblob.execute();
PreparedStatement updblob = oracon.prepareStatement("select filedata from
test_blob_data where filename = ? for update");
updblob.setString(1,srcfil);
updblob.execute();
OracleResultSet rset = (OracleResultSet)updblob.getResultSet();
rset.next();
BLOB blobloc=rset.getBLOB("filedata");
OutputStream os = blobloc.getBinaryOutputStream();
BufferedOutputStream bos = new BufferedOutputStream(os);
int bsize = 8196;
byte[] buffer = new byte[bsize];
int readCount;
while ((readCount = bis.read(buffer)) > 0) {
bos.write(buffer, 0, readCount);
bos.flush();
}
bos.close();
oracon.commit();
}
finally {
if (client != null) {
client.closeServer();
}
}
}
}
/
show err
create or replace procedure ftp_get_blob(host in varchar2,
username in varchar2,
password in varchar2,
sourcefile in varchar2) as
language java name 'ftp_get_blob.get(java.lang.String,
java.lang.String,
java.lang.String,
java.lang.String)';
/
show err
This email is only intended for the person to whom it is addressed and may
contain confidential information. If you have received this email in error,
please notify the sender and delete this email which must not be copied,
distributed of disclosed to any other person.
Unless stated otherwise, the contents of this email are personal to the writer
and do not represent the official view of Ordnance Survey. Nor can any contract
be formed on Ordnance Survey's behalf via email. We reserve the right to
monitor emails and attachments without prior notice.
Thank you for your cooperation.
Ordnance Survey
Romsey Road
Southampton SO16 4GU
Tel: 023 8079 2000
http://www.ordnancesurvey.co.uk
--
//www.freelists.org/webpage/oracle-l
Other related posts:
