Can i use a BFILENAME within a cursor for multiple images?

Question

Is it possible to concatenate the .jpg filename in order to upload several images to a table?

I want it to do it with a cursor.

Here is the code:

CREATE OR REPLACE PROCEDURE upload_pics AS

    CURSOR c_id IS
    SELECT DISTINCT id_pic
    FROM id_table;

    r_id           c_id%rowtype;
    v_blob         BLOB;
    v_bfile        BFILE;

BEGIN
    FOR r_id IN c_id LOOP
        v_bfile := bfilename('TMP_DIR', r_id.id_pic || '.jpg');
        dbms_lob.open(v_bfile, dbms_lob.lob_readonly);
        dbms_lob.loadfromfile(v_blob, v_bfile, dbms_lob.getlength(v_bfile));

        UPDATE pic_table
        SET
            picture = v_blob
        WHERE
            id = r_id.id_pic;

        dbms_lob.close(v_bfile);
    END LOOP;
END;

Answer 1

Yes possible, here's is an alternative way which worked for me :

SQL> set serveroutput on;
SQL> Create or Replace Procedure upload_pics As
  v_blob blob := null;

  Function load_blob_from_file( filename varchar2, directoryname varchar2 ) Return blob Is
    filecontent blob := null;
    v_bfile    bfile := bfilename(directoryname, filename);
    v_offset    pls_integer := 1;
    v_var       pls_integer;
  Begin
    v_var := dbms_lob.fileexists(v_bfile);
   if v_var = 1 then   
    dbms_lob.createtemporary(filecontent, true, dbms_lob.session);
    dbms_lob.fileopen(v_bfile, dbms_lob.file_readonly);
    dbms_lob.loadblobfromfile(filecontent,v_bfile,dbms_lob.getlength(v_bfile),v_offset,v_offset);
    dbms_lob.fileclose(v_bfile);
   end if; 
    Return filecontent;
  End;
Begin 
 for r_id in ( 
              select distinct id_pic
                from id_table  
              )
 loop
  v_blob := load_blob_from_file(to_char(r_id.id_pic)||'.jpg', 'TMP_DIR');
 if v_blob is not null then 
  update pic_table set picture = v_blob where id = r_id.id_pic; commit;  
  dbms_output.put_line(to_char(r_id.id_pic)||' is OK');
 end if; 
 end loop; 
 exception when others then dbms_output.put_line(sqlerrm);  
End;
/
SQL> exec upload_pics;