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!

error in inserting picture into table

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

  1. pgudur

    pgudur Active Member

    Messages:
    18
    Likes Received:
    1
    Trophy Points:
    90
    Here is the code:

    though i created directory as photos
    and i placed all images in photos directory
    but it is keep giving error as non-existent
    so could any help me please.........



    1 CREATE OR REPLACE procedure insert_img
    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.get length(f_photo));
    13 dbms_lob.fileclose(f_photo);
    14* end insert_img;
    SQL> /

    Procedure created.

    SQL> commit;

    Commit complete.

    SQL> execute insert_img(1,'Animallandscape.jpeg');
    BEGIN insert_img(1,'Animallandscape.jpeg'); END;

    *
    ERROR at line 1:
    ORA-22285: non-existent directory or file for FILEOPEN operation
    ORA-06512: at "SYS.DBMS_LOB", line 504
    ORA-06512: at "APPS.INSERT_IMG", line 11
    ORA-06512: at line 1
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    It seems you dont have sufficient grant on the directory to the current user.

    Try
    GRANT READ ON DIRECTORY PHOTOS TO <Username> form the system.

    And then try SELECT statement .
     
  3. pgudur

    pgudur Active Member

    Messages:
    18
    Likes Received:
    1
    Trophy Points:
    90
    Help me!! Error in inserting pictures into table

    Hi all,
    here is the procedure I hope this is good
    but
    still i am getting same error
    i am sure pictures reside in photos directory as 'C:\myfiles'

    CAN ANY ONE TELL ME where i am doing wrong here PLEASE...............

    SQL> create or replace directory photos as 'C:\myfiles';

    Directory created.

    SQL> commit;

    Commit complete.

    SQL> grant read,write on directory photos to apps;

    Grant succeeded.

    SQL> commit;

    Commit complete.


    SQL> CREATE OR REPLACE procedure insert_img
    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 end insert_img;
    16
    17 /

    Procedure created.

    SQL> commit;

    Commit complete.

    SQL> execute insert_img(1,'Animallandscape.jpeg');
    BEGIN insert_img(1,'Animallandscape.jpeg'); END;

    *
    ERROR at line 1:
    ORA-22285: non-existent directory or file for FILEOPEN operation
    ORA-06512: at "SYS.DBMS_LOB", line 504
    ORA-06512: at "APPS.INSERT_IMG", line 11
    ORA-06512: at line 1
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India

    You might have ...

    Given wrong Directory Name or Path
    Given wrong path (May be spelling or case mismatch)
    Given wrong Slash in the Path
    Given wrong file Name (May be spelling or case mismatch)
    Given not the appropriate Grants (to the Owner of the procedure)

    Please check .. Oracle wont lie :)


    Please
     
  5. djbabu

    djbabu Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Thats Right rajavu ...

    may be check the file 'Animallandscape.jpeg' jpg ?

    Gud Luck.