How i can use blob data type?

[niladri]

Dear All,
How can I store picture in oracle ? can I use BLOB data type for this?
And How I can use BLOB data type in oracle?
What is long row? What is different with BLOB?
Thanking ALL
Niladri


[Edited by niladri on 11-05-2001 at 07:54 AM]

[Raminder]

Yes. You can store Images in BLOB datatype in Oracle.
Using it is a bit tricky. You have to use DBMS_LOB package and know the syntax.
First Create an Oracle directory corresponding to the OS directory where your images are stored.

create directory root_dir as 'C:/My_Pictures';

After creating the table with a column of BLOB datatype, you can initialize it by storing empty values (different from NULL values).
Then you can load images with a procedure like this:

CREATE OR REPLACE PROCEDURE loadLOB_proc IS
   Dest_loc       BLOB;
   Src_loc        BFILE := BFILENAME('ROOT_DIR', 'ocp_logo.gif');
   Amount         INTEGER := 4000;--this can be as per size of image
BEGIN
   SELECT image INTO Dest_loc FROM imagetest
      WHERE ID = 1 FOR UPDATE;
   /* Opening the LOB is mandatory: */
    DBMS_LOB.OPEN(Src_loc);
     /* Opening the LOB is optional: */
   DBMS_LOB.OPEN(Dest_loc);
   DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount);
   /* Closing the LOB is mandatory if you have opened it: */
   DBMS_LOB.CLOSE(Dest_loc);
   DBMS_LOB.CLOSE(Src_loc);
   COMMIT;
END;
/
show errors

Wish you all the best.

[vrkeesara]

HII THANKS Raminder UR REPLY
BUT I EXECUITED UR PROCEDURE IT RETURN ERRORS
LIKE*************

CREATE OR REPLACE PROCEDURE loadLOB_proc IS
Dest_loc BLOB;
Src_loc BFILE := BFILENAME('TDTECH', 'picture1.jpg');
Amount INTEGER := 4000;
BEGIN
SELECT COMPANY_MAP INTO Dest_loc FROM TDTECH11
WHERE COMPANY_ID = 'TD1' FOR UPDATE;
DBMS_LOB.OPEN(Src_loc);
DBMS_LOB.OPEN(Dest_loc);
DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount);
DBMS_LOB.CLOSE(Dest_loc);
DBMS_LOB.CLOSE(Src_loc);
COMMIT;
END;

** ERRORS

-----------------------------------------------------------------
PLS-00306: wrong number or types of arguments in call to 'OPEN'
PL/SQL: Statement ignored

[Raminder]

Which Oracle release u are using?

Have you initialised the destination blob by empty_blob() procedure?

[Edited by Raminder on 11-11-2001 at 12:58 AM]

[vrkeesara]

Hiiii I am using Oracle8i Enterprise Edition Release 8.1.6.0.0 .

1) create table tdtech11(CID VARCHA2,CD CLOB,IMAGE BLOB,DETAILS BFILE);
INSERT INTO TDTECH11 VALUES(2,'DDDD',EMPTY_BLOB(),BFILENAME('TDTECH','PICTURE1.JPG'));

3)RUN THE PROCEDURE,AFTER THAT I GET ERROR

HII THANKS Raminder UR REPLY
BUT I EXECUITED UR PROCEDURE IT RETURN ERRORS
LIKE*************

CREATE OR REPLACE PROCEDURE loadLOB_proc IS
Dest_loc BLOB;
Src_loc BFILE := BFILENAME('TDTECH', 'picture1.jpg');
Amount INTEGER := 4000;
BEGIN
SELECT COMPANY_MAP INTO Dest_loc FROM TDTECH11
WHERE COMPANY_ID = 'TD1' FOR UPDATE;
DBMS_LOB.OPEN(Src_loc);
DBMS_LOB.OPEN(Dest_loc);
DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount);
DBMS_LOB.CLOSE(Dest_loc);
DBMS_LOB.CLOSE(Src_loc);
COMMIT;
END;

** ERRORS

-----------------------------------------------------------------
PLS-00306: wrong number or types of arguments in call to 'OPEN'
PL/SQL: Statement ignored

HIIII HOW TO OVER COME THIS ERROR?
THANKING U

[gpsingh]

You are using which requires further configurations:

1) At OS level , create directory to store files , owned by oracle:dba (or whatever ur oracle id and group is)

2) At DBA level: a) open_files= ini init.ora file,
b) create directory ......(see syntax in oracle manual), grant privileges on this directory to users who need to access these bfiles.