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>,
February 05, 2010, 10:03 AM
njsden
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!