8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
InterMedia - Import-Export Of Images
Oracle interMedia allows Image, Audio and Video data to be stored in the database. Oracle seem to have focused most of their attention on getting the data in, assuming that all content will be served directly from the database. There is currently no direct support for data export from interMedia, all export routines being merely "stubs" for future development. Instead, the user is required to code custom routines for data export. The following code fragments show how to implement image Import and Export using "Oracle 8.1.5-7, interMedia, Java Stored Procedures and PL/SQL".
This process can be broken down into the following steps:
- Create a Table to store the images.
- Load interMedia Classes into your schema.
- Create a Java Stored Procedure to do the work.
- Create ProcessImages Specification to publish the Java call specification.
- Create ProcessImages Body to allow PL/SQL to access additional interMedia methods.
- Grant Privileges to give JServer access to the filesystem.
- Import an Image and query it's properties.
- Export an Image.
- Possible Modifications.
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 Table
CREATE TABLE images ( file_name VARCHAR2(100) NOT NULL, image ORDSYS.OrdImage );
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.
Although imports can be done using the interMedia PL/SQL API, they require directory objects which makes them rather inflexible. A more flexible approach is to code both Import and Export routines using Java Stored Procedures. The Java can be writen and compiled, then loaded into the database, or compiled directly into the database. For simple routines it is easier to load them directly into the database using SQL*Plus, letting Oracle compile them for you. Oracle interMedia comes with a simple Java API, whose classes must be present in the schema where your Java code will reside. This can be done as follows.
Load interMedia Classes
ORACLE_HOME\bin\loadjava -user username/password@service ORACLE_HOME\ord\jlib\ordim.zip
Once these classes are loaded you are ready to code Stored Java Procedures using interMedia. The following Java Stored Procedure is called ImageHandler. It contains two public methods, ImportImage & ExportImage, which require Location and FileName parameters.
ImageHandler Java Stored Procedure
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "ImageHandler" AS import java.lang.*; import java.sql.*; import oracle.sql.*; import java.io.*; import oracle.jdbc.driver.*; import oracle.ord.media.*; public class ImageHandler { // Import images into the database. The java version frees us from needing // to create a directory object every time we import. public static void ImportImage(String Location, String FileName) throws Exception { // Connect to the database Connection conn = null; OracleDriver ora = new OracleDriver(); conn = ora.defaultConnection(); // Check record exists, and create it if it doesn't Statement statement = conn.createStatement(); ResultSet resultSet = statement.executeQuery("SELECT * FROM images WHERE file_name = '" + FileName + "'"); if (!resultSet.next()) { statement.executeUpdate("INSERT INTO images (file_name, image) VALUES ('" + FileName + "',ORDSYS.ORDImage(ORDSYS.ORDSource(empty_blob(),NULL,NULL,NULL,SYSDATE,0),NULL,NULL,NULL,NULL,NULL,NULL,NULL))"); } resultSet.close(); statement.close(); // Bind the image object to the database object OrdImage imgObj = new OrdImage(conn); imgObj.setBindParams("images", "image", " file_name = '" + FileName + "' "); imgObj.refresh(true); // Retrieve the data as a blob BLOB blobObj = imgObj.getContent(); // Open streams for the output file and the blob File binaryFile = new File(Location + FileName); FileInputStream inStream = new FileInputStream(binaryFile); OutputStream outStream = blobObj.getBinaryOutputStream(); // Get the optimum buffer size and use this to create the read/write buffer int size = blobObj.getBufferSize(); byte[] buffer = new byte[size]; int length = -1; // Transfer the data while ((length = inStream.read(buffer)) != -1) { outStream.write(buffer, 0, length); outStream.flush(); } // Close everything down inStream.close(); outStream.close(); //Send alterations back to server if (imgObj.loadData(Location + FileName)) imgObj.setProperties(); imgObj.flush(); conn.close(); } // This is required because the is no native support for the export function // in interMedia. public static void ExportImage(String Location, String FileName) throws Exception { // Connect to the database Connection conn = null; OracleDriver ora = new OracleDriver(); conn = ora.defaultConnection(); // Bind the image object to the database object OrdImage imgObj = new OrdImage(conn); imgObj.setBindParams("images", "image", " file_name = '" + FileName + "' "); imgObj.refresh(true); // Retrieve the data as a blob BLOB blobObj = imgObj.getContent(); // Open streams for the output file and the blob File binaryFile = new File(Location + FileName); FileOutputStream outStream = new FileOutputStream(binaryFile); InputStream inStream = blobObj.getBinaryStream(); // Get the optimum buffer size and use this to create the read/write buffer int size = blobObj.getBufferSize(); byte[] buffer = new byte[size]; int length = -1; // Transfer the data while ((length = inStream.read(buffer)) != -1) { outStream.write(buffer, 0, length); outStream.flush(); } // Close everything down inStream.close(); outStream.close(); conn.close(); } }; / show errors java source "ImageHandler"
Once this Java class is compiled into the database you will need to publish call specifications for it's methods. This involves writing PL/SQL "Wrappers" to allow the methods to be called from PL/SQL. The following two code samples create a package specification and body called ProcessImages which publishes the ImageHandler methods and some of the ORDSYS.ORDIMAGE methods.
ProcessImages Specification
CREATE OR REPLACE PACKAGE ProcessImages AS -- ----------------------------------------------------------------------------- -- The interMedia classes do not seem to be loaded into the database by default. -- This means references to them cannot be resolved. Do the following from DOS: -- -- loadjava -user username/password@service ORACLE_HOMEordjlibordim.zip -- -- File system permission must be granted to allow the JVM to write files: -- -- EXEC Dbms_Java.Grant_Permission('schema-name', 'java.io.FilePermission', '<>', 'read ,write, execute, delete'); -- ----------------------------------------------------------------------------- PROCEDURE ImportImage (p_location IN VARCHAR2, p_file_name IN VARCHAR2) AS LANGUAGE JAVA NAME 'ImageHandler.ImportImage(java.lang.String, java.lang.String)'; PROCEDURE ExportImage (p_location IN VARCHAR2, p_file_name IN VARCHAR2) AS LANGUAGE JAVA NAME 'ImageHandler.ExportImage(java.lang.String, java.lang.String)'; FUNCTION GetHeight(p_image IN ORDSYS.ORDImage) RETURN NUMBER; FUNCTION GetWidth(p_image IN ORDSYS.ORDImage) RETURN NUMBER; FUNCTION GetFileFormat(p_image IN ORDSYS.ORDImage) RETURN VARCHAR2; FUNCTION GetContentFormat(p_image IN ORDSYS.ORDImage) RETURN VARCHAR2; FUNCTION GetCompressionFormat(p_image IN ORDSYS.ORDImage) RETURN VARCHAR2; FUNCTION GetUpdateTime(p_image IN ORDSYS.ORDImage) RETURN DATE; FUNCTION GetMimeType(p_image IN ORDSYS.ORDImage) RETURN VARCHAR2; FUNCTION GetContentLength(p_image IN ORDSYS.ORDImage) RETURN NUMBER; FUNCTION GetSource(p_image IN ORDSYS.ORDImage) RETURN VARCHAR2; FUNCTION GetSourceType(p_image IN ORDSYS.ORDImage) RETURN VARCHAR2; FUNCTION GetSourceLocation(p_image IN ORDSYS.ORDImage) RETURN VARCHAR2; FUNCTION GetSourceName(p_image IN ORDSYS.ORDImage) RETURN VARCHAR2; FUNCTION GetContent(p_image IN ORDSYS.ORDImage) RETURN BLOB; END ProcessImages; /
With the advent of Java in the database Oracle have relaxed the restrictions on using Packaged Functions within SQL, hence the lack of PRAGMA RESTRICT_REFERENCES in the ProcessImages package specification.
ProcessImages Body
CREATE OR REPLACE PACKAGE BODY ProcessImages AS FUNCTION GetHeight(p_image IN ORDSYS.ORDImage) RETURN NUMBER IS BEGIN RETURN p_image.GetHeight; END; FUNCTION GetWidth(p_image IN ORDSYS.ORDImage) RETURN NUMBER IS BEGIN RETURN p_image.GetWidth; END; FUNCTION GetFileFormat(p_image IN ORDSYS.ORDImage) RETURN VARCHAR2 IS BEGIN RETURN p_image.GetFileFormat; END; FUNCTION GetContentFormat(p_image IN ORDSYS.ORDImage) RETURN VARCHAR2 IS BEGIN RETURN p_image.GetContentFormat; END; FUNCTION GetCompressionFormat(p_image IN ORDSYS.ORDImage) RETURN VARCHAR2 IS BEGIN RETURN p_image.GetCompressionFormat; END; FUNCTION GetUpdateTime(p_image IN ORDSYS.ORDImage) RETURN DATE IS BEGIN RETURN p_image.GetUpdateTime; END; FUNCTION GetMimeType(p_image IN ORDSYS.ORDImage) RETURN VARCHAR2 IS BEGIN RETURN p_image.GetMimeType; END; FUNCTION GetContentLength(p_image IN ORDSYS.ORDImage) RETURN NUMBER IS BEGIN RETURN p_image.GetContentLength; END; FUNCTION GetSource(p_image IN ORDSYS.ORDImage) RETURN VARCHAR2 IS BEGIN RETURN p_image.GetSource; END; FUNCTION GetSourceType(p_image IN ORDSYS.ORDImage) RETURN VARCHAR2 IS BEGIN RETURN p_image.GetSourceType; END; FUNCTION GetSourceLocation(p_image IN ORDSYS.ORDImage) RETURN VARCHAR2 IS BEGIN RETURN p_image.GetSourceLocation; END; FUNCTION GetSourceName(p_image IN ORDSYS.ORDImage) RETURN VARCHAR2 IS BEGIN RETURN p_image.GetSourceName; END; FUNCTION GetContent(p_image IN ORDSYS.ORDImage) RETURN BLOB IS BEGIN RETURN p_image.GetContent; END; END ProcessImages; /
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.
Grant Privileges
EXEC Dbms_Java.Grant_Permission('SCHEMA-NAME', 'java.io.FilePermission', '<<ALL FILES>>', 'read ,write, execute, delete');
It is up to the individual to decide on the level of access that is required. Depending on the documentation used, you may be told to add the directories you intend to access to your UTL_FILE_DIR parameter in the Init.ora file. I have not found this to be necessary when using the Java approach.
Assuming you have made it this far without errors, you should now be ready to load images into the database. Calling the ProcessImages.ImportImage procedure with a valid Location and FileName will import the specified image into the Images table. Once there, you can use the ProcessImages functions to retrieve information about the image.
Import Image
EXEC ProcessImages.ImportImage('C:\Images\', 'MyImage.gif'); SELECT ProcessImages.GetHeight(image) height, ProcessImages.GetWidth(image) width, ProcessImages.GetContentLength(image) length FROM images WHERE file_name = 'MyImage.gif';
If you subsequently need to export the images, you simply call ProcessImages.ExportImage with a valid Location and FileName.
Export Image
EXEC ProcessImages.ExportImage('C:\Images\Export\', 'MyImage.gif');
PossibleModifications
With minor modifications the same code will run outside the database allowing import of images from filesystems not present on the Oracle Server.
- Add ordim.zip to you classpath.
- Use Oracle's Thin JDBC Driver instead of the default driver used in Java Stored Procedures to connect to the database.
Import of Audio and Video data follows the same format, being stored in Blobs within the OrdAudio and OrdVideo types which are both decended from the OrdSource type. Early documentation implies that Oracle 8.1.7 may fully support interMedia export, but this may still require the presence of directory objects.
Hope this helps. Regards Tim...