Loading Image in Oracle HRMS

How To Load BLOB Data Into the Database via PL/SQL

   1.  Create a table TEST_BLOB.

      DROP TABLE test_blob; 

      CREATE TABLE test_blob ( 
        id   NUMBER, 
        data BLOB 
      ) 
      /

   2. Create a procedure INSERT_BLOB.

      CREATE OR REPLACE PROCEDURE insert_blob (in_id IN INTEGER, in_data IN BLOB)
      AS
        -- Max Buffer Size = 32767
        buffersize CONSTANT INTEGER := 32767;

        v_data         BLOB;
        v_data_offset  BINARY_INTEGER := 1;
        v_data_remain  INTEGER;
        v_data_buffer  RAW(32767);
        v_data_amount  INTEGER;
         
      BEGIN
        
        INSERT INTO test_blob VALUES (in_id, EMPTY_BLOB())
          RETURNING data INTO v_data;
        
        v_data_remain := DBMS_LOB.GETLENGTH(in_data);
        DBMS_OUTPUT.PUT_LINE('Size of BLOB: ' || v_data_remain || ' bytes');
        
        WHILE v_data_remain > 0 LOOP

          -- Calculate the size of the buffer    
          v_data_amount := LEAST(buffersize, v_data_remain);

          -- Read a chunk of the BLOB passed in
          DBMS_LOB.READ(in_data, v_data_amount, v_data_offset, v_data_buffer);

          -- Write the chunk to the interal BLOB inside the database
          DBMS_LOB.WRITE(v_data, v_data_amount, v_data_offset, v_data_buffer);

          -- Calculate remaining BLOB data
          v_data_remain := v_data_remain - v_data_amount;

          -- Calculate next BLOB offset
          v_data_offset := v_data_offset + v_data_amount;

          DBMS_OUTPUT.PUT_LINE('Amount Read/Written: ' || (v_data_offset - 1) || ' bytes');    

        END LOOP;  

      END;
      /

   3. Create a large BLOB and call procedure INSERT_BLOB.

      SET serveroutput ON

      DECLARE

        test_blob   BLOB;
        test_data   RAW(100) := UTL_RAW.CAST_TO_RAW('ABCDEFGHIJ1234567890abcdefghij');
        chunk_size  BINARY_INTEGER;

      BEGIN 

        chunk_size := UTL_RAW.LENGTH(test_data); 
        DBMS_OUTPUT.PUT_LINE('Size of Chunk: ' || chunk_size || ' bytes');
        
        -- Create a Temporary BLOB
        DBMS_LOB.CREATETEMPORARY(test_blob, false);

        -- Fill the BLOB with 50,000 test_data sized chunks
        FOR i IN 1..50000 LOOP
          DBMS_LOB.WRITE(test_blob, chunk_size, ((i-1)*chunk_size)+1, test_data);
        END LOOP;

        -- Report the length of the BLOB
        DBMS_OUTPUT.PUT_LINE('Length of TEST_BLOB: ' || DBMS_LOB.GETLENGTH(test_blob) || ' bytes');

        -- Insert the BLOB into the database
        DBMS_OUTPUT.PUT_LINE('Calling INSERT_BLOB...');
        insert_blob(1, test_blob);

      END;
      /

   4. Review the OUTPUT below.

      Size of Chunk: 30 bytes
      Length of TEST_BLOB: 1500000 bytes

      Calling INSERT_BLOB...

      Size of BLOB: 1500000 bytes

      Amount Read/Written: 32767 bytes
      Amount Read/Written: 65534 bytes
      Amount Read/Written: 98301 bytes
      Amount Read/Written: 131068 bytes
 

      PL/SQL procedure successfully completed.

   5. Verify that the BLOB was successfully inserted.

      SQL> SELECT DBMS_LOB.GETLENGTH(data) FROM test_blob;

      DBMS_LOB.GETLENGTH(DATA)
      ------------------------
                       1500000

      SQL>

Additional Notes

    * You can use procedure INSERT_BLOB to also load CLOBs into the database.  That is because Binary data is a superset of Character data.  Hence all character data can be represented by binary code points.

    * You cannot use procedure INSERT_CLOB to load BLOBs into the database.  Binary data is not a subset of Character data.  All binary data cannot be represented in character code points.

    * When creating a test BLOB you must use the UTL_RAW.LENGTH function rather than the standard LENGTH function.  UTL_RAW.LENGTH returns the size in BYTES of a RAW datatype.  The LENGTH function returns the size in CHARACTERS of character datatypes.

"Team Work" More We Less Me.

         Sameer Patil