interMedia - Import-Export of Images

This article is an update of my original Oracle8i article (Read Article). Since the original article was written Oracle have included full support for importing data from the filesystem, and limited export support, making the use of Java stored procedures unnecessary.

This process can be broken down into the following steps:
·    Create Schema Objects
·    Grant Privileges
·    Import Image
·    Query Image
·    Export Image
Assuming you have installed Oracle interMedia on your server, the first thing you will need to do is create a table to store the images.


Create Schema Objects
First we create a table to hold the images and a directory object:

CREATE TABLE images (
  file_name VARCHAR2(100) NOT NULL,
  image     ORDSYS.OrdImage
);

CREATE OR REPLACE DIRECTORY images AS '/u01/app/oracle/';

The FILE_NAME column could be omitted, making this an object table, as the OrdImage type contains a file name property. The IMAGE column is defined using the OrdImage type found in the ORDSYS schema. ORDSYS is the owner of all the interMedia code and default data. The OrdImage type decends from the OrdSource type which contains a BLOB that is used to store the image data.

Grant Privileges
By default, JServer (Oracle's Java Virtual Machine) does not allow access to the file system. The DBMS_JAVA package can be used to grant assorted privileges to JServer.

EXEC DBMS_JAVA.grant_permission('SCHEMA-NAME', 'java.io.FilePermission', '<>', 'read ,write, execute, delete');
EXEC DBMS_JAVA.grant_permission('SCHEMA-NAME', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
EXEC DBMS_JAVA.grant_permission('SCHEMA-NAME', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
GRANT JAVAUSERPRIV TO SCHEMA-NAME;


It is up to the individual to decide on the level of access that is required.

Import Image
Images can be imported using the OrdImage.import method:

DECLARE
  l_image      ORDSYS.ORDImage;
  l_ctx        RAW(4000)    := NULL;
  l_file_name  VARCHAR2(10) := 'Test1.gif';
BEGIN
  INSERT INTO images (file_name, image)
  VALUES (l_file_name, ORDSYS.ORDImage.init());
 
  SELECT image
  INTO   l_image
  FROM   images
  WHERE  file_name = l_file_name
  FOR UPDATE;
 
  -- Import the image into the database
  l_image.importFrom(l_ctx, 'file', 'IMAGES', 'Test2.gif');

  UPDATE images
  SET    image     = l_image
  WHERE  file_name = l_file_name;
  COMMIT;
END;
/
Query Image
Information about the stored image can be retrieved using:

SELECT i.image.getContentLength(),
       i.image.getSourceType(),
       i.image.getSourceLocation(),
       i.image.getSource(),
       i.image.getSourceName(),
       i.image.getHeight(),
       i.image.getWidth(),
       i.image.getFileFormat(),
       i.image.getContentFormat(),
       i.image.getCompressionFormat(),
       i.image.getUpdateTime(),
       i.image.getMimeType()
FROM   images i;
Export Image
Although the documentation claims that export of files is now supported I am unable to get this feature to work. Instead I use the new features of UTL_FILE:

DECLARE
  l_file_name  VARCHAR2(10) := 'Test1.gif';
  l_file       UTL_FILE.file_type;
  l_buffer     RAW(32767);
  l_amount     BINARY_INTEGER := 32767;
  l_pos        INTEGER := 1;
  l_blob       BLOB;
  l_blob_len   INTEGER;
BEGIN
  -- Get LOB locator
  SELECT i.image.getContent()
  INTO   l_blob
  FROM   images i
  WHERE  i.file_name = l_file_name;

  l_blob_len := DBMS_LOB.GETLENGTH(l_blob);
 
  -- Open the destination file.
  l_file := UTL_FILE.FOPEN('IMAGES',l_file_name,'w', 32767);

  -- Read chunks of the BLOB and write them to the file
  -- until complete.
  WHILE l_pos < l_blob_len LOOP
    DBMS_LOB.READ (l_blob, l_amount, l_pos, l_buffer);
    UTL_FILE.PUT_RAW(l_file, l_buffer, TRUE);
    l_pos := l_pos + l_amount;
  END LOOP;
 
  -- Close the file.
  UTL_FILE.FCLOSE(l_file);
 
EXCEPTION
  WHEN OTHERS THEN
    -- Close the file if something goes wrong.
    IF UTL_FILE.IS_OPEN(l_file) THEN
      UTL_FILE.FCLOSE(l_file);
    END IF;
    RAISE;
END;
/