Re: How do I store files in 8.x?

From: Van Messner <vmessner_at_bestweb.net>
Date: Sat, 10 Mar 2001 16:34:20 GMT
Message-ID: <gIsq6.584$7d.66960_at_newshog.newsread.com>

You asked how so...see below. Everything works as shown for binary files (I used an Excel spreadsheet), but you will need some Java pieces to present the final results to your users. Don't have a sample of that for you, but may be available in books or on the Oracle tech site. Or if any other posters are willing to append one, I'd love to see it. Or to correct or improve anything below.

Van

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 LDGLOBALINFO used for all tables, LOB or not lob (BLOB_COLUMN_NAME) store as used for LOBs only ( tablespace LDGLOBALINFO_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 ntradevl UNIX server we set up a new directory:
ldcwlt40/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 ntradevl and ldcfusion 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 ldcfusion: GRANT READ ON DIRECTORY DOCS to ldcfusion; 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', 'ldcwlt40',
        '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; --
  - 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; /

"E. Frijters" <erik_at_e-frijters.tmfweb.nl> wrote in message news:3aa7ead2$0$9741_at_reader2...

> Hello,
>
> I'm building a client server application with Microsft Visual FoxPro 6.0

and

> Oracle 8.
>
> I want to store report-files in  the central Oracle database, so I can
> update the reports of the different clients through the database. The only
> purpose is to distribute the files, nothing more...
>
> I want to use BLOB or BFILE, but I don't now how to pass the information

> the database. (I use the latest ODBC drivers 8.05.00)
>
> I use Sequel Pass Through and Remote Views as programming techniques.
>
> Any help or examples are very appreciated!
>
> Greetings
>
> E. Frijters
> erik_frijters_at_deltalloyd.nl
> or
> e.frijters_at_wanadoo.nl
>
>
>

Received on Sat Mar 10 2001 - 17:34:20 CET

This message: [ Message body ]
Next message: Arthur: "Please help with upgrade of Form v4.5 to 5"
Previous message: Van Messner: "Re: ora-12505"
In reply to: E. Frijters: "How do I store files in 8.x?"
Next in thread: Daniel A. Morgan: "Re: Is it possible to create a System tablespace with Local managed extent?"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message