Has anyone a working example of how to insert an Excel document as a BLOB in an Oracle table?

This message has been edited. Last edited by: Kerry,

Would this question be better suited to an Oracle forum ?

WF does not handle BLOBs very well.

Iway could probably do it.

Based on this simple table

 
CREATE TABLE blobtable_BLOB
(
  ATTACHMENT_ID    NUMBER,
  ATTACHMENT_NAME  VARCHAR2(255 BYTE),
  ATTACHMENT       BLOB
);

 

Create a package in oracle

 
CREATE OR REPLACE PACKAGE          blobload AS
   PROCEDURE blob_BLOB_LOAD(
PATTACHMENT_ID IN NUMBER,
PATTACHMENT_NAME IN VARCHAR2);
   END;
/

CREATE OR REPLACE PACKAGE BODY          blobload AS
   PROCEDURE blob_BLOB_LOAD(
PATTACHMENT_ID NUMBER,
PATTACHMENT_NAME VARCHAR2) IS

src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;
BEGIN
src_file := bfilename('blobUPLOAD', PATTACHMENT_NAME);

-- insert a NULL record to lock
 INSERT INTO blobtable_BLOB
 (ATTACHMENT_ID,ATTACHMENT)
 VALUES (PATTACHMENT_ID,EMPTY_BLOB())
 RETURNING ATTACHMENT INTO dst_file;

-- UPDATE blobtable_BLOB
-- SET ATTACHMENT  = EMPTY_BLOB()
-- WHERE ATTACHMENT_ID = PATTACHMENT_ID 
-- RETURNING ATTACHMENT INTO dst_file;

-- lock record
SELECT ATTACHMENT
INTO dst_file
FROM blobtable_BLOB
WHERE ATTACHMENT_ID = PATTACHMENT_ID
FOR UPDATE;

-- open the file
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);

-- determine length
lgh_file := dbms_lob.getlength(src_file);

-- read the file
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);

-- update the blob field
UPDATE blobtable_BLOB
SET ATTACHMENT = dst_file , ATTACHMENT_NAME  =  PATTACHMENT_NAME
WHERE ATTACHMENT_ID = PATTACHMENT_ID;
-- close file
dbms_lob.fileclose(src_file);
END blob_BLOB_LOAD;
   END blobload;
/

 

To load the image, excel or anything via WF

 
SQL SQLORA 
EX oracleuserid.blobload.blob_BLOB_LOAD('index in table blobtable_BLOB,' ','fully qualified filename); 
END 
-RUN  

This message has been edited. Last edited by: <JG>,

Bill, for JG's solution to work (nice one by the way) keep in mind that the Excel (or whatever binary) file must be accessible to the Oracle server (either locally or through sharing services such as NFS, Samba, ...).

Since you want to initiate the load from WebFOCUS I am assuming that you probably will have a WebFOCUS process creating a HOLD/SAVE file in Excel format to later be uploaded into Oracle so, unless both the WF Reporting Server and the Oracle Server "live" in the same box you'll have to make additional arrangements so your HOLD file gets transferred to some location Oracle can "see". One such thing could be, for example, scheduling your report through Report Caster and choosing FTP to distribute the final file. Just an idea!