Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Putting a .pdf file into a column in an oracle table

671312Nov 19 2008 — edited Nov 19 2008
I have created a table with one column as a blob so I can put 4800 .pdf files into that column of a table. Can anyone correct the ways I am trying to do this or let me know of a better way. I have tried several ways and have not been successful. Thanks.
Here are the two ways I have tried that haven't worked.
-- the storage table for the image file
CREATE TABLE pdm (
dname VARCHAR2(30), -- directory name
sname VARCHAR2(30), -- subdirectory name
fname VARCHAR2(30), -- file name
iblob BLOB); -- image file

-- create the procedure to load the file
CREATE OR REPLACE PROCEDURE load_file (
pdname VARCHAR2,
psname VARCHAR2,
pfname VARCHAR2) IS

src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;
BEGIN
src_file := bfilename('O:\twilliams\DD_promotion\cards\', pfname);

-- insert a NULL record to lock
INSERT INTO pdm
(dname, sname, fname, iblob)
VALUES
(pdname, psname, pfname, EMPTY_BLOB())
RETURNING iblob INTO dst_file;

-- lock record
SELECT iblob
INTO dst_file
FROM pdm
WHERE dname = pdname
AND sname = psname
AND fname = pfname
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 pdm
SET iblob = dst_file
WHERE dname = pdname
AND sname = psname
AND fname = pfname;

-- close file
dbms_lob.fileclose(src_file);
END load_file;
/

This one I get an error message on this statements:(dbms_lob.LOADBLOBFROMFILE(blob_loc,bfile_loc,dbms_l ob.lobmaxsize,bfile_offset,
blob_offset) ; )

DECLARE
bfile_loc BFILE;
blob_loc BLOB;
bfile_offset NUMBER := 1;
blob_offset NUMBER := 1;
tot_len INTEGER;
BEGIN
/*-- First INSERT a row with an empty blob */
INSERT INTO blob_tab VALUES (5, EMPTY_BLOB());
COMMIT;
/*-- SELECT the blob locator FOR UPDATE */
SELECT blob_data INTO blob_loc FROM blob_tab
WHERE id = 5 FOR UPDATE;
/*- Obtain the BFILE locator */
bfile_loc := bfilename('O:\twilliams\DD_promotion\cards\','00EAL.pdf');
/*-- Open the input BFILE */
dbms_lob.fileopen(bfile_loc, dbms_lob.file_readonly);
/*-- Open the BLOB */
dbms_lob.OPEN(blob_loc, dbms_lob.lob_readwrite);
/*-- Populate the blob with the whole bfile data */
dbms_lob.LOADBLOBFROMFILE(blob_loc,bfile_loc,dbms_l ob.lobmaxsize,bfile_offset,
blob_offset) ;
/*-- Obtain length of the populated BLOB */
tot_len := DBMS_LOB.GETLENGTH(blob_loc);
/*-- Close the BLOB */
dbms_lob.close(blob_loc);
/*-- Close the BFILE */
dbms_lob.fileclose(bfile_loc);
COMMIT;
/*-- Display the length of the BLOB */
DBMS_OUTPUT.PUT_LINE('The length of the BLOB after population is: '||
TO_CHAR(tot_len));
END ;
/
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 17 2008
Added on Nov 19 2008
5 comments
2,293 views