8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
InterMedia - Import-Export Of Images
This article is an update of my original Oracle 8i interMedia 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:
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.
In later releases this feature has been renamed from Oracle interMedia to Oracle Multimedia.
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', '<<ALL FILES>>', '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; /
For further information see:
- Oracle interMedia User's Guide and Reference
- interMedia - Import-Export of Images (8i)
- Export BLOB Contents Using UTL_FILE
Hope this helps. Regards Tim...