RE: PDF's file in oracle database

From: Ian Cary (C) <Ian.Cary_at_ordnancesurvey.co.uk>
Date: Mon, 5 Sep 2005 11:35:45 +0100
Message-ID: <4AA808D68343824E8891632BD448AE6B1BEB56@OSMAIL.ordsvy.gov.uk>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Sep 05 2005 - 05:37:48 CDT