[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Restore BLOBs to .PDF-files

Posted on 2013-12-18
8
Medium Priority
?
982 Views
Last Modified: 2014-01-03
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?
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;

Open in new window

I should end up with a directory with files named

109823.pdf
142980.pdf
220194.pdf
392380.pdf
etc.

Thanks a lot!


IVer
0
Comment
Question by:IverErling
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39726459
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.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 39726494
WHILE l_pos < l_blob_len 

Open in new window


I think that should be


 WHILE l_pos <= l_blob_len 

Open in new window


so you don't lose the last character


Simple example:   l_blob_len = 1, you'd never enter the loop

also, your block needs a final

END;

Open in new window

0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 39726511
Another suggestion,

l_location  STRING := 'c:\temp\blobs'

rather than using direct OS paths,  which require the use of utl_file_dir parameter.
Try creating directory objects first

then use the object name for the FOPEN call.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:IverErling
ID: 39727112
Thanks a lot!  I will get one of our DBAs to run it tomorrow and let you know how it goes.

IVer
0
 

Author Comment

by:IverErling
ID: 39728701
I needed to do some changes, and the script now looks like this:
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;

Open in new window

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!
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 39728932
try creating a directory object
for your path and use that

otherwise you need to have your dbas change utl_file_dir - which requires shutting down the database
0
 

Author Comment

by:IverErling
ID: 39731191
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 ;-)
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;

Open in new window

Brgds iVer
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 39731595
you need to reset your l_pos variable for each new file.


DECLARE
    l_file     UTL_FILE.file_type;
    l_buffer   RAW(32767);
    l_amount   BINARY_INTEGER := 32767;
    l_pos      NUMBER;
    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
            );
        l_pos := 1;
        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;

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Starting up a Project

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question