How to automate image storing on oracle database?

user_13468217 0
Viewed 196

I am trying to store images on a database. So I have created lob_table

CREATE TABLE lob_table (id NUMBER, doc BLOB);

CREATE OR REPLACE DIRECTORY my_dir AS 'C:\temp'; 

DECLARE
  src_lob  BFILE := BFILENAME('MY_DIR', 'example.jpg');
  dest_lob BLOB;
BEGIN
  INSERT INTO lob_table VALUES(1, EMPTY_BLOB())
     RETURNING doc INTO dest_lob;

  DBMS_LOB.OPEN(src_lob, DBMS_LOB.LOB_READONLY);
  DBMS_LOB.LoadFromFile( DEST_LOB => dest_lob,
                         SRC_LOB  => src_lob,
                         AMOUNT   => DBMS_LOB.GETLENGTH(src_lob) );
  DBMS_LOB.CLOSE(src_lob);

  COMMIT;
END;

But in the directory about 90000 photos and each photo stored with its id name like a "1.jpg". How I can automate the process?

oracle11g blob plsql oracle
1 Answers

You can loop through for an integer set starting from 1 upto an upper bound of 100,000 as an arbitrary greater value than 900,000 in order to cover all those files, while checking out the existence of each file in order to skip if there's no file for any value of integers between that interval such as

DECLARE
  src_lob  BFILE;
  dest_lob BLOB;
  v_exists INT;
BEGIN
  FOR i IN 1..100000
  LOOP
   BEGIN  
      src_lob  := BFILENAME('MY_DIR', i||'.jpg');
      v_exists := dbms_lob.fileexists(src_lob);
      IF v_exists = 1 THEN
        INSERT INTO lob_table VALUES(i, EMPTY_BLOB()) RETURNING doc INTO dest_lob;

        DBMS_LOB.OPEN(src_lob, DBMS_LOB.LOB_READONLY);
        DBMS_LOB.LOADFROMFILE( DEST_LOB => dest_lob,
                               SRC_LOB  => src_lob,
                               AMOUNT   => DBMS_LOB.GETLENGTH(src_lob) );
        DBMS_LOB.CLOSE(src_lob);
        IF i MOD 1000 = 0 THEN COMMIT; END IF;
      END IF;
     EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); 
    END;  
  END LOOP;
    
  COMMIT;
END;
/
user_58413060
Related