Iver Erling Arva
asked on
Restore BLOBs to .PDF-files
I have found/written a script to create pdf files in a specified directory from a document table with a blob column. Can anyone check this and verify that it will work, or eventually help me fix it?
109823.pdf
142980.pdf
220194.pdf
392380.pdf
etc.
Thanks a lot!
IVer
DECLARE
l_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32767);
l_amount BINARY_INTEGER := 32767;
l_pos NUMBER := 1;
l_blob BLOB;
l_blob_len NUMBER;
l_doc_key NUMBER;
l_location STRING := 'c:\temp\blobs'
l_filename STRING
BEGIN
FOR l_doc_key IN (109823,142980,220194,392380) -- ...etc.)
LOOP
SELECT document
INTO l_blob
FROM tia.archive
WHERE document_key = l_doc_key;
l_blob_len := DBMS_LOB.getlength(l_blob);
-- Open the destination file.
l_file := UTL_FILE.fopen(l_location,l_filename||'.pdf','wb', 32767);
WHILE l_pos < l_blob_len LOOP
DBMS_LOB.read(l_blob, l_amount, l_pos, l_buffer);
UTL_FILE.put_raw(l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP;
-- Close the file.
UTL_FILE.fclose(l_file);
END LOOP;
I should end up with a directory with files named109823.pdf
142980.pdf
220194.pdf
392380.pdf
etc.
Thanks a lot!
IVer
On a quick scan, it seems like it should do what you want. I would just try it in a development database and see what it does.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot! I will get one of our DBAs to run it tomorrow and let you know how it goes.
IVer
IVer
ASKER
I needed to do some changes, and the script now looks like this:
ORA-29280: invalid directory path
ORA-06512 at SYS.UTL_FILE line 41
ORA-06512 at SYS,UTL_FILE line 478
ORA-06512 at line 25
I tried changing directory to several verified locations, but it seems that no matter what I put in, with or whitout the trailing backslash, I get this error. Any ideas anyone?
And the 06512-error I gather has to do with how the file is opened. This should be a .pdf file. Should I change anything?
Can someone please help?
Thanks!
DECLARE
l_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32767);
l_amount BINARY_INTEGER := 32767;
l_pos NUMBER := 1;
l_blob BLOB;
l_blob_len NUMBER;
l_doc_key NUMBER;
l_location STRING(100) := '\\server\temp\blobs\';
l_filename STRING(100);
type nt_type is table of number;
nt nt_type := nt_type (1766225,1766227);
BEGIN
FOR i IN 1..nt.count LOOP
SELECT document
INTO l_blob
FROM tia.archive
WHERE document_key = nt(i);
l_blob_len := DBMS_LOB.getlength(l_blob);
-- Open the destination file.
l_file := UTL_FILE.fopen(l_location,nt(i)||'.pdf','wb', 32767);
WHILE l_pos <= l_blob_len LOOP
DBMS_LOB.read(l_blob, l_amount, l_pos, l_buffer);
UTL_FILE.put_raw(l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP;
-- Close the file.
UTL_FILE.fclose(l_file);
END LOOP;
END;
When I try to run it I get:ORA-29280: invalid directory path
ORA-06512 at SYS.UTL_FILE line 41
ORA-06512 at SYS,UTL_FILE line 478
ORA-06512 at line 25
I tried changing directory to several verified locations, but it seems that no matter what I put in, with or whitout the trailing backslash, I get this error. Any ideas anyone?
And the 06512-error I gather has to do with how the file is opened. This should be a .pdf file. Should I change anything?
Can someone please help?
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi!
Using a directory object made the script run and create .pdf-files.
BUT:
Only the first file contains data...
So if I remove the first file number, so the second becomes the first and run the script again the second file (now the first) is created ok and the third is empty and so on.
Please help! I have hundreds of files to create ;-)
Using a directory object made the script run and create .pdf-files.
BUT:
Only the first file contains data...
So if I remove the first file number, so the second becomes the first and run the script again the second file (now the first) is created ok and the third is empty and so on.
Please help! I have hundreds of files to create ;-)
DECLARE
l_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32767);
l_amount BINARY_INTEGER := 32767;
l_pos NUMBER := 1;
l_blob BLOB;
l_blob_len NUMBER :=0;
l_doc_key NUMBER :=0;
l_location STRING(100) := 'TEMP_DIR';
l_filename STRING(100);
type nt_type is table of number;
nt nt_type := nt_type (1766225, 1766227, 1766228, 1766229, 1766230, 1766231, 1766232, 1766233, 1766234, 1766235, 1766236, 1766237, 1766238, 1766240, 1766241, 1766243, 1766710, 1766730, 1766749, 1766751, 176937); --...etc.
BEGIN
FOR i IN 1..nt.count LOOP
SELECT document
INTO l_blob
FROM tia.archive
WHERE document_key = nt(i);
l_blob_len := DBMS_LOB.getlength(l_blob);
-- Open the destination file.
l_file := UTL_FILE.fopen(l_location,nt(i)||'.pdf','wb', 32767);
WHILE l_pos <= l_blob_len LOOP
DBMS_LOB.read(l_blob, l_amount, l_pos, l_buffer);
UTL_FILE.put_raw(l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP;
-- Close the file.
UTL_FILE.fclose(l_file);
END LOOP;
END;
Brgds iVer
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.