DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: How i can use blob data type?

  1. #1
    Join Date
    Nov 2001
    Location
    Indian
    Posts
    2

    Arrow How can I store picture in oracle?

    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]

  2. #2
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    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.
    Code:
    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:

    Code:
    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.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  3. #3
    Join Date
    Feb 2001
    Posts
    20

    Post

    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


  4. #4
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    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]

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  5. #5
    Join Date
    Feb 2001
    Posts
    20
    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

  6. #6
    Join Date
    Feb 2001
    Posts
    389
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width