Entries in PLSQL (2)


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;


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
    vstart := 1;
    WHILE vstart < len


      vstart := vstart + bytelen;

      x := x - bytelen;
      IF x < 32000 THEN
        bytelen := x;
      END IF;

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:

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_attribute('myjob' , 'credential_name', 'CONVERT_IMAGE');

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:


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;

          dest_lob => dest_loc
        , src_lob  => src_file
        , amount   => DBMS_LOB.getLength(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.




On a recent project we ran across a great place to use apex_util.set_session_state and apex_util.json_from_items. A client wanted an email address to self populate after picking a name from a pop-up LOV. Easy enough but how to do this...options options options...well we could have used jQuery but I decided to use APEX_UTIL because of the existing code on the page. So here is what we did..

First, we added an onChange event to the HTML Form Element Attributes of the popup LOV:



Next on to the javascript in the header....

function getEmail(pUser) {
    var get = new htmldb_Get(null,$v('pFlowId'),
    gReturn = get.get();

nothing too complex...just calling an APEX onDemand process and passing over a user name. In return we get an email address as JSON.

Last is the onDemand plsql. We use apex_util.set_session_state and apex_util.json_from_items to set the item and pass over the email address.

  l_userid varchar2(100);
  l_email varchar2(100);
  l_userid := wwv_flow.g_x01;
   SELECT email
   into l_email
   FROM my_user_table
   WHERE upper(user_account) = upper(l_userid);



Now when a user selects from the pop-up LOV, the email address is auto filled into the approver email text field.

Nothing earth shattering here but rather helpful when you want to have values filled in on the page automatically giving that web 2.0 fresh feeling. Also on Chris Beck's blog is a bigger implementation of JSON calls to a apex shuttle.