6 Replies Latest reply: Aug 27, 2013 1:20 PM by Rajat RSS

    loading a picture or stream in BLOB

    Rajat

      Dear experts,

       

      I am able to update a BFILE field like the below example

       

       

      update emp set emp_photo=BFILENAME('IMAGE_DIR','Tired.JPG') where empno='7566';

       

      But is there any simple way to update BLOB field with a picture.

      What i can find in my book is a procedure.

       

      CREATE OR REPLACE PROCEDURE loadLOBFromBFILE_proc

      (p_dest_loc IN OUT BLOB, p_file_name IN VARCHAR2,

      p_file_dir IN VARCHAR2)

      IS

      v_src_loc BFILE := BFILENAME(p_file_dir, p_file_name);

      v_amount INTEGER := 4000;

      BEGIN

      DBMS_LOB.OPEN(v_src_loc, DBMS_LOB.LOB_READONLY);

      v_amount := DBMS_LOB.GETLENGTH(v_src_loc);

      DBMS_LOB.LOADFROMFILE(p_dest_loc, v_src_loc, v_amount);

      DBMS_LOB.CLOSE(v_src_loc);

      END loadLOBFromBFILE_proc;

       

      is there any update statement like the afore mentioned update statement of BFILENAME which will work for BLOB.

       

      Regards

      Rajat

        • 1. Re: loading a picture or stream in BLOB
          Rajat

          Any body . Please.

           

          Regards

          Rajat

          • 2. Re: loading a picture or stream in BLOB
            Karthick_Arp

            Like this?

             

            SQL> create table t
              2  (
              3    no integer primary key,
              4    col blob
              5  )
              6  /

            Table created.

            SQL> insert into t values (1, null);

            1 row created.

            SQL> commit;

            Commit complete.

             

            SQL> declare
              2      l_col    blob;
              3      l_bfile  bfile;
              4  begin
              5      update t
              6         set col = empty_blob
              7       where no = 1 returning col into l_col;
              8      l_bfile := bfilename( 'KARDIR', 'test.gif' );
              9      dbms_lob.fileopen(l_bfile);
            10      dbms_lob.loadfromfile
            11      (
            12         l_col
            13       , l_bfile
            14       , dbms_lob.getlength(l_bfile)
            15      );
            16      dbms_lob.fileclose(l_bfile);
            17  end;
            18  /

            PL/SQL procedure successfully completed.

            • 3. Re: loading a picture or stream in BLOB
              Rajat

              Dear karthick,

               

              Thank you very much for your reply. But i was wondering if there is any method which can directly insert a picture or stream in the BLOB column.

               

              Like an insert statement.like

               

              insert into emp_pic ( emp_no,emp_pic) values(100,bfilename( 'KARDIR', 'test.gif' ));

               

              Thanks for your response.

               

              or

               

              What's wrong with this approach

               

              update emp set

               

              emp_db_photo= DBMS_LOB.LOADFROMFILE(EMP_DB_PHOTO,

               

                                                  BFILENAME('IMAGE_DIR','Bridge.JPG'),

               

                                                  DBMS_LOB.GETLENGTH(BFILENAME('IMAGE_DIR','Bridge.JPG'))

               

                                                  )

               

              where empno='7698';

               

               

              Regards

               

              Rajat


              • 4. Re: loading a picture or stream in BLOB
                Rajat

                Any body. Please.

                 

                Regards

                Rajat


                • 5. Re: loading a picture or stream in BLOB
                  Barbara Boehmer

                  There is  no built-in way to insert or update a blob column from a bfile using just SQL without PL/SQL.  What is wrong with your attempted method is that you are trying to use a procedure in a SQL insert or update statement.  You need to use a function in a SQL insert or update statement.  The following demonstrates how to create your own generic bfile_to_blob function and use it in a SQL insert or update statement.

                   

                  SCOTT@orcl12c> ALTER TABLE emp ADD (emp_db_photo BLOB)

                    2  /

                   

                  Table altered.

                   

                  SCOTT@orcl12c> CREATE OR REPLACE DIRECTORY image_dir AS 'c:\my_oracle_files'

                    2  /

                   

                  Directory created.

                   

                  SCOTT@orcl12c> CREATE OR REPLACE FUNCTION bfile_to_blob

                    2    (p_bfile     IN BFILE)

                    3    RETURN           BLOB

                    4  AS

                    5    v_src_file      BFILE := p_bfile;

                    6    v_amount        INTEGER;

                    7    v_dest_lob      BLOB;

                    8  BEGIN

                    9    DBMS_LOB.OPEN (v_src_file, DBMS_LOB.LOB_READONLY);

                  10    v_amount := DBMS_LOB.GETLENGTH (v_src_file);

                  11    DBMS_LOB.CREATETEMPORARY (v_dest_lob, FALSE, DBMS_LOB.CALL);

                  12    DBMS_LOB.LOADFROMFILE (v_dest_lob, v_src_file, v_amount);

                  13    DBMS_LOB.CLOSE (v_src_file);

                  14    RETURN v_dest_lob;

                  15  END bfile_to_blob;

                  16  /

                   

                  Function created.

                   

                  SCOTT@orcl12c> SHOW ERRORS

                  No errors.

                  SCOTT@orcl12c> INSERT INTO emp (empno, deptno, emp_db_photo)

                    2  VALUES (99, 40, bfile_to_blob (BFILENAME ('IMAGE_DIR', 'Bridge.JPG')))

                    3  /

                   

                  1 row created.

                   

                  SCOTT@orcl12c> UPDATE emp

                    2  SET    emp_db_photo = bfile_to_blob (BFILENAME ('IMAGE_DIR','Bridge.JPG'))

                    3  WHERE  empno = 7698

                    4  /

                   

                  1 row updated.

                   

                  SCOTT@orcl12c> COMMIT

                    2  /

                   

                  Commit complete.

                   

                  SCOTT@orcl12c> SELECT empno, DBMS_LOB.GETLENGTH (emp_db_photo)

                    2  FROM   emp

                    3  WHERE  emp_db_photo IS NOT NULL

                    4  /

                   

                       EMPNO DBMS_LOB.GETLENGTH(EMP_DB_PHOTO)

                  ---------- --------------------------------

                        7698                           511500

                          99                           511500

                   

                  2 rows selected.

                  • 6. Re: loading a picture or stream in BLOB
                    Rajat

                    Dear Barbara,

                     

                    Thanks a lot , It was a please to see answer  what actually i asked. Really great full for your wonderful thought,

                     

                    Regards

                    Rajat