1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Unable to see the pictures inserted into table

Discussion in 'SQL PL/SQL' started by pgudur, Jul 22, 2009.

  1. pgudur

    pgudur Active Member

    Messages:
    18
    Likes Received:
    1
    Trophy Points:
    90
    Hi,
    Can any one help me Please..........
    I appreciate if any one

    Please look at this

    Code (Text):
    SQL> CREATE OR REPLACE procedure insert_image
      2  (p_catalogn number,p_photo varchar2)
      3  as
      4  f_photo bfile;
      5  b_photo blob;
      6  begin
      7  update sh_photo set picture=empty_blob()
      8  where catalogn=p_catalogn
      9  return picture into b_photo;
     10  f_photo := bfilename('photos',p_photo);
     11  dbms_lob.fileopen(f_photo,dbms_lob.file_readonly);
     12  dbms_lob.loadfromfile(b_photo,f_photo,dbms_lob.getlength(f_photo));
     13  dbms_lob.fileclose(f_photo);
     14  commit;
     15  exception
     16  when others then
     17  dbms_output.put_line('**** error ***** check your procedure');
     18  end insert_image;
     19  /

    Procedure created.

    SQL> commit;

    Commit complete.

    SQL> execute insert_image(1,'Animallandscape.jpeg');

    PL/SQL procedure successfully completed.

    SQL> commit;

    Commit complete.

    SQL> select dbms_lob.getlength(picture) as photo_size from sh_photo;

    PHOTO_SIZE
    ----------
             0
             0
             0
             0
             0
             0
             0
             0
             0
             0
             0
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Hi have you mapped the directory in which your image 'Animallandscape.jpeg' resides to a directory in oracle? That is something like this

    Code (Text):
    create directory image_dir as 'c:\image_dir';
    Also please provide the create table script for sh_photo so that I can reproduce your case in my database.
     
  3. pgudur

    pgudur Active Member

    Messages:
    18
    Likes Received:
    1
    Trophy Points:
    90
    Hi,
    Yes
    created a directory as photos.
     
  4. pgudur

    pgudur Active Member

    Messages:
    18
    Likes Received:
    1
    Trophy Points:
    90
    Hi,

    Here is the table script

    create table sh_photo(catalogn number
    , picture blob
    ,foreign key(catalogn) references photo(catalogn)
    );

    I tried all the way but where i am going wrong i dont know
    please let me know
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Code (SQL):

    dbms_lob.fileopen(f_photo,dbms_lob.file_readonly);
     12  dbms_lob.loadfromfile(b_photo,f_photo,dbms_lob.getlength(f_photo));
     13  dbms_lob.fileclose(f_photo);
     
    Check what you tried in your script ...

    dbms_lob.fileopen --> Opened a file for reading
    dbms_lob.loadfromfile --> Loaded BFILE data into an LOB Variable
    dbms_lob.fileclose --> Closed the file Opened

    You missed out to update the Blob back to the table sh_photo !!!!!!


    Check the DBMS_LOB link in PSOUG for more details.