How to automate image storing on oracle database?

Question

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?

When you say "in the file" do you mean "in the directory"? If you want to process every file in a directory, I'd use SQL*Loader rather than a PL/SQL. https://oracle-base.com/articles/10g/load-lob-data-using-sql-loader You could turn the anonymous block into a stored procedure and then script out thousands of calls to that procedure from the output of a `ls` or `dir` command. I've used a Java stored procedure in the past to return every file in a directory through a query. You could also use an external table with a preprocessor script. — Justin Cave, May 01 '21 at 04:00
Larry Ellison (Oracle's owner) does a little dance every time he sees a question like this. Delivering images directly from database LOBs is probably the most expensive way to do it. Oracle licenses cost real money and LOB retrieval isn't very fast compared to other database operations. You're much better off storing the images in a file system and their file paths in the database. Seriously. — O. Jones, May 01 '21 at 11:47

score 1 · Accepted Answer · answered May 01 '21 at 11:37

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;
/

How to automate image storing on oracle database?

1 Answers1