How to load LOB into the database. Please help

[65]

Hi All,

Anyone please help me how load LOB (from PDF file)into the database. I am very new to this issue. Please direct me step by step how to load LOB and also where to find the useful documentation to learn more about LOB. Your help is appreciated.

Thanks in advance.

th

 

[spenglerr]

Hi th

You get an Oracle Documentation CD with your database containing a lot of information how to work with LOBS and/or cartridges.

To load a FILE into the database (a pdf-file) your user needs the "create any directory"-privilege:

/*
|| Directory-Name for the PDF-Files
*/
CREATE DIRECTORY pdffiles AS 'D:\';

CREATE TABLE pdfs (
ID NUMBER
,FILENAME VARCHAR2(250)
,CONTENT BLOB
,CONSTRAINT pk_pdfimp PRIMARY KEY(id)
USING INDEX TABLESPACE users
);

CREATE SEQUENCE seq_pdf_imports
START WITH 1
INCREMENT BY 1
NOCACHE;

CREATE OR REPLACE PROCEDURE insertPDFFile (
dir VARCHAR2
,file VARCHAR2
,name VARCHAR2 := NULL )
IS
theBFile BFILE;
theBlob BLOB;
theFileName VARCHAR2(250) := NVL(name,file);

BEGIN
-- (1) insert a new row into xml_documents with an empty BLOB and
-- (2) Retrieve the empty BLOB into a variable
INSERT INTO pdfs (
id
,filename
,content )
VALUES (
seq_pdf_imports.NextVal
,theFileName
,empty_blob()
)
RETURNING content INTO theBlob;

-- (3) Get a BFile handle to the external file
theBFile := BFileName(dir,file);

-- (4) Open the file
DBMS_LOB.fileOpen(theBFile);

-- (5) Copy the contents of the BFile into the empty CLOB
DBMS_LOB.loadFromFile (
dest_lob => theBLOB
,src_lob => theBFile
,amount => DBMS_LOB.GETLENGTH(theBFile) );

DBMS_LOB.fileClose(theBFile);
END insertPDFFile;
/


execute insertPDFFile('PDFFILES','test.pdf','pdf-picture of me');

NOTE: the directory has always to be given in CAPITALS to the procedure BFileName!!!

 

[65]

Hi Spenglerr,

Thanks for your quick response. I do appreciate it. I will work on it very soon then will let you know the result. Again, thanks for your help.

Th

 

[65]

Hi Spenglerr,

Thanks for your quick response. I do appreciate it. I will work on it very soon then will let you know the result. Again, thanks for your help.

Th

 

[65]

Hi Spenglerr,

I want to get internal LOBs (not external LOBs)stored within the database (either in-line in the table or in a seperate tablspace. Your code provided an example of BFILE which is stored in operating system. Please help me how to load pdf file into the table.

Thanks in advance.
th

 

[spenglerr]

Hi th,

that is not true:

DBMS_LOB.fileOpen(theBFile);
-- this indeed opens a BFILE for reading from it!

DBMS_LOB.loadFromFile (
dest_lob => theBLOB
,src_lob => theBFile
,amount => DBMS_LOB.GETLENGTH(theBFile) );
-- this uses the BFILE as a source (src_lob) and load the data inside this file to the BLOB-column inside the table that still is opened due to the RETURNING-clause.

DBMS_LOB.fileClose(theBFile);
-- this is just to close the fileHandle from the SOURCE-file.

Actually it is true indeed, that I load the PDF directly into a BLOB, so it is still a PDF-File, but inside a recordset in the BLOB-column! If you want to put the content of the PDF as text into a CLOB you will need to use a routine to convert PDF to text, but your question didn't sound like this.

 

[65]

Thank you for your help, Spenglerr. I do appreciate it.

Th

 

[65]

Hi Spenglerr,

I just finished working on backup task. Now I switch back working on loading LOB again. Just read the Oracle8i utilities, I then learn one thing that I can load LOBs by using sql*loader. I do appreciate your help, however, I barely understand the code you provided it as LOBs is very brand new to me. My question is what the difference between your method and the sql*loader utility is? and how do I view the content of column has LOBs datatype to verify that the LOBs contents is correct?

Thanks in advance. Again, your help is greatly appreciated.

th

 

[spenglerr]

Hi

the only difference is, that my routine is a PL/SQL procedure that can be called directly by any Application. Therefore you can enable for instance that a user can upload a file directly using a form on a website for example or a http-POST to a form.

With SQL*Loader this would be more difficult, since you need a sql-client on the users PC and you need an experienced user and the user has to use a batch-file for instance requesting the name of the file ....

Of course, just for uploading images into the database by yourself SQL*Loader can be used.

 

[65]

Hi Spenglerr,

Thanks for pointing me in the right direction. I do appreciate it. Well, I have to read and do more research about LOBs to educate myself to be familiar with LOBs. Since, I had installed downloaded Oracle8i on my computer from Oracle.com, so, I can't get an Oracle Documentation CD containing a lot of information how to work with LOBS as you directed. Anyway, I will try my best to make it work. Again, thanks for your quick response.

Th

 

[jee]

If you need access to Oracle manuals, sign up for the Oracle Technology Network (signup is free). All Oracle 8i manuals are there (as well as 9i). URL for 8i manuals is:

http://otn.oracle.com/doc/oracle8i_816/products.htm

 

[65]

HI Jee,
Thanks for your information. I do appreciate it. Currently I've been working on LOBs. I will need you all your input as needed.

Again, thanks a lot.
th

 

[65]

Hi,

I 've still struggled with this issue. For temperary use, can I upload pdf file (all text, no images)into a BLOB column by using sql*plus. If so, how can I view the contents of the BLOB as it can not be query-able. Please help.

I do need you all for your help badly and urgently.

Your help is greatly appreciated.

Thanks a lot
Th

 

[spenglerr]

Hi Th,

actually you can read the BLOB using the DBMS_LOB packages READ method. In your documentation you will find howtos to the package.

Important, you will have to read into buffer, the size of maxdata in SQL*PLUS is limited to a maximum of 32767. Thus if your pdf extents this size you will have to create a LOOP and read the BLOB with DBMS_LOB.READ chunkwise.

Another possibility is to use java, then you can request from an application via JDBC the BLOB using the java classes that already can read bigger sizes as well.

 

[65]

Hi spenglerr,

Below is what I did step by step to load an external PDF file into BFILE column.

Please note that I already stored the PDF file (‘6117cdsapx.pdf’) on my Oracle server site at '/Disk05/test'.(my Oracle server 8.1.7 is running on Suse Linux 7.3 system),
And also granted the CREATE ANY DIRECTORY to Lenka


CREATE TABLE MY_BOOK_TEXT (
FILE_DESC VARCHAR2 (200),
BOOK_FILE BFILE);

CREATE DIRECTORY TEST AS ‘/Disk05/test’;

DECLARE
pdfile BFILE;
BEGIN
pdfile := BFILENAME (‘TEST’, ‘6117cdsapx.pdf’);
END;
/
INSERT INTO MY_BOOK_TEXT (FILE_DESC, BOOK_FILE )
VALUES (‘IBM Microprocessor’, BFILENAME(‘TEST’, ‘6117cdsapx.pdf’);

Up to this point, everything worked well. Now I just wanted to retrieve/view the content of the BLIFE column to make sure that it exists. I did use DBMS_LOB package to do so

CREATE OR REPLACE PROCEDURE displayBFILE_proc IS
Lob_loc BFILE;
Buffer RAW(1024);
Amount BINARY_INTEGER := 1024;
Position INTEGER := 1;
BEGIN
/* Select the LOB: */
SELECT book_file INTO Lob_loc
FROM my_book_text WHERE file_desc = 'IBM Microprocessor';
/* Opening the BFILE: */
DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READONLY);
LOOP
DBMS_LOB.READ (Lob_loc, Amount, Position, Buffer);
/* Display the buffer contents: */
DBMS_OUTPUT.PUT_LINE(utl_raw.cast_to_varchar2(Buffer));
Position := Position + Amount;
END LOOP;
/* Closing the BFILE: */
DBMS_LOB.CLOSE (Lob_loc);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('End of data');
END;

This procedure is successfully compiled. However, when execute it with the below command:

execute DISPLAYBFILE_PROC;

The procecure failed on me.

Please base on the above provided information to help me to write a procedure to retrieve/view the content of the BFILE column in the table my_book_text. Your help is greatly appreciated.

Thanks in advance.

th