Home » SQL & PL/SQL » Client Tools » Inserting BLOB - Unable to Locate file on Server (Oracle 9i R2 on Linux)
Inserting BLOB - Unable to Locate file on Server [message #309112] Wed, 26 March 2008 08:05 Go to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Hi,

I have following table which uses BLOB field.


CREATE TABLE SIGNS
(
  LSEQ       NUMBER(12)                      NOT NULL,
  SIGNATURE  BLOB
)
TABLESPACE TBS01
  LOB (STRSIGNATURE) STORE AS 
      ( TABLESPACE  TBS01
        ENABLE      STORAGE IN ROW
        CHUNK       8192
        PCTVERSION  10
        NOCACHE
        STORAGE    (
                    INITIAL          64K
                    MINEXTENTS       1
                    MAXEXTENTS       2147483645
                    PCTINCREASE      0
                    BUFFER_POOL      DEFAULT
                   )
      );



I loaded a record into the table as following


 declare
    l_blob    blob;
    l_bfile    bfile;
begin
    insert into SIGNS values ( 3, empty_blob() ) 
    returning SIGNATURE into l_blob;

    l_bfile := bfilename( 'DIR9', 'mast2.bmp' );
    dbms_lob.fileopen( l_bfile );

    dbms_lob.loadfromfile( l_blob, l_bfile, 
                           dbms_lob.getlength( l_bfile ) );

    dbms_lob.fileclose( l_bfile );
end;
/
commit;



it went fine and the file mast2.bmp as at /u02/DB01/test9

Next
Insert into SIGNS values(5);

Then I carried following actions from TOAD from client.

1)
edit SIGNS where lseq=5;

2)
Right clicked the record displayed

3) Clicked "Memo Editor"

4) Clicked "Load File" icon

5) Selected a file 'c:\test.bmp' from local drive.

6) commited the transaction.



Now I was able to view the Image from application or even tools like SQL Developer, PL/SQL developer
however when i searched 'test.bmp' on the database server, I was unable to find it out.

Even if I 'Load File' from local machine using SQL Developer or PL/SQL Developer, I can see the image from application but just can't locate the file on server.

Kindly suggest what could be happening with the files and how to locate the files on the server?

Thanks and Regards,
Pratap
Re: Inserting BLOB - Unable to Locate file on Server [message #309130 is a reply to message #309112] Wed, 26 March 2008 09:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The image is stored in the database, not in the filesystem.

insert into SIGNS values ( 3, empty_blob() )


You also cannot find that 3 anywhere on the filesystem..
Re: Inserting BLOB - Unable to Locate file on Server [message #309145 is a reply to message #309112] Wed, 26 March 2008 10:23 Go to previous message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Many Thanks Frank!!

I was thinking For Internal LOB like BLOB even after LOBSegment and LOBIndex file used for uploading will be required on server.

I further confirm it by removing the file on server and still application is able to access and display the Image correctly.

Thanks for Clearing my doubt.

Regards,
Pratap
Previous Topic: Debug Menu is disabled in TOAD 8.6.1.0 (to debug Stored Procedure)
Next Topic: TOAD Error
Goto Forum:
  


Current Time: Fri Dec 02 12:06:46 CST 2016

Total time taken to generate the page: 0.10357 seconds