6 Replies Latest reply: Jul 30, 2012 2:36 AM by Siddik RSS

    insert image in blob field in oracle

    Siddik
      Hi,
      I am getting below eror to insert image file in bloc field.


      ORA-22288: file or LOB operation FILEOPEN failed
      The system cannot find the path specified.
      ORA-06512: at "SYS.DBMS_LOB", line 744

      below are my SP:

      CREATE OR REPLACE PROCEDURE DRFSMS.iPAD_Image_Load(vParcel_ID IN VARCHAR2, vSerial_No IN NUMBER, vFileName IN VARCHAR2) IS
      l_source BFILE;
      l_dest BLOB;
      l_length BINARY_INTEGER;
      BEGIN

      l_source := BFILENAME ('PHOTO_DIR', vFileName);

      INSERT INTO WS_PARCEL_SS_IMAGES (Parcel_ID, Serial_No,Image_File) VALUES (vParcel_ID,vSerial_No, EMPTY_BLOB () )
      RETURNING Image_File INTO l_dest;
      -- lock record
      SELECT Image_File INTO l_dest FROM WS_PARCEL_SS_IMAGES WHERE PARCEL_ID = vParcel_ID AND Serial_No= vSerial_No FOR UPDATE;
      -- open the file
      DBMS_LOB.fileopen (l_source, DBMS_LOB.file_readonly);
      -- get length
      l_length := DBMS_LOB.getlength (l_source);
      -- read the file and store in the destination
      DBMS_LOB.loadfromfile (l_dest, l_source, l_length);
      -- update the blob field with destination
      UPDATE WS_PARCEL_SS_IMAGES SET Image_File = l_dest WHERE PARCEL_ID = vParcel_ID AND Serial_No= vSerial_No;
      -- close file
      DBMS_LOB.fileclose (l_source);
      END iPAD_Image_Load;

      belows are the inputs:

      DECLARE
      VPARCEL_ID VARCHAR2(32767);
      VSERIAL_NO NUMBER;
      VFILENAME VARCHAR2(32767);

      BEGIN
      VPARCEL_ID := 'N00O-240';
      VSERIAL_NO := 2;
      VFILENAME := 'photo_dir/N00O-222_1.jpg';

      DRFSMS.IPAD_IMAGE_LOAD ( VPARCEL_ID, VSERIAL_NO, VFILENAME );
      COMMIT;
      END;

      pls. anyone please help me.
      advance thnx.

      Regards,

      Siddik

      Edited by: Siddik on Jul 30, 2012 12:06 PM
        • 1. Re: insert image in blocl field in oracle
          odie_63
           VFILENAME := 'photo_dir/N00O-222_1.jpg';
          should be :
           VFILENAME := 'N00O-222_1.jpg';
          And you're doing a lot of unneeded stuff too.
          The whole procedure can be simplified to :
          CREATE OR REPLACE PROCEDURE DRFSMS.iPAD_Image_Load (
            vParcel_ID in varchar2
          , vSerial_No in number
          , vFileName  in varchar2
          ) 
          IS
          
            l_source bfile := bfilename ('PHOTO_DIR', vFileName);
            l_dest   blob;
          
          BEGIN
          
            insert into ws_parcel_ss_images (Parcel_ID, Serial_No, Image_File) 
            values (vParcel_ID, vSerial_No, EMPTY_BLOB())
            returning Image_File into l_dest;
          
            DBMS_LOB.fileopen (l_source, DBMS_LOB.file_readonly);
            DBMS_LOB.loadfromfile (l_dest, l_source, DBMS_LOB.getlength (l_source));
            DBMS_LOB.fileclose (l_source);
            
          END iPAD_Image_Load;
          - You don't have to SELECT FOR UPDATE, you're already holding a lock on the row since you've just inserted it.
          - You don't have to perform an UPDATE either, DBMS_LOB.loadFromFile writes directly in the column via the LOB locator.
          • 2. Re: insert image in blocl field in oracle
            ShankarViji
            Hi Siddik,

            Welcome to Forum !!!!

            Check that you have given the filename with our Spell Error.

            You can Check that the File Exists in the Directory Object Specified as,
            SELECT DBMS_LOB.fileexists (BFILENAME ('PHOTO_DIR',
                                                   'filename.jpg'
                                                  )
                                       )
              FROM DUAL;
            Returns 1 - If the File Specified exists in the Directory.
            0 - If the File does not exists.

            Thanks,
            Shankar
            • 3. Re: insert image in blob field in oracle
              Siddik
              Thanx a lot for your reply.
              but have tried same as same code you have provide, but i got error as bwlow:

              ORA-22288: file or LOB operation FILEOPEN failed
              The system cannot find the path specified.
              ORA-06512: at "SYS.DBMS_LOB", line 744

              please help me where i make wrong.

              CREATE OR REPLACE PROCEDURE DRFSMS.iPAD_Image_Load(vParcel_ID IN VARCHAR2, vSerial_No IN NUMBER, vFileName IN VARCHAR2) IS
              l_source BFILE;
              l_dest BLOB;
              l_length BINARY_INTEGER;
              BEGIN

              l_source := BFILENAME ('PHOTO_DIR', vFileName);

              INSERT INTO WS_PARCEL_SS_IMAGES (Parcel_ID, Serial_No,Image_File) VALUES (vParcel_ID,vSerial_No, EMPTY_BLOB () )
              RETURNING Image_File INTO l_dest;
              -- open the file
              DBMS_LOB.fileopen (l_source, DBMS_LOB.file_readonly);
              -- get length
              l_length := DBMS_LOB.getlength (l_source);
              -- read the file and store in the destination
              DBMS_LOB.loadfromfile (l_dest, l_source,DBMS_LOB.getlength (l_source));
              -- close file
              DBMS_LOB.fileclose (l_source);
              END iPAD_Image_Load;

              Edited by: Siddik on Jul 30, 2012 12:07 PM

              Edited by: Siddik on Jul 30, 2012 12:07 PM
              • 4. Re: insert image in blob field in oracle
                JustinCave
                How did you define PHOTO_DIR (what was the CREATE DIRECTORY statement)?
                Is the path for PHOTO_DIR a directory that exists on the server (not on your client machine)?
                Does the operating system user that runs the Oracle database on the server have access to that directory on the server? Can you log in to the server as the operating system user that runs the Oracle database and open the file on the server?

                Justin
                • 5. Re: insert image in blob field in oracle
                  Siddik
                  Thank you very much Justin.

                  I was wrong as I had create the directory ion my client machine.

                  Now i have created the directory on sarever machine and it works perfect.
                  All images are inserted.

                  Thanx a lot once again.

                  Best Regards,

                  Siddik
                  • 6. Re: insert image in blob field in oracle
                    Siddik
                    hi shankar,

                    thanks a lot for your help.

                    REgards,

                    Siddik