Re: Problem creating LOB in Oracle 8i

From: Van Messner <vmessner_at_bestweb.net>
Date: Mon, 29 Jan 2001 01:50:18 GMT
Message-ID: <u%3d6.1182$9q6.182032@newshog.newsread.com>

Here's an example I give our developers when they want to bring in a BLOB:

(1) On the UNIX server we set up a new directory: XXX/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 xxxdevl and xxxfus 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 LDGLOBALINFO, we create like this: CREATE DIRECTORY DOCS AS '/oracle/oradata/tblspc/documents'; We'll grant read permission on this directory to xxxfus: GRANT READ ON DIRECTORY DOCS to xxxfus;
 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 LDGLOBALINFO.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 LDGLOBALINFO_INDX     storage (initial 32K next 32K pctincrease 0 minextents 1 maxextents 249)   )
  tablespace LDGLOBALINFO
  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 LDGLOBALINFO.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', 'XXX', '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
    LDGLOBALINFO.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 LDGLOBALINFO.LG_DOCUMENT_ITEM_DETAIL2 WHERE DOC_ITEM_KEY = 696;
      --
      
    • 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; /

To actually put the spreadsheet back onto the file system properly you'll need a little Java or C++ routine - see Rob.

"RS" <rs_at_rshome.u-net.com> wrote in message news:4WVc6.518$i54.799937_at_newsr1.u-net.net...

> I'm experimenting with Oracle LOBs for the first time, and have created a
> simple table to hold a BLOB object..  (Oracle 8i, Linux)
>
> I've then written a procedure to read in a OS file into a BFILE and then

 use

> that BFILE to create the BLOB object in the database...
>
> Here is my code...
>
> PROCEDURE LOB_TEST
> IS
>
>        image_bfile BFILE := BFILENAME('TMP_DIR','me.jpg');
>        image_blob  BLOB;
>
> BEGIN
>      DBMS_OUTPUT.ENABLE;
>
>      DBMS_LOB.FILEOPEN (image_bfile,dbms_lob.file_readonly);
>
>          DBMS_OUTPUT.PUT_LINE (    DBMS_LOB.GETLENGTH(image_bfile)    );
>
>          SELECT image INTO image_blob FROM LOB_TABLE FOR UPDATE;
>
>          dbms_lob.loadfromfile (image_blob, image_bfile, 4000,1,1);
>
>          COMMIT;
>
>      DBMS_LOB.FILECLOSE(image_bfile);
> END;
>
>
> The procedure compiles ok, but when I run it I get the following error...
>
> ORA-06502: PL/SQL: numeric or value error
> ORA-06512: at "SYS.DBMS_LOB", line 578
> ORA-06512: at "RJS.LOB_TEST", line 18
> ORA-06512: at line 2
>
> Line 18 is    dbms_lob.loadfromfile (image_blob, image_bfile, 4000,1,1);
>
> I've tried using different values in the numeric arguments, but no luck.
> The DIRECTORY object has already been created succesfully.
>
> Can anyone point me in the right direction here... I've come to a dead

 end.

>
> Thanks in advance.  Replies by email also please.
>
> Richard
>
>

Received on Sun Jan 28 2001 - 19:50:18 CST