The APEX Podcast now available!

Its here!! Eposode 1 of the APEX podcast! In Ep.1 we interview Mr. David Peake about the new and exciting things Apex 4.0 brings us. Check it out here at www.theapexpodcast.com or on iTunes.

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.

Step away from the table...moving from <table> to <div> in your templates

Apex templates can be somewhat daunting when we go and look at them in the builder...lots of HTML and tags and bears oh my! So lets look at creating a simple region template that will move us away from table tags and to div tags. WAIT!!! Why would I want to do this? Flexibility...a div structure will give you more control over the elements inside it than a table structure...Now apex will still generate some table structures by default, but we can at least encapsulate a region to start.

Lets look at the default reports region for theme 20:

<table class="t20ReportRegion t20Region" id="#REGION_STATIC_ID#" border="0" cellpadding="0" cellspacing="0" summary="" #REGION_ATTRIBUTES#>
<thead><tr><th class="t20RegionHeader" id="#REGION_STATIC_ID#_header">#TITLE#</th></tr></thead>
<tbody id="#REGION_STATIC_ID#_body">
<tr><td class="t20ButtonHolder">#CLOSE##PREVIOUS##NEXT##DELETE##EDIT##CHANGE#
#CREATE##CREATE2##EXPAND##COPY##HELP#</td></tr>
<tr><td class="t20RegionBody">#BODY#</td></tr>
</tbody>
</table>

hmmm....lets move it to this:

<div class="region1" id="#REGION_STATIC_ID#">
<div class="topHolder">
<div class="regionHeader" >#TITLE#</div>
<div class="buttonHolder">#CLOSE##PREVIOUS##NEXT##DELETE##EDIT#
#CHANGE##CREATE##CREATE2##EXPAND##COPY##HELP#</div>
</div>
<div class="regionBody" id="#REGION_STATIC_ID#_body">#BODY#</div>
</div>

Just a bit better...lets look at it in depth.

First we start with the <table> tags. We can easily move this to a <div> tag that will hold everything within the region. We do this with <div class="region1" id="#REGION_STATIC_ID#">. With the class we can apply stylesheets that will give our div a real personal look and feel. (More on that later) Next we add a div to hold all the header elements. Here we use <div class="topHolder">. Again, we give it a class and in here we pretty much move all the title and button elements.

<div class="regionHeader" >#TITLE#</div>
<div class="buttonHolder">#CLOSE##PREVIOUS##NEXT##DELETE##EDIT#
#CHANGE##CREATE##CREATE2##EXPAND##COPY##HELP#</div>
</div>

Next on to the body div. Really simple stuff here...

<div class="regionBody" id="#REGION_STATIC_ID#_body">#BODY#</div>

We use the #BODY# tag inside a div with the class regionBody. So now that we have this lets take a look at it in practice.

Not all that exciting...so lets put a border around the region and have it be 200px in size. The width can either be fluid (using %) or rigid (setting to 200px).

.region1 {
      border:1px solid;
      width:200px;

}

Lets also add a bit to the title...maybe underline it and increase the font size, bold.

.regionHeader {
      font-size:16px;
      font-weight:bold;
      text-decoration:underline;
}

It now looks like this:

Its getting there...what's next? Anything really. Add a background gradient, pad the body for spacing. Go wild. Don't know CSS? No problem. There are many CSS editors that will help you on your way. (I use CSSEdit for the mac). Ill leave you with an example I did for a client. Again, anything is possible!

APEX_UTIL ...ready SET JSON

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:

onChange="getEmail(this.value);"

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

function getEmail(pUser) {
    var get = new htmldb_Get(null,$v('pFlowId'),
                  'APPLICATION_PROCESS=GET_EMAIL',0);
    get.addParam('x01',pUser);
    gReturn = get.get();
    json_SetItems(gReturn);  
}

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.

DECLARE
  l_userid varchar2(100);
  l_email varchar2(100);
BEGIN
  l_userid := wwv_flow.g_x01;
   
   SELECT email
   into l_email
   FROM my_user_table
   WHERE upper(user_account) = upper(l_userid);
   
apex_util.set_session_state('P100_APPROVER_EMAIL',l_email);
apex_util.json_from_items('P100_APPROVER_EMAIL');

end;

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.

Well it had to happen....

Upon leaving a 10 year career at Oracle and setting out for myself...I thought that a web presence in the application express community was necessary. So here it is, a blog about application express and life itself. Here you can find tips and lessons learned while building apps at my new place of employment, JSA2, as well as tidbits about well...anything...you may learn something new if you stick around...so with that I'll say hello/goodbye and have something meaningful soon.

Thanks!

Brian