Scripts to test lob usage on Oracle

Read Blob from database and write to a local file

i needed a method to test the throughput of a system when working with blob data.    A lot of finger pointing occurred at the application middleware, but i was concerned the underlying database, network and/or SAN architecture was at fault.  Hence writing a simple db resident script to do the work of the middleware whose main content fields have Blob/clob definitions.

Steps

1. Define the Stored Procedures
2. Create a table
3. Connect a client – if applicable, run this locally on the db server to remove any client network latency.

 

create stored procedure

CREATE OR REPLACE PROCEDURE kate.WriteBLOBToFILE (itemId IN NUMBER,v_dir varchar2,runno IN number) IS
  v_blob         BLOB;
  blob_length    INTEGER;
  out_file       UTL_FILE.FILE_TYPE;
  v_buffer       RAW(32767);
  chunk_size     BINARY_INTEGER := 32767;
  blob_position  INTEGER := 1;
BEGIN
  -- Retrieve the BLOB for reading
  --SELECT item_data INTO v_blob FROM pritemdata WHERE data_id = (select ContId;
    SELECT  item_data INTO v_blob FROM pritemdata WHERE data_id in (select data_id from prdatareference where item_id=itemId and data_type=1);

  -- Retrieve the SIZE of the BLOB
  blob_length:=DBMS_LOB.GETLENGTH(v_blob);

  -- Open a handle to the location to write the BLOB to file
  out_file := UTL_FILE.FOPEN (v_dir,itemId||'_'||runno , 'wb', chunk_size);

  -- Write the BLOB to file in chunks
  WHILE blob_position <= blob_length LOOP
    IF blob_position + chunk_size - 1 > blob_length THEN
      chunk_size := blob_length - blob_position + 1;
    END IF;
    DBMS_LOB.READ(v_blob, chunk_size, blob_position, v_buffer);
    UTL_FILE.PUT_RAW(out_file, v_buffer, TRUE);
    blob_position := blob_position + chunk_size;
  END LOOP;

  -- Close the file handle
  UTL_FILE.FCLOSE (out_file);
END;

Write lob into database

1. Create table

CREATE TABLE BLOB_DATA
(
  ID         NUMBER,
  BLOB_DATA  BLOB,
  PRTIME     TIMESTAMP(6)  DEFAULT SYSTIMESTAMP
)

2. Create Stored Procedure

requirements – grant execute on dbms_lock;

CREATE OR REPLACE PROCEDURE AESUSER.insertBLOB (src_dir VARCHAR2,src_filename IN VARCHAR2,runno IN NUMBER,sleepinterval IN NUMBER) IS

--exec insertBLOB3('DUMPDIR','test.jpg',5,1);

      bfile_offset NUMBER ;
      blob_offset NUMBER;
    v_file_loc       BFILE;
    v_srcfile    BLOB;
    v_srcfile_size   INTEGER;
     lob_fileexists       BOOLEAN := FALSE;

    BEGIN
 DBMS_OUTPUT.ENABLE(100000);
    v_file_loc := BFILENAME(src_dir,src_filename);
     lob_fileexists := DBMS_LOB.FILEEXISTS(v_file_loc) = 1;

      IF  lob_fileexists  THEN
          v_srcfile_size := DBMS_LOB.GETLENGTH(v_file_loc);
            DBMS_OUTPUT.PUT_LINE(src_dir|| '\' ||src_filename);
            DBMS_OUTPUT.PUT_LINE('FILE SIZE: ' ||  v_srcfile_size);
            DBMS_OUTPUT.PUT_LINE('Repetitions: ' || runno);
            DBMS_OUTPUT.PUT_LINE('Sleep INTERVAL: ' || sleepinterval);

                  for i in 1..runno loop
                    bfile_offset  :=1;
                    blob_offset  :=1;
                         INSERT INTO blob_data VALUES (i,EMPTY_BLOB(),systimestamp) returning blob_data into v_srcfile;
                         dbms_lob.fileopen(v_file_loc, dbms_lob.file_readonly);
                        dbms_lob.OPEN( v_srcfile, dbms_lob.lob_readwrite);
                       DBMS_LOB.LOADBLOBFROMFILE (
                               dest_lob   => v_srcfile,
                               src_bfile  => v_file_loc,
                               amount       =>  dbms_lob.lobmaxsize,
                               dest_offset => bfile_offset,
                               src_offset   => blob_offset);
                         dbms_lob.close(v_srcfile);
                         dbms_lob.fileclose(v_file_loc);
                         dbms_lock.sleep(sleepinterval);
                           commit;
                            end loop;
              ELSE
                DBMS_OUTPUT.PUT_LINE('ERROR.  Supplied FILE Name  '||src_dir|| '\' ||src_filename|| ' Does NOT exist');

         END IF;
         EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('Error:' || SQLERRM);
        dbms_lob.close(v_srcfile);
        dbms_lob.fileclose(v_file_loc);
   END;
/

3. Run Script.

sql> insertblob [oracle_directory],[filename],reptitions,interval);

• oracle_directory – name of directory within the database
• Filename – name of test file to import
• repetitions – qty of times to repeat test
• interval – sleep time between each repetition

remember to set serveroutput on if you wish a report at run end.

SQL> SET serveroutput ON

Success

SQL> EXEC insertBLOB('DUMPDIR','test.jpg',1,0);
DUMPDIR\test.jpg
FILE SIZE: 4637515
Repetitions: 1
Sleep INTERVAL: 0

failure

SQL> EXEC insertBLOB('DUMPDIR','testxxxxxx.jpg',1,0);
ERROR.  Supplied FILE DUMPDIR\testxxxxxx.jpg Does NOT existRead Lob FROM DATABASE