Home -> Usenet -> c.d.o.tools -> Re: loading a blob into oracle

Re: loading a blob into oracle

From: Van Messner <vmessner_at_bestweb.net>
Date: Sun, 25 Mar 2001 20:48:07 GMT
Message-ID: <bQsv6.79$7d.18184_at_newshog.newsread.com> 







Here's one way.  Hopefully others will have something more elegant,
especially when it comes to putting the blob back out into the file system.



Large Objects



 Oracle can store different types of large objects (LOBs).  For our purposes
we will be storing binary files as BLOBs.  If the information were strictly
character data, we could store it as a CLOB.
 Within Oracle, BLOBs are put in a separate tablespace and not stored with
the rest of the columns in a table.  The table has only an internal locator
value for the BLOB data.  That way the database does not have to scan the
BLOB data each time it reads multiple rows from the database table.



 When your database designer creates a table with a LOB he needs an
additional storage clause to tell Oracle where to put the LOB:
storage(initial....)


used for all tables, LOB or not


tablespace LDGLOB


used for all tables, LOB or not

lob (BLOB_COLUMN_NAME) store as                       used for LOBs only
  ( tablespace LDGLOB_LOBS                                       used for
LOBs only
      storage (initial 256K next 256K pctincrease 0)          used for LOBs
only
      chunk 16K pctversion 10 nocache logging );              used for LOBs


only



 For now we will not index our LOBs, but you can and you can use a separate
LOB index tablespace if you wish.



 In general LOBs are manipulated using the Oracle-supplied package DBMS_LOB.
This package is similar in concept to other supplied packages we already
use - DBMS_SQL, DBMS_JOB, etc.  It is more difficult to select and
manipulate LOBs than it is for VARCHAR2s.



 When a developer inserts a row into a table, if there is no value for the
BLOB the insert looks like this:


INSERT INTO X ( KEY, BLOB_COL ) values ( 1, EMPTY_BLOB() )
Note the use of EMPTY_BLOB() to represent an empty locator value rather than
NULL.  It is very important to use this syntax to prevent extra work later.








 So we need to be able to take documents from a file system location(1), put
them into Oracle(2), retrieve them from Oracle(3), and put them out to a
file system or some other place where a user can access and use the
documents(4).




(1) On the ntrade UNIX server we set up a new directory:


ldc40/oracle/oradata/tblspc/documents


and in the documents folder we placed an Excel spreadsheet, TSpaces.xls




(2) We needed to take several steps before putting a document into Oracle.


First we granted ntrade and ldfusion the CREATE ANY DIRECTORY privilege.
Then we modified the init.ora parameter UTL_FILE_DIR =
/oracle/oradata/tblspc/documents.  In step (4) below this will prevent us
from accidentally writing something to an inappropriate location, such as
directly on top of our control files!


 Next we create a BFILE locator which is a directory path and a filename.
We will use an easy-to-write alias for the directory path which, after
logging in as LDGLOB, we create like this:
CREATE DIRECTORY DOCS AS '/oracle/oradata/tblspc/documents';
We'll grant read permission on this directory to ldfusion:
GRANT READ ON DIRECTORY DOCS to ldfusion;
 Oracle has a built-in function called BFILENAME.  We can use this function
to create a locator for the external file.  This locator is called a BFILE
locator and we might use it in a little anonymous block like this:
  DECLARE



        tspacexls  BFILE;


  BEGIN



        tspacexls := BFILENAME('DOCS', 'TSpaces.xls');
  END;



  /


  PL/SQL procedure successfully completed.
 Well, so far so good.  Now we'd like to grab a document and insert it into
an Oracle table.  While you were reading, I went and created a table called
LG_DOCUMENT_ITEM_DETAIL2 using this SQL:
  Create Table LDGLOB.LG_DOCUMENT_ITEM_DETAIL2 (

  DOC_ITEM_KEY    NUMBER(8)   NOT NULL,
  DOC_ITEM_NAME    VARCHAR2(100) NULL,
  DOC_ITEM_PROGRAM   NUMBER(8)  NULL,
  DOC_ITEM_EXTENSION   VARCHAR2(10) NULL,
  DOC_ITEM_SERVER   VARCHAR2(40) NULL,
  DOC_ITEM_PATH    VARCHAR2(100) NULL,
  DOC_ITEM_TYPE    VARCHAR2(40) NULL,
  DOC_ITEM_ACTUAL_DATA  BLOB   NULL,
  DOC_ITEM_POSTED_DATE  DATE   NULL,
  DOC_ITEM_LAST_MOD_DATE  DATE   NULL,
  DOC_ITEM_COMMENT   VARCHAR2(200) NULL,


    constraint DOC_ITEM2_PK primary key (DOC_ITEM_KEY)
    using index tablespace LDGLOB_INDX


    storage (initial 32K next 32K pctincrease 0 minextents 1 maxextents 249)
  )


  tablespace LDGLOB


  storage (initial 32K next 32K pctincrease 0 minextents 1 maxextents 249)
  LOB (DOC_ITEM_ACTUAL_DATA) store as


    ( tablespace LOBSPC

      storage (initial 1M next 1M pctincrease 0 minextents 1 maxextents 512)
      chunk 16K pctversion 20 nocache logging )


  ;


 Let's grab our Excel spreadsheet and insert it into the
DOC_ITEM_ACTUAL_DATA.  To show this I'll use another anonymous block using
some of the procedures in the Oracle-supplied DBMS_LOB package:
  DECLARE



    source_locator    BFILE;


    destination_locator    BLOB;


    file_size       INTEGER;


    ignore   INTEGER;


  BEGIN



    source_locator := BFILENAME('DOCS', 'TSpaces.xls');
    file_size      := DBMS_LOB.GETLENGTH(source_locator);
    --


    DBMS_OUTPUT.PUT_LINE('Open the External File');

    --
    -- you must open the file before you can work with it.
    --




    ignore := DBMS_LOB.FILEISOPEN(source_locator);
    IF (ignore=1) THEN


      NULL;



    ELSE



      DBMS_LOB.FILEOPEN(source_locator, DBMS_LOB.file_readonly);
    END IF;



    --


    DBMS_OUTPUT.PUT_LINE('Spreadsheet Size: '||file_size);
    DBMS_OUTPUT.PUT_LINE('Inserting Empty Spreadsheet Row');
    --


    INSERT INTO LDGLOB.LG_DOCUMENT_ITEM_DETAIL2 (


      DOC_ITEM_KEY,
      DOC_ITEM_NAME,
      DOC_ITEM_PROGRAM,
      DOC_ITEM_EXTENSION,
      DOC_ITEM_SERVER,
      DOC_ITEM_PATH,
      DOC_ITEM_TYPE,
      DOC_ITEM_ACTUAL_DATA,
      DOC_ITEM_POSTED_DATE,
      DOC_ITEM_LAST_MOD_DATE,
      DOC_ITEM_COMMENT )
      VALUES (
        696, 'Test Excel Spreadsheet', 1, '.xls', 'ldc40',
        'dummy path', '', EMPTY_BLOB, sysdate, sysdate, 'dummy comment')


    RETURNING DOC_ITEM_ACTUAL_DATA INTO destination_locator;
    --


    DBMS_OUTPUT.PUT_LINE('Loading Spreadsheet into Oracle Table');
    --


    DBMS_LOB.LOADFROMFILE(destination_locator, source_locator, file_size);
    COMMIT;


    --
    -- close the file when you are all done
    --




    DBMS_OUTPUT.PUT_LINE('Close the External File');
    --


    DBMS_LOB.FILECLOSE(source_locator);


  EXCEPTION



    WHEN OTHERS THEN



      DBMS_OUTPUT.PUT_LINE('OTHERS Exception ' || sqlerrm);
  END;



  /


 Which produces these results:


Open the External File


Spreadsheet Size: 194048


Inserting Empty Spreadsheet Row


Loading Spreadsheet into Oracle Table


Close the External File


PL/SQL procedure successfully completed.




(3) Now let's make sure we can manipulate the data we placed in Oracle by


running a small SQL select:


  SELECT



    DOC_ITEM_KEY,



    DOC_ITEM_NAME,



    DBMS_LOB.GETLENGTH(DOC_ITEM_ACTUAL_DATA)

  FROM



    LDGLOB.LG_DOCUMENT_ITEM_DETAIL2



  WHERE



    DOC_ITEM_PROGRAM = 1;



Which gives us


DOC_ITEM_KEY DOC_ITEM_NAME



DBMS_LOB.GETLENGTH(DOC_ITEM_ACTUAL_DATA)


------------ ------------------------- -------------------------------------
---
         696 Test Excel Spreadsheet




194048




(4) The last step is to grab the information from Oracle and make it


accessible to a user as an Excel spreadsheet.  We cannot simply select the
BLOB column in SQL*Plus since Oracle does not have this capability.  Once
again we'll need a short procedure.


  DECLARE



    destination_locator BLOB;    -- pointer to the BLOB stored


    
  
  •   in the database
      lob_buffer RAW(32767); -- where the BLOB will go when retrieved
    amount  BINARY_INTEGER := 32767; -- how much BLOB to grab with each pass
    position  INTEGER := 1;   -- location of the byte in the BLOB

  
      
    
    •   where the read begins
    chunksize          INTEGER;
  BEGIN

    --

    
        
      
      •   Select the LOB
    --
    SELECT

      DOC_ITEM_ACTUAL_DATA INTO destination_locator
    FROM

      LDGLOB.LG_DOCUMENT_ITEM_DETAIL2

    WHERE

      DOC_ITEM_KEY = 696;

    --
      
      •   Find out the chunksize for this LOB column
    --
    chunksize := DBMS_LOB.GETCHUNKSIZE(destination_locator);
    IF (chunksize < 32767) THEN
      Amount := (32767 / chunksize) * chunksize;
    END IF;

    --
      
      •   Read data from the LOB
    --
    DBMS_LOB.READ (destination_locator, amount, position, lob_buffer);
    --
      
      •   You may want to loop through the procedure until you have the whole
LOB

    --
  END;

  /




    
      

  
    





"William S. Kossack" <wskossack_at_home.com> wrote in message
news:3ABE24D4.A2C3DFA1_at_home.com...

> I have a jpg file that I want to insert into a blob field in oracle.
> How do I do this?  I've given up trying to hack a solution.
>
> contact kossack80021_at_yohoo.com
>


Received on Sun Mar 25 2001 - 22:48:07 CEST












Original text of this message