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!

Insert images into oracle database

Discussion in 'SQL PL/SQL' started by amy85, Mar 29, 2009.

  1. amy85

    amy85 Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Hi friends,

    I have a requirement where against some data, some images need to be stored. How can one store images in oracle tables? or is it at all possible?

    If i wanted to upload an image as a CLOB how to go about it?

    Thanks
     
  2. sameer

    sameer Forum Advisor

    Messages:
    105
    Likes Received:
    6
    Trophy Points:
    240
    Hmmm first step would be to create a directory in oracle and map it to the folder where your image resides.

    create directory image_dir as 'c:\image_dir';

    Then you would have to use a procedure to insert the image in your table. SO first create a table to hold the image. Note that you have to use a BLOB to insert the image.
    Code (Text):

    CREATE TABLE test_image
     (
     ID NUMBER,
     image_filename VARCHAR2(50),
     image BLOB
     );
     
    Now let's write the procedure to insert the image in the table above.
    Code (Text):

    CREATE OR REPLACE PROCEDURE insert_image_file (p_id NUMBER, p_image_name IN VARCHAR2)
    IS
       src_file   BFILE;
       dst_file   BLOB;
       lgh_file   BINARY_INTEGER;
    BEGIN
       src_file := BFILENAME ('image_DIR', p_image_name);

       -- insert a NULL record to lock
       INSERT INTO temp_image
                   (ID, image_name, image
                   )
            VALUES (p_id, p_image_name, EMPTY_BLOB ()
                   )
         RETURNING image
              INTO dst_file;

       -- lock record
       SELECT     image
             INTO dst_file
             FROM temp_image
            WHERE ID = p_id AND image_name = p_image_name
       FOR UPDATE;

       -- open the file
       DBMS_LOB.fileopen (src_file, DBMS_LOB.file_readonly);
       -- determine length
       lgh_file := DBMS_LOB.getlength (src_file);
       -- read the file
       DBMS_LOB.loadfromfile (dst_file, src_file, lgh_file);

       -- update the blob field
       UPDATE temp_image
          SET image = dst_file
        WHERE ID = p_id AND image_name = p_image_name;

       -- close file
       DBMS_LOB.fileclose (src_file);
    END insert_image_file;
     
    Now execute the procedure to insert the image.
    Code (Text):

    EXECUTE insert_image_file(1,'test_image.jpg');
     
    That's it!
     
    amy85 likes this.
  3. amy85

    amy85 Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Yes it is working perfectly.
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Also be aware of most common Exceptions for better error handling As available from psoug


    [table]Error Code| Reason
    ORA-21560 |The argument is expecting a non-null, valid value but the argument value passed in is null, invalid, or out of range
    ORA-22285 |The directory leading to the file does not exist
    ORA-22286 |user does not have the necessary access privileges on the directory alias and/or file
    ORA-22287 |directory alias is not valid
    ORA-22288 |file operation failed
    ORA-22288 |The file is not open for the required operation
    ORA-22290 | open files has reached the maximum limit
    ORA-22925 |operation exceeds maximum lob size [/table]
     
  5. debasisdas

    debasisdas Active Member

    Messages:
    46
    Likes Received:
    3
    Trophy Points:
    90
    Location:
    Bangalore, India
    The following link just might be helpful.

    link
     
  6. princess

    princess Guest

    hi.. i m trying to execute the procedure and inserting any image...
    are u sure v dont hav to giv d image path while insertinng image???
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Not if you are using the code posted in this thread -- you're actually storing the image data in the table. Also you've created a directory in the database which 'points' to the actual file location so that provides the path for the file open and read operations. If you were using a BFILE column rather than a BLOB then you would need the path -- the LOB locater would use it to identify the correct image file.
     
  8. predi

    predi Guest

    but how to retrieve those images.....
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Select will retrieve them -- using Forms it will display the image but it's a basic query, nothing fancy. Actually most GUIs will display images selected from a database table, if I remember correctly.
     
  10. nisha2905

    nisha2905 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    75
    Hi, I am able to insert an image in a table. Now from the same table i want to insert the image in per_images of Oracle Apps HRMS ver 11.5.

    The problem is of data type incompatibility. per_images has long raw data type where as my staging table has BLOB data type.

    Can any one help me that how can i insert image from blob to long raw data type.
    I am using "bms_load.read(lob_loc in blob,amount in out binary_integer,offset in integer,buffer out raw)" this but not successeded yet.
     
  11. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Oracle is usually pretty good about providing APIs for inserting data into its core Application tables. Have you looked at the Tech Ref?


    CJ
     
  12. nisha2905

    nisha2905 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    75
    Hi, There is no API for Imgae insertion in oracle apps. I hv also raised a SR for the same. Tey suggested that i may use dbms_lob.read() to insert an image. My question is that this is not working at all. I m not able to insert an image from a BLOB data type to a long raw data type (as 11.5 has image data type of Long Raw).

    So can any one help in this, that how can i insert full image into per_images table.
     
  13. Obyys

    Obyys Guest

    Hi in this Blog, i created the above table as described.... but when i run the procedure as above, it gives an error via..... temp_image and image_name does not exist.......
    Does this mean i have to create the following tables, or is it a typographical error mistaken for test_image and image_filename?
     
  14. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It appears to be a typographical error; temp_image should be test_image and image_name should be image_filename. This is what happens when example code is posted without first testing it.
     
    Obyys likes this.
  15. aclink

    aclink Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    i am going to try those method, thank you for giving such sample codes.
     
  16. aadityainpccs

    aadityainpccs Active Member

    Messages:
    52
    Likes Received:
    2
    Trophy Points:
    160
    Location:
    New delhi (INDIA)
    Hello I am trying it but getting an error as: ORA-22285: non-existent directory or file for FILEOPEN operation
    ORA-06512: at "SYS.DBMS_LOB", line 744
    ORA-06512: at "SCOTT.INSERT_IMAGE_FILE", line 25
    ORA-06512: at line 1
    22285. 00000 - "non-existent directory or file for %s operation"
    *Cause: Attempted to access a directory that does not exist, or attempted
    to access a file in a directory that does not exist.
    *Action: Ensure that a system object corresponding to the specified
    directory exists in the database dictionary, or
    make sure the name is correct.
    please Help
     
  17. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    The error is telling you what the problem is...either the path to the file is incorrect, not given or not set in the environment...or the name of the file you are using doesn't match the name of the file on the system.

    CJ
     
  18. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Post the code you are using; it would be helpful to see what Oracle is trying to access.
     
  19. venu57

    venu57 Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    Bangalore
    Hi Sameer,

    As you suggested above, i have created a table and inserted the images. Now i need to send that images in an email.

    Please suggest me how to do that??

    Thanks.
     
  20. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi,

    I completely followed the steps as you've mentioned for inserting an image into an Oracle table., But I'm facing an error below:

    Code (SQL):

    Error starting at line 57 IN command:
    EXECUTE insert_image_file(1,'test_image.jpg')
    Error report:
    ORA-22288: file OR LOB operation FILEOPEN failed
    No such file OR directory
    ORA-06512: at "SYS.DBMS_LOB", line 523
    ORA-06512: at "TEST_USER.INSERT_IMAGE_FILE", line 26
    ORA-06512: at line 1
    22288. 00000 -  "file or LOB operation %s failed\n%s"
    *Cause:    The operation attempted ON the file OR LOB failed.
    *Action:   See the NEXT error message IN the error stack FOR more detailed
               information.  Also, verify that the file OR LOB EXISTS AND that
               the necessary privileges are SET FOR the specified operation. IF
               the error still persists, report the error TO the DBA.
     
    Could U help me in rectifying the same?