Re: Problem creating LOB in Oracle 8i
Date: Mon, 29 Jan 2001 01:50:18 GMT
Message-ID: <u%3d6.1182$9q6.182032_at_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; /
- Select the LOB
- where the read begins
chunksize INTEGER;
BEGIN
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 thenuse
> 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 deadend.
> > Thanks in advance. Replies by email also please. > > Richard > >Received on Mon Jan 29 2001 - 02:50:18 CET