3 Replies Latest reply on Nov 5, 2010 4:03 AM by benton

    Loading multiple blobs with PLSQL - Thank You

    benton
      Last week I posted a question (Re: Loading multiple blobs with PLSQL in desperation asking if any body had a good tutorial to load multiple blobs. One response back was google it, I deserved that Mustafa. I did a search and eventually found some useful information by googling the word 'DBMS_LOB' Thank You Billy for the hint. From this I was able to write an anonymous block that accomplished what I required, but then I was asked to put it into a package, I haven’t written a package in a while and must admit to being very rusty on this, but with the kind help of a number of you, Toon and Saubhik (Correct use of Type declaration? I have been able to get this thing up and running.

      By way of giving something back instead of asking questions all the time, I have included a script below that shows how I approached the bulk upload of records with a BLOB column using an external csv file.

      Preparation
      1. Place a number of documents (I used pdf's) into the directory specified by ULOAD_DIR.

      2. Create a csv file and enter the following text. This can be done by copying the following records into a notepad file and then naming it EXTERNAL_DATA.csv.
      sketch_a.pdf,application/pdf
      sketch_b.pdf,application/pdf
      sketch_c.pdf,application/pdf
      sketch_d.pdf,application/pdf
      sketch_e.pdf,application/pdf
      sketch_f.pdf,application/pdf
      sketch_g.pdf,application/pdf
      sketch_h.pdf,application/pdf
      sketch_i.pdf,application/pdf
      3. Run the following script to create objects:
      CONNECT SYSTEM/password@XE
      
      CREATE DIRECTORY UPLOAD_DIR AS 'C:\temp';
      
      GRANT READ ON DIRECTORY UPLOAD_DIR TO SCHEMA;
      GRANT WRITE ON DIRECTORY UPLOAD_DIR TO SCHEMA;
      
      
      CONNECT user/password@XE
      
      -- CREATE TABLES
      
      CREATE TABLE TARGET_TABLE
      ( FILENAME VARCHAR2(4000)
      , MIME_TYPE VARCHAR2(4000)
      , BLOB_CONTENT   BLOB
      ); 
      
      CREATE TABLE EXTERNAL_TABLE
      ( FILENAME VARCHAR2(128)
      , MIME_TYPE VARCHAR2(4000)
      )
      ORGANIZATION EXTERNAL ( 
        type oracle_loader 
        DEFAULT directory UPLOAD_DIR 
        access parameters ( 
          records delimited  by newline
          NOBADFILE
          NOLOGFILE
          fields  terminated by ','
          missing field values are null
        )
        location (UPLOAD_DIR:'EXTERNAL_DATA.csv')
      )
      reject limit unlimited;
      
      
      -- CREATE SPECIFICATION
      
      CREATE OR REPLACE
      PACKAGE bulk_upload_pkg
      AS
      -- DECLARE TYPE
      type get_fbt_bulk_upload_rec_type IS record
        ( filename external_table.filename%TYPE
        , mime_type external_table.mime_type%TYPE
          );
        
      -- DECLARE CURSOR
        CURSOR c_get_fbt_bulk_upload RETURN get_fbt_bulk_upload_rec_type;
        
      -- DECLARE PROCEDURE
        PROCEDURE bulk_upload_prc
          ( p_rec OUT get_fbt_bulk_upload_rec_type
          , p_blob OUT BLOB
          , p_srcfile OUT BFILE
            );
      
      END bulk_upload_pkg;
      /
      
      
      -- CREATE BODY
      
      CREATE OR REPLACE
      PACKAGE BODY bulk_upload_pkg
      AS
        CURSOR c_get_fbt_bulk_upload RETURN get_fbt_bulk_upload_rec_type IS
           SELECT filename
                , mime_type
             FROM external_table;
             
      PROCEDURE bulk_upload_prc
        ( p_rec OUT get_fbt_bulk_upload_rec_type
        , p_blob OUT BLOB
        , p_srcfile OUT BFILE
          )
      IS
      BEGIN
        OPEN c_get_fbt_bulk_upload;
        LOOP
          FETCH c_get_fbt_bulk_upload INTO p_rec;
          EXIT
        WHEN c_get_fbt_bulk_upload%NOTFOUND;
         
          DBMS_LOB.CreateTemporary(p_blob, TRUE);
          DBMS_LOB.Open(p_blob, dbms_lob.Lob_ReadWrite);
          
          p_srcfile := Bfilename('UPLOAD_DIR', p_rec.filename);
          
          DBMS_LOB.FileOpen (p_srcfile, dbms_lob.File_ReadOnly);
          DBMS_LOB.LoadFromFile(p_blob, p_srcfile, DBMS_LOB.GetLength(p_srcfile));
          
          INSERT
             INTO fmbsite.TARGET_TABLE
            ( filename
            , mime_type
            , blob_content
            )
            VALUES
            ( p_rec.filename
            , p_rec.mime_type
            , EMPTY_BLOB()
            );
          
          UPDATE fmbsite.TARGET_TABLE SET blob_content = p_blob WHERE filename LIKE p_rec.filename;
          
             DBMS_LOB.FileClose(p_srcfile);
             
          COMMIT;
          
        END LOOP;
        
        IF c_get_fbt_bulk_upload%isopen THEN
          CLOSE c_get_fbt_bulk_upload;
        END IF;
      
      END bulk_upload_prc;
      
      END bulk_upload_pkg;
      /
      4. Run the following code:
      DECLARE
      
        P_REC FMBSITE.BULK_UPLOAD_PKG.GET_FBT_BULK_UPLOAD_REC_TYPE;
        P_BLOB BLOB;
        P_SRCFILE BFILE;
        
      BEGIN
      
        BULK_UPLOAD_PKG.BULK_UPLOAD_PRC
         ( P_REC => P_REC
         , P_BLOB => P_BLOB
         , P_SRCFILE => P_SRCFILE
          );
      
      END;
      5. Have a look at the table named TARGET_TABLE, it should contain the attributes from the CSV file and BLOB content from the UPLOAD_DIR

      Cheers
      Ben
        • 1. Re: Loading multiple blobs with PLSQL - Thank You
          802709
          perfect :) I congratulate you not just because you found the answer also you share the soulution of your problem.
          1 person found this helpful
          • 2. Re: Loading multiple blobs with PLSQL - Thank You
            Saubhik
            Hi Benton, Congratulation on your solution. But my feeling is you did it in a hard working way!. You can load your pdf file as BLOB in external table directly! See the demo.
            This is my pdf files
            C:\Saubhik\Assembly\Books\Algorithm>dir *.pdf
             Volume in drive C has no label.
             Volume Serial Number is 6806-ABBD
            
             Directory of C:\Saubhik\Assembly\Books\Algorithm
            
            08/16/2009  02:11 PM         1,208,247 algorithms.pdf
            08/17/2009  01:05 PM        13,119,033 fci4all.com.Introduction_to_the
            d_Analysis_of_Algorithms.pdf
            09/04/2009  06:58 PM        30,375,002 sedgewick-algorithms.pdf
                           3 File(s)     44,702,282 bytes
                           0 Dir(s)   7,474,593,792 bytes free
            
            C:\Saubhik\Assembly\Books\Algorithm>
            This is my file with which I'll load the pdf files as BLOB
            C:\Saubhik\Assembly\Books\Algorithm>type mypdfs.txt
            Algorithms.pdf,algorithms.pdf
            Sedgewick-Algorithms.pdf,sedgewick-algorithms.pdf
            
            C:\Saubhik\Assembly\Books\Algorithm>
            Now the actual code
            SQL> /* This is my directory object */
            SQL> CREATE or REPLACE DIRECTORY saubhik AS 'C:\Saubhik\Assembly\Books\Algorithm';
            
            Directory created.
            
            SQL> /* Now my external table */
            SQL> /* This table contains two columns. 1.pdfname contains the name of the file
            DOC>   and 2.pdfFile is a BLOB column contains the actual pdf*/  
            SQL> CREATE TABLE mypdf_external (pdfname VARCHAR2(50),pdfFile BLOB)
              2         ORGANIZATION EXTERNAL (
              3           TYPE ORACLE_LOADER
              4            DEFAULT DIRECTORY saubhik
              5            ACCESS PARAMETERS (
              6              RECORDS DELIMITED BY NEWLINE
              7              BADFILE saubhik:'lob_tab_%a_%p.bad'
              8              LOGFILE saubhik:'lob_tab_%a_%p.log'
              9              FIELDS TERMINATED BY ','
             10              MISSING FIELD VALUES ARE NULL
             11               (pdfname char(100),blob_file_name CHAR(100))
             12              COLUMN TRANSFORMS (pdfFile FROM lobfile(blob_file_name) FROM (saubhik) BLOB)
             13            )
             14            LOCATION('mypdfs.txt')
             15         )
             16         REJECT LIMIT UNLIMITED;
            
            Table created.
            
            SQL> SELECT pdfname,DBMS_LOB.getlength(pdfFile) pdfFileLength
              2  FROM   mypdf_external;
            
            PDFNAME                                            PDFFILELENGTH
            -------------------------------------------------- -------------
            Algorithms.pdf                                           1208247
            Sedgewick-Algorithms.pdf                                30375002
            
            SQL> 
            Now, you can use this table for any operation very easily. Even for your loading into another table!.
            • 3. Re: Loading multiple blobs with PLSQL - Thank You
              benton
              Hello Saubhik,
              This is a much more elegant solution than using DBMS_LOB. I am going to incorporate this into my package, cheers for the tip.
              Ben