Saturday, 23 June 2012

Two Apex 4.2 Noteworthy APIs

Well, yesterday oracle released the first early adopter of application express, having had a chance to have a little play around, these are the ones that caught my attention:



There are currently some interactive reports utility functions/procedures as part of the apex_util package. These will now be deprecated, and functionality will be added to a package named APEX_IR.

The most exciting addition is the ability to get the derived IR query (from added filters/sorts). Without any published API docs, I can only assume this is through the function get_report. Further, I set up an example on page 2 of the sample database application. Created a new popup page with a dynamic region with the following source:


 l_report apex_ir.t_report;


    l_report := apex_ir.get_report(
        p_region_id => 6506762112827941367,
        p_page_id => 2);

    htp.p('<br />');htp.p('<br />');
    htp.p('<p><strong>BIND VARIABLES:</strong>');
    htp.p('<br />');
    htp.p('<br />');
    for i in 1..l_report.binds.COUNT LOOP
        htp.p('name: ' || l_report.binds(i).name || '<br />');
        htp.p('value: ' || l_report.binds(i).value || '<br />');



Where I got the p_region_id by querying the APEX_APPLICATION_PAGE_REGIONS data dictionary.

Then I added a button to page 2, to the right of the interactive report search "Show Source" pointing to that new page.




Well, any experienced apex developer will no doubt have used the undocumented htmldb_Get object to perform some form of AJAX request. I know I often use it to submit values of items to session state. With 4.2 comes some additions to the javascript API.

The new objects include:


The one I am most interested in is the apex.server.process. So how do we use it? Typing in the function name into the javascript console, reveals the following:

function ( pName, pData, pOptions ) {
    return _call( "APPLICATION_PROCESS=" + pName, pData, pOptions );

Obviously, pName is the name of the process if you want to submit, pData would be session items you would like to set, and can include the x0n variables you may be used to.

If you dig into the source a bit deeper, you will see that to set page items can be set by passing in an array of page item names with the property name: pageItems. Alternatively, if you want some other derived value you would pass respective values to p_arg_names and p_arg_values (arrays). Also you can set the x0n variables, which are referred to in the app process by wwv_flow.g_x0n. These are all passed into the pData object (2nd parameter).

Then there is the pOptions object. These are the properties you would expect to see in the jQuery AJAX API. Some cases I can imagine you would like to specify is the success/error callback(s), and specifying the dataType in case you would like to return text that is not JSON (as JSON is the default dataType specified).

So, some examples:

Submitting a page item, with the current value:

apex.server.process('ProcessName', {pageItems: ['P2_TEST'], x01: 'temp value'});

If you are returning text from the server, that is not json, i would suggest changing the type in the pOptions.

var respObj = apex.server.process('ProcessName', {pageItems: ['P2_TEST'], x01: 'temp value'}, {dataType: 'text'});

Otherwise, you will get a Parse Error / Syntax Error (as it is not in valid JSON).

Submitting session state to both a current page item, and an item not on the page (e.g. app item).

var respObj = apex.server.process('ProcessName', {p_arg_names: ['TEST_ITEM'], p_arg_values: ['Some value'], pageItems: ['P2_TEST'], x01: 'temp value'}, {dataType: 'text'});

On demand process used for testing:


    htp.p('App Item: ' || :TEST_ITEM || ' Page Item: ' || :P2_TEST || ' x Item: ' || wwv_flow.g_x01 );


Anyways, seems good. This is not based on any docs, so there may be some better recommendations come out in time. Happy coding!

Thursday, 14 June 2012

Enhancing gedit for PL/SQL Development

Most text editors allow customisation of syntax highlighting and the like, and having recently moved to using gedit, thought i'd enhance it a bit. In a fresh installation, it already has reasonable support for oracle based keywords, but could do with a bit of an update.

The object that does the syntax highlighting is gtksourceview - depending on the version of gedit, either version 2 or 3. 

By going to the project page on gnome, you will see the howto for adding a new language: (Linux only) Since package source code is typically in files with the extension pks and pkb, it is first necessary to update the system to recognise that those files are of the mime type text/x-sql (or a custom one if you really want). So, create an xml file with the following:

<mime-info xmlns="">
  <mime-type type="text/x-sql">
    <comment>SQL Source including PL/SQL</comment>
    <glob pattern="*.sql"></glob>
    <glob pattern="*.pks"></glob>
    <glob pattern="*.pkb"></glob>

This can be saved in two locations. Either:

  • /usr/share/mime/packages
  • ~/.local/share/mime/packages
I saved mine into the former path. Once done, you need to update the mime database:

sudo update-mime-database /usr/share/mime

Now, when you open those two new file types in gedit, it will recognise them as text/x-sql files, and apply the correct language.

The next thing you'll notice, is that there are a few keywords that aren't having syntax highlighting applied. We can update this word list so particular words are highlighted accordingly. Make a backup of the sql.lang file and edit the sql.lang file to add new words:

sudo cp /usr/share/gtksourceview-3.0/language-specs/sql.lang /usr/share/gtksourceview-3.0/language-specs/sql.lang.backup1
sudo gedit /usr/share/gtksourceview-3.0/language-specs/sql.lang

The first section of the file lists the metadata for which files this configuration should apply to. Update the globs property to the file pattern as specified in the mime type configuration file above, so it should read:

    <property name="mimetypes">text/x-sql</property>
    <property name="globs">*.sql;*.pks;*.pkb</property>
    <property name="line-comment-start">--</property>

Then, we need a new context section with the list of words that should get highlighted. To do this, I ran the following query:

  '<keyword>' || keyword|| '</keyword>' keyword
  length(keyword) > 2
order by

I then exported that data, enclosed in the xml property:

<context id="oracle-updated-keywords" style-ref="keyword">

Added it to the file we already have opened (sql.lang), and updated the context with the same id as the language, so it has the entry within the include property (at the bottom of the config file). So it should be looking like:

<context id="sql" class="no-spell-check">
        <context ref="oracle-built-in-datatypes"/>
        <!-- Other contexts removed for readability of this article -->
        <context ref="oracle-updated-keywords"/>

Restart gedit and open a file with one of those extensions (or manually set the language) and it should now have those keywords highlighted.

More than likely, that list of keywords have been specified earlier in the language file - from what I can tell, gtksourceview takes the first style for a particular keyword it finds. This could obviously be extended further to include packages, procedures and functions.

It's worth noting, a custom language file could also be placed into: ~/.local/share/gtksourceview-3.0/language-specs.

I've chucked the files onto github:

Other language files:

Monday, 4 June 2012

Saving Files Client Side

A couple of html5 interfaces that allow you to download files from the client side are:
These haven't quite made it into the browsers, but there are projects on GitHub for both of these interfaces, whilst we wait for them to become main stream. The basic examples on the FileSaver project README are saving both a text file based on an input string, and saving a canvas as an image file (you can already redirect the user to the data url of the canvas object, but this would actually download the file). See: and Anyways, i'll give a little focus on saving a file.

 I have a basic page with a text field to be used as the file name (:P20_FILE_NAME) and a text area with the data that I want to save to a file (:P20_EXPORT_CONTENT). First I need to add a reference to the two interfaces (I just added these into the page header - obviously better to import these files into the app):

<script src="" type="text/javascript">
<script src="" type="text/javascript">

Then we write a function to save that will save the data:

var exportData = function(){
    //Set to download.txt if nothing was specified
    var fileName = $v("P20_FILE_NAME") || "Download.txt";
    var exportContents = $('#P20_EXPORT_DATA').val();
    //notepad.exe doesnt like printing new lines.,
    exportContents = exportContents.replace(/\n\r?/g, '\r\n');
    var bb = new BlobBuilder();
    saveAs(bb.getBlob("plain/text;charset=UTF-8"), fileName);

Along with a button (Save) pointing to the function to initiate the download. See an example: It's probably worth mentioning, because the scripts are loaded directly from github in this example. it won't work properly in IE(9). See 

Friday, 1 June 2012

Error Handling API

Originally heard about this from Patrick's blog months back. See and Then a couple of weeks back I was looking at the new features on the Apex builder guide, where they mention about the enhanced error handling. See So I thought I may as well set up a basic example to see how it is.

The only sections in the documentation that really talk about it are:

Which is basically about setting the error handling (PL/SQL) function. The main source of information is on the API docs for apex_error. This page talks about data types, and available functions/procedures you can use with this package, which will allow customising the displayed error message.

Whilst the API docs do provide a good example, I just wanted to have a play on my own to see it in action. First set up a table with a unique constraint:

   ( "ID" NUMBER, 
 "NAME" VARCHAR2(200), 


  before insert on "UNIQUE_PEOPLE"               
  for each row  
  if :NEW."ID" is null then 
    select "UNIQUE_PEOPLE_SEQ".nextval into :NEW."ID" from dual; 
  end if; 


Then a function to check this validation:

create or replace function unique_person_check(
    p_error in apex_error.t_error )
    return apex_error.t_error_result
    l_result          apex_error.t_error_result;
    l_reference_id    number;
    l_constraint_name varchar2(255);
    l_result := apex_error.init_error_result (
                    p_error => p_error );
    IF p_error.ora_sqlcode = -1 THEN --unique constraint
        if apex_error.extract_constraint_name(p_error) = 'UNIQUE_PEOPLE_UK1' THEN
            l_result.message := 'Person already exists. Please choose another';

        END IF;
    END IF;
    --fallback incase our testcase doesn't match anything
    if p_error.ora_sqlcode is not null and l_result.message = p_error.message then --no new message yet assigned. Must mean we haven't met the conditions above
    l_result.message := apex_error.get_first_ora_error_text(
            p_error => p_error);
    end if;
    return l_result;
end unique_person_check;​

Then set up a page with a tabular form for easy testing. Edit the page attributes and specify the error function as: unique_person_check. If this function was more of a global set up, you would just set this up in the application definition.

I set up a demo: - try and specify a name that is already in the list, and you should receive the error specified above. The example on the docs also does some logging for internal errors that aren't access denied errors, but I couldn't think of any use cases to test that, so left that alone for now. :-).

Oracle HTTPS Requests: Set up the Wallet

In addition to setting up the ACL, if you wish to make HTTPS requests you will need to set up a wallet - and more than likely, import trusted certificates.

On the oracle server, open the wallet manager. In linux this is with the command: owm.

Assuming no current wallet exists, create a new one by going to File --> New. At this point you will need to give the wallet a password. Save the wallet and take note of the directory it installed to (this is what you need to pass to utl_http, and no single file). The default directory this is saved to is generally: $ORACLE_HOME/owm/wallets/oracle.

The next step is to import trusted certificates into the wallet. The process varies between OS/Browser. First, go to the website you are intending on making requests to and view the certificate information which is normally done by clicking a button in the address bar.

Linux Chrome:

  1. Click the button the left of the URL
  2. Click certificate information
  3. Click Details
  4. Click Export
  5. Specify the type as PKCS #7, certificate chain
  6. Save to a convenient location. I find it best to actually save it into the same folder as the oracle wallet, so you have a future reference
Windows Chrome:

  1. Click the button to the left of the URL
  2. Click certificate information
  3. Click Details
  4. Click Copy to File...
  5. Click Next
  6. Click Cryptographic Message Syntax Standard - PKCS #7 Certificates (.P7B)
  7. Click Next
  8. Save to a convenient location. I find it best to actually save it into the same folder as the oracle wallet, so you have a future reference
(The same dialogue can be got to in Internet Explorer by going to File --> Properties --> Certificates)

It is important to export the certificates in a type that allows you to export the full certificate chain, otherwise it is highly likely, and HTTPS requests will still not work.

To import the certificates into the wallet, from the wallet manager:

Right click on the tree node that reads Trusted Certificates, and select the option Import Trusted Certificate... 
Select the option Select a file that contains the certificates
Locate the file and click OK

If all went well, you should see at least 1 new certificate added to the node Trusted Certificates.

In any PL/SQL code, you then just need to pass in the wallet parameters - wallet directory, and wallet password (the wallet path parameter is used with the syntax "file:path", where path is the actual path the wallet directory). I normally do this with the set_wallet procedure, but the two properties are parameters on the functions/procedures to execute http requests (e.g. utl_http.begin_request).

Confirm the wallet is working with the following:

 req utl_http.req;
 resp utl_http.resp;
 rw varchar2(32767);

 utl_http.set_wallet('file:<path-to-wallet-directory>', '<wallet-password>');

 req := utl_http.begin_request( '<secure-url>');
 resp := utl_http.get_response(req);

   rw := null; 
   utl_http.read_line(resp, rw, TRUE); 
   --do something with rw if you like
   exception when others 
 end loop;