Schedule a conversion (UTL_FILE and DBMS_SCHEDULER)

Here is your mission.....convert a coldfusion application to APEX...check...also convert adobe illustrator/PDF files to image files...chec...wait a sec! .ai files to .png/.jpg/.gif? How? What? Where?

So this was my challenge...all on an AIX platform to boot. Lets take this apart one piece at a time. Getting the source files (.ai or .pdf) into a database table is pretty easy...the apex docs actually have a sample on how to do it (From V 2.2 but still works link).

Now that we have the source file in the database...what to do...well you can convert a postscript file with ghostscript. But we have to run ghostscript from an OS command line. Lets start by getting the file from the database to the file system.

Some pre-reqs. We need a table with a blob column and an adobe illustrator file (my_illustration.ai). Also having a user with the dba role is going to speed things along. The writing of the blob to the file system is a modified version of the demo found on the PSOUG site.

First we need to get a directory we can read and write from on the OS. Lets use /temp/convert/ for example. Next we have to tell the database about this directory. So with the create directory privilege granted on your db user issue the following:

create or replace directory CONVERT as '/temp/convert/';

If you are not creating this directory as the DB user you are going to use you have to issue this:

grant read, write on directory convert to scott;

also

grant execute on utl_file to scott;

OK. We have the directory...now lets write the file out to the OS. First we have to define the output directory. Declare the following:

l_output utl_file.file_type;

then in your body

l_output := utl_file.fopen('CONVERT', 'my_illustration.ai', 'WB', 32760);

Now, write the BLOB from the table:

  SELECT dbms_lob.getlength(ai_file)
  INTO len
  FROM image_table
  where file_name = 'my_illustration.ai';

  x := len;

  SELECT ai_file
  INTO vblob
  FROM pl_item_image
  where id = l_id;

  IF len < 32760 THEN
    utl_file.put_raw(l_output,vblob);
    utl_file.fflush(l_output);
  ELSE
    vstart := 1;
    WHILE vstart < len
    LOOP
      dbms_lob.read(vblob,bytelen,vstart,my_vr);

      utl_file.put_raw(l_output,my_vr);
      utl_file.fflush(l_output);

      vstart := vstart + bytelen;

      x := x - bytelen;
      IF x < 32000 THEN
        bytelen := x;
      END IF;
    END LOOP;
  END IF;
  utl_file.fclose(l_output);

If all went well we have the BLOB in the /temp/convert/ directory. Now we call dbms_scheduler. The following code examples will be for release 11g and up. I will outline the differences. 

DBMS_SCHEDULER can be used to run OS scripts, IF the correct credentials are given. In 10gR2 and previous releases, the credentials were kept in a file located at $ORACLE_HOME/rdbms/admin/externaljob.ora. The default values were nobody/nobody. For production instances this posed a challenge. How do we alter this file, give permissions and keep a secure environment. Enter 11g. Scheduler credentials have been moved from this file to inside the database. We create credentials with the following command:

dbms_scheduler.create_credential(
credential_name IN VARCHAR2,
username        IN VARCHAR2, -- operating system user
password        IN VARCHAR2, -- and corresponding pwd
database_role   IN VARCHAR2 DEFAULT NULL,
windows_domain  IN VARCHAR2 DEFAULT NULL,
comments        IN VARCHAR2 DEFAULT NULL);

Also found in the docs here. For our example lets use the following:

dbms_scheduler.create_credential('CONVERT_IMAGE', 'oracle','oracle');

Now we can execute OS level jobs with dbms_scheduler. Lets look at the following scheduler job:

dbms_scheduler.create_job(job_name => 'myjob',
job_type => 'executable',
job_action => '/temp/convert/convert_image.ksh',
number_of_arguments => 1,
enabled => FALSE,
auto_drop => TRUE);

dbms_scheduler.set_job_argument_value('myjob',1,'my_illustration.ai');
DBMS_SCHEDULER.set_attribute('myjob' , 'credential_name', 'CONVERT_IMAGE');
dbms_scheduler.enable('myjob');

Here we set up a job called "myjob". We are on AIX remember so we are executing the convert_image.ksh script. In this script we are calling the ghostscript executable. We are also passing this script a variable. The variable is passed using the following code:

dbms_scheduler.set_job_argument_value('myjob',1,'my_illustration.ai');

We also have to pass the credentials so that we can run the OS level job. We do this here:

DBMS_SCHEDULER.set_attribute('myjob' , 'credential_name', 'CONVERT_IMAGE');

After the job is run, the illustrator file that we wrote out to the file system should have been converted into a .png/.jpg/.gif file. (depending on your needs). The last step is to get the file back into the database. We use the following code to get the file back into another BLOB column in the same or different table.

For your declare section:

dest_loc  BLOB;
src_file BFILE;

For your plsql body:

src_file := bfilename('CONVERT','my_illustration.jpg');

update new_image_table set
new_image_file = empty_blob()
where image_name = 'my_illustration'
RETURNING new_image_file INTO dest_loc;

DBMS_LOB.OPEN(src_file, DBMS_LOB.LOB_READONLY);
DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.LOADFROMFILE(
          dest_lob => dest_loc
        , src_lob  => src_file
        , amount   => DBMS_LOB.getLength(src_file));

DBMS_LOB.CLOSE(dest_loc);
DBMS_LOB.CLOSE(src_file);

Now the new converted image is in the database. We can display this image in a report with APEX no problem. So there you have it....an illustrator file conversion to an image file that can be displayed.