View LOBs with DBMS_LOB package. Please help

[65]

Hi All,

I used SQL*Loader to load an external PDF file into a database. I did successfully inserted PDF file into the database, I believe (due to not having any return error messages). However, After some digging, I found that SQL*Loader reacts to LOBs that can't be loaded in different manner. In other words, If a LOB can not be loaded, the record containing the LOB won't be written to the bad file. Instead, the record will be loaded into the databse,and the LOB column will be empty.
Knowing that DBMS_LOB package provides a set of procedures and fucntions to access and manipulate LOBs from within PL/SQL Since LOBs is not query-able, however, I do not know how to write a procedure or function in order to view the contents of the LOBS column. Anyone please help me step by step how to do so. Any help is well appreciated.
Just wanted to make sure that I did successfully load the external PDF file into the database by looking at its contents.
I need you all for your help badly.

Thanks in advance
Lenka

 

[Turkbear]

Here is an example of Reading a LOB file that you can probably adapt:

CREATE OR REPLACE PROCEDURE displayLOB_proc IS 
Lob_loc  BLOB; 
Buffer   RAW(1024); 
Amount   BINARY_INTEGER := 1024; 
Position INTEGER := 1; 
BEGIN 
   /* Select the LOB: */ 
   SELECT m.Map_obj.Drawing INTO Lob_loc 
   FROM Multimedia_tab m WHERE m.Clip_ID = 1; 
   /* Opening the LOB is optional: */ 
   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 LOB is mandatory if you have opened it: */ 
   DBMS_LOB.CLOSE (Lob_loc); 
   EXCEPTION 
      WHEN NO_DATA_FOUND THEN 
         DBMS_OUTPUT.PUT_LINE('End of data');

I suspect you could just use this part of the proc to test for the existance of a LOB

CREATE OR REPLACE PROCEDURE displayLOB_proc IS 
Lob_loc  BLOB; 
BEGIN 
   /* Select the LOB: */ 
   SELECT m.Map_obj.Drawing INTO Lob_loc 
   FROM Multimedia_tab m WHERE m.Clip_ID = 1;

Then test Lob_Loc and if NULL there was no LOB there..

The OnLIne docs at

http://tahiti.oracle.com

has this and other examples - search in the Pl/Sql sections of the books...

 

[65]

Thanks for your help, Turkbear. I will get back with you very soon.

Thanks alot

Lenka

 

[65]

Hi Turkbear,

Below is what I did step by step. Please take a look at my procedure to see what did I do wrong. I am unable to view the content of the lobs column. Your help is greatly appreciated.
Please note that I already stored my pdf file (6117cdsapx.pdf) on the Oracle server under /Disk05/test.


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

CREATE DIRECTORY TEST AS ‘/Disk05/test’;

DECLARE
pdf BFILE;
BEGIN
pdf := BFILENAME (‘TEST’, ‘6117cdsapx.pdf’);
END;
/

INSERT INTO MY_BOOK_TEXT (FILE_DESC, BOOK_FILE )
VALUES (‘IBM Microprocessor’, BFILENAME(‘TEST’, ‘6117cdsapx.pdf’);


Here is your procedure, I just cut and past to execute it on the server side. But still I am unable to view the lobs.

CREATE OR REPLACE PROCEDURE displayLOB_proc IS
Lob_loc BLOB;
Buffer RAW(1024);
Amount BINARY_INTEGER := 1024;
Position INTEGER := 1;
BEGIN
/* Select the LOB: */
SELECT m.Map_obj.Drawing INTO Lob_loc
FROM Multimedia_tab m WHERE m.Clip_ID = 1;
/* Opening the LOB is optional: */
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 LOB is mandatory if you have opened it: */
DBMS_LOB.CLOSE (Lob_loc);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('End of data');

Any help is greatly appreciated.

Thanks in advance.

Lenka

 

[Turkbear]

Hi,
Sorry but my knowledge base on LOBs is limited to what I posted...I don't use them myself, so someone else will need to take you further..

 

[65]

Hi,
Thanks for your response, Turkbear. I do appreciate it.

I need you all for your help.

Thanks

Lenka.

 

[karluk]

If your procedure is completing successfully but you just aren't seeing any data, you should check to make sure that you've set serveroutput on. That's the most common reason for not seeing the results of a call to dbms_output.

There may be other problems, but it's best to check the obvious first.

 

[65]

Hi Karluk,

Thanks for your response. For some reason, I did not receive any email notification for your answer. Sorry for not replying to your message any sooner.Welll, The procedure is not completing succesffuly. It created with compilation errors. Anyway, I don't need to retrieve the contents of BFILE column any more. Just wanted to ask you all a question about how to load the in-line data into BLOB column. I need to load quite a few PDF files into a BLOB column. These data of PDF files need to be stored on the database not on Operating system. Please note that each pdf file contains pictures and text altogether. They are 125GB in size. Please advice me how to load in-line data into BLOB column. Thanks for your help.

Lenka