Changing the status of an Oracle Apex application with pl/sql

I needed this. I needed this badly. And for once the Oracle Apex forum gave me the answer for my need..

create or replace procedure set_application_status
  ( p_application in apex_applications.application_id%type
  , p_status      in varchar2
  )
is
begin
  if p_status not in ( 'AVAILABLE'        , 'AVAILABLE_W_EDIT_LINK', 'DEVELOPER_ONLY'
                     , 'RESTRICTED_ACCESS', 'UNAVAILABLE'          , 'UNAVAILABLE_PLSQL'
	             , 'UNAVAILABLE_URL')
  then
    raise_application_error(-20000, 'Status '||p_status||' is not supported');
  end if;
  --
  for r_i in ( select app.workspace_id
               ,      app.application_id
               from   apex_applications app
               where  app.application_id = p_application
	     )
  loop
    wwv_flow_api.set_security_group_id
      ( p_security_group_id => r_i.workspace_id
      );
    wwv_flow_api.set_flow_status
      ( p_flow_id     => r_i.application_id
      , p_flow_status => p_status
      );
    commit;
  end loop;
end;

Component view in Apex 5.1

So I’m a dinosaur. I don’t care if I’m not one of the cool kids. I like component view, I started with component view, I’m comfortable in component view and I get the “flow” of component view.
Of course I’m using page designer, but when things get “buggy”, I switch in dinosaur-modus.

And than there is Apex 5.1….
Where is my component view?

First, there is Two pane mode
1_switch_to_two_pane
For me that is a big step in the right direction. I’m not always in the position to ask for a 24″ monitor for development. So, less is more in this case.
And look
2_two_pane_layout
Component view!

But it’s not the same. This Component View imposter doesn’t give me the original page for e.g. a region. It jumps to the right section of the page-designer.
Not fair! That’s cheating! …. given that it is also very handy and perhaps my new MO.

But look in the upper right corner.
3_goto_preferences
Here you can set some developer specific preferences, like

4_enable_component

This will change the original panel to
4_legacy_button

Legacy, deprecated, whatever. It’s still there.

Like I said, I will be using Two Pane Mode as default with component view as my prefered way of developing (it could be nice if I could set this somewhere in my preferences),
but it’s comforting to know I can still switch “back”.

APEX 5 – The title of a modal dialog

Is it bug or is it a feature? You tell me.

When you create a modal page in Apex 5, the title of the dialog is created before any change to session state. Dynamic titles, based on session state will therefore be funky.

Take this example.

1. Create a new page
2. Select as page type Form > Form on a Table with Report
3. next, next … until the Form page and set the property of the page mode to “Modal Dialog”
4. etc ect, finish

This creates a multirecord report with edit links.
When you click an edit link a dialog appears with the default title “Form on DEPT” (let’s say we had chosen DEPT as table).

But now I want to include the DEPTNO of the record in title. Set the property Title e.g. to “DEPT &P2_DEPTNO.”

Go to the report, click a record…. the deptno in the title is empty…
Go to the report, click another record… the title is not the deptno of the record you clicked, it is the former deptno.

Like I’ve said, is it a bug or a feature?

What happens:

The Apex team implemented the dialog page as an iframe within a jQuery dialog.
The links that are generated in the report look something like:

javascript:apex.navigation.dialog('f?p=x:x:x::NO::P1_DEPTNO:x
,{title:'DEPT ',height:'500',width:'720',maxWidth:'960',modal:true,dialog:null}
,'t-Dialog--standard',apex.jQuery('#Rx'));

And that is the problem in a nutshell. When the links in the report is generated, a JavaScript object is also generated for the dialog with e.g. the title as a property. This title is based on the current session state. However, the first argument, the link for the iframe, will alter session state to a new value. But that value is not propagated to the title of the dialog.

How to alter (solve?) this behaviour?

On the global page 0 I have created a Dynamic action, on Page load, executing the following JavaScript:

(function($){
   if ( $("body").hasClass("t-Dialog-page")) {
     $(".ui-dialog-title", parent.document).html($("title").html());
   }
})(jQuery); 

When in a dialog page, take the title of the page and replace the title of the parenting dialog.

The only issue I have with this solution is that sometimes you can see the title change. But still, it is better that nothing.

Mail a screenprint in Oracle APEX

We always build bug-free applications (hehe), but in the extremely rare occasion that the user encounters a bug, they always seem to use words like “it doesn’t” work and nothing more than that.

Well, I like to have more information, so on our site we wanted the user to be able to send some background info and a screen print from within the system.

Personally I like the Team Development framework but for our purpose we needed some lightweight solution. And sending a screen print should be cool, right?

Well, most of the heavy lifting of creating a screen print is done by the library HTML2Canvas

I included an “I want to report a problem” link in the navigation bar entries with an URL-target

  javascript:getScreen();

The javascript function that is called is the following:

function getScreen() {
  if (!!document.createElement("canvas").getContext) {
    html2canvas(window.document.body, {
      onrendered: function(canvas) {
      var dataUrl = canvas.toDataURL("image/jpeg");
      var clobObj = new apex.ajax.clob(
        function(p){
          if (p.readyState == 4){
            var get = new htmldb_Get(null,$v('pFlowId')
                     ,'APPLICATION_PROCESS=mailScreen',0);
            gReturn = get.get();
            alert('Mail has been send');
          }
        });
        clobObj._set(dataUrl );      
      }
    });
  } else {
    var get = new htmldb_Get(null,$v('pFlowId')
              ,'APPLICATION_PROCESS=mailScreen',0);
    gReturn = get.get();
    alert('Mail has been send');
  }  
}

Notice that I check if canvas is supported by the browser. If it isn’t supported we’re not able to create a screenshot and we just send some background information. When canvas is enabled I put the output string in the CLOB_CONTENT-collection.

On our site we are sending the mail to our customer to let them be able to give some more background information. But you could of course also navigate to a new page for that or just email the information directly to you.

The On Demand process handles the mailing part.

declare
  cursor c_variables
  is
    select item_name||' : '
    || apex_util.get_session_state(item_name) AS session_value
    from   apex_application_page_items
    where  application_id = :APP_ID
    and    page_id        = :G_CURRENT_PAGE
  UNION ALL
    select item_name||' : '
    || apex_util.get_session_state(item_name) 
    from   apex_application_items
    where  application_id = :APP_ID
  ;               
  l_id   number;
  l_blob blob;
  l_clob clob;
  l_body clob;
BEGIN
  l_body := to_clob(:APP_USER||', please give some more information before sending it to us'||utl_tcp.crlf);
  l_body := l_body||utl_tcp.crlf;  
  for r_variable in c_variables
  loop
    if ( c_variables%ROWCOUNT = 1 ) 
    then
      l_body := l_body||'Page variables in session state: '||utl_tcp.crlf;  
    end if;
    l_body:= l_body||r_variable.session_value||utl_tcp.crlf;
  end loop;
  --
  /* Add the screen print */
  FOR r_coll IN 
    ( SELECT coll.clob001
      FROM   apex_collections coll
      WHERE  coll.collection_name = 'CLOB_CONTENT'
    ) 
  loop
    dbms_lob.createtemporary(l_clob,false);
    dbms_lob.copy
      ( dest_lob   => l_clob
      , src_lob    => r_coll.clob001
      , amount     => dbms_lob.getlength(r_coll.clob001)
      , src_offset => dbms_lob.instr(r_coll.clob001,',',1,1)+1
      );   
    l_blob := apex_web_service.clobbase642blob(l_clob);
  end loop;
  --
  l_id:= apex_mail.send
      ( p_to  => :app_user||'@mydomain.nl'
      , p_from => 'myapp@mydomain.nl'
      , p_subj => 'Issue for application '
                 ||:APP_ID||' Page '||:APP_PAGE_ID
      , p_body => l_body
      );   
  if dbms_lob.getlength(l_blob) > 0
  then
    apex_mail.add_attachment
     ( p_mail_id    => l_id
     , p_attachment => l_blob
     , p_filename   => 'screenprint.jpg'
     , p_mime_type  => 'image/jpeg'
     );
  end if;
  --
  apex_mail.push_queue;
end;

And again, that’s it!

Oracle Apex Gantt collapse all tasks

When implementing the project Gantt chart in Apex, I thought it was annoying that nested tasks default expanded.
I wanted them to be collapsed by default.

For this I added a listener to the draw event of the AnyGantt object to collapse the Gantt chart:

  AnyGantt._charts.chart__0.addEventListener('draw',function(){
    AnyGantt._charts.chart__0.collapseAll();
  });

This snippet should be executed on load (Dynamic Action, Page JavaScript property, whatever).

In your case, chart__0 could be chart__N. You have to figure it out in your case.

Just a small blog post, but it could be handy at times.

Building a chrome extension for Oracle APEX Builder

As a developer I like to create my own tools to make my work easier. In our current Oracle APEX project we have build a custom authorization mechanism, based on page numbers. A drawback the development team faced was that we had to register every single page in our custom authorization tables. So I came to think that if I could extend the APEX builder with some custom code on the page builder page, it would make our life a lot easier. When working on a new page we could register the page with a simple click.

The technique I describe in this blog can be used to extract information from the APEX Builder and call some stored procedure in the database to do some magic with it.

I wanted to build an extension for Chrome. With some googling I stumbled upon Chrome extensions. As always, the documentation of these Google API’s is excellent.

I find that building a very, very simple extension for Chrome is relatively easy. Of course you can always be overcomplicating things, but following the KISS principle I was able to build an extension that served my purpose.

The idea behind my extension is, that when the extension is clicked, some JavaScript is fired that “gets” the application id and page id from the current tab and sends it to an API on the database to register the page. But the last API can of course do all kinds of magic with the application and page id.

This is what I did:

Create a directory to host the files for the extension. E.g.

D:\ChromeExtensions\AddToAuth

The extension needs a manifest file with the name manifest.json.
My manifest file became

{
  "name": "Add a page to Auth",
  "version": "1.0",
  "manifest_version": 2,

  "description": "With this extension one can add a page from APEX builder to Auth",
  "icons":{"128":"db.png"},

   "browser_action": {
    "name": "Add a pagina to Auth",
    "default_icon": "db.png"
  },

  "background": { "scripts": ["background.js"] },
  "permissions": [
    "http://*/*"
  ]
}

See Formats: Manifest Files for a full description of the fields of a manifest file.

If you look at the manifest two things pop up

  • db.png This is a 128×128 png file that is shown in the browser
  • background.js This is the event page of the extension.

When the extension – a browserAction – is clicked I have to fire some JavaScript. So, the background.js would have to add a listener to the event.
background.js

chrome.browserAction.onClicked.addListener(function(tab) {
  chrome.tabs.executeScript(null, {file:"worker.js"});
});

When the browserAction is clicked, the worker.js is executed
worker.js

var APPL_XPATH = '//*[@id="apex-breadcrumbs"]/a[3]';
var PAGE_XPATH = '//*[@id="apex-breadcrumbs"]/span[4]';

var
  application,
  local,
  page,
  tabQuery,
  xhr;

function getNumber(pXpath, pItem) {

  var
    nextResult,
    lReturn,
    xpathResult;

  xpathResult = document.evaluate(pXpath, document.body, null, XPathResult.ANY_TYPE, null);
  nextResult = xpathResult.iterateNext();

  while (nextResult) {
    if (nextResult.textContent.indexOf(pItem) !== -1) {
      lReturn = parseInt(nextResult.textContent.substr(pItem.length), 10);
    }
    nextResult = xpathResult.iterateNext();
  }
  return lReturn;
}

tabQuery = location.search.split('=')[1].split(':');
local = 'http://' + location.host + '/' + location.pathname.split('/')[1] + '/';

if (tabQuery[0] === '4000') {    /* Builder   */
  if (tabQuery[1] === '4150') {  /* Edit page */
    /* 
      Get the pagenumber from the body
    */
    page = getNumber(PAGE_XPATH, 'Page');

    if (page) {
      /* 
        The page is found. Now get the applicationnumber
      */
      application = getNumber(APPL_XPATH, 'Application');
    }
    if (application && page) {
      /* 
        Both items have been found. Call the stored procedure to do some magic.
      */
      xhr = new XMLHttpRequest();
      xhr.open("GET", local + 'AUTH_API.ADD_PAGE?p_application_id=' + application + '&p_page_id=' + page, true);
      xhr.send();
      alert('application: ' + application + ' page: ' + page + ' is added');
    }
  }
}

When the query part of the URL is like 4000:4150 we know that we are on the page edit page.Then we use some XPATH on the body of the page to get the application id and page id. The XPATH expressions are stored in two constant vars. The XPATH expressions you see are working for the current APEX 4.2 version; to get these expressions is very easy using Chrome:

  • In APEX Builder, go to the page edit page.
  • In the breadcrumbs go to the Page xx part.
  • Inspect the element using the context menu in chrome.
  • In the elements tab use the context menu Copy Xpath

The last step to take is to send the two id’s to the database.
For this, I just use a HTTP-request to a stored packaged procedure AUTH_API.ADD_PAGE with two parameters p_application_id and p_page_id.

In our architecture (OHS with mod_plsl and a standard dads.conf) for the development database we are allowed to fire stored procedures this way. In our development
database the wwv_flow_epg_include_mod_local is non-restrictive:

create or replace function wwv_flow_epg_include_mod_local
  ( procedure_name in varchar2
  ) return boolean
is
begin
  return true; 
end wwv_flow_epg_include_mod_local;

The connecting user, defined in the dads.conf, -e.g. APEX_PUBLIC_USER-, has to have proper grants on the stored procedure that is being called by the HTTP-GET.

Importing the extension.
– Go to the Extra > extentions part of the menu
– Enable developmentmodus
– Use the load button to load the directory where you put all your files.

The new browserAction should show up in the browser.

That’s it. With this you can build an extension in chrome to parse a page in APEX Builder and send the information to some API in the database.

Using Knockout.js with Oracle Apex

If you’re one of the cool kids in the JavaScript block, you might have heard of Knockout.js. With Knockout you can e.g. use declarative binding to DOM elements and use it to dynamically refresh your UI. Just go through the Tutorials on the site. They are excellent!

Sounds like Dynamic Actions in Oracle Apex, right? But if you are starting to hate the dense forest and – in my humble opinion – bugginess of Dynamic Actions and are looking around for alternatives, I would suggest a look into Knockout.

I will give a very, very simple example to dynamically show a message after inserting a value in a text item. Some of the basic Apex concepts I will not discuss.

First of all, you will have to download the latest version of knockout.js and link it to your page c.q. application. You could also link to a Content Delivery Network (CDN) like cdnjs.com.

The Item where you have to enter the text in has to be bound to the viewmodel. You can use the attributes part of the HTML element.
element

In my example I have a P60_ELI text field which I bind to the observable ELI.

The text that automagically will appear will be shown in another Text Field P60_ELI_VERBRUIK, bound to the knockout id ELI_VERBRUIK.
element2

To put it all together you have to apply the bindings with some small JavaScript in the page:

function getVerbruik(p_x, p_y) {
  var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=SomeOnDemandProcedure',0); 
  //get.add('X',p_x); you could pass the input to the process
  //get.add('Y',p_y);
  gReturn = get.get();    
  return gReturn; 
}  

function viewModel() {
  var self = this;  
  self.ELI = ko.observable();
  self.ELI_VERBRUIK = ko.computed( function() { 
                                     return getVerbruik('ELI', self.ELI() ); /* passing through the value that is entered in the observable ELI field, i.e. the P60_ELI item */
                                   }
                                 );
}
/*
  Bind the right viewModel (you could have created several) to the elements on the page. 
*/ 
$(function(){
  ko.applyBindings(new viewModel());
});
 

The function getVerbruik will give some text back from the database using an ON-DEMAND process. You can of course just give back text, but I would suggest to return some JSON. Just because you can.

Building a printserver in PL/SQL for APEX with PLPDF

When our customer purchased a licence for PLPDF I started to think about building a simple printserver for APEX in pure PL/SQL.

That should be simple, right? Well, let’s see.

The first step I took was to investigate the full HTTP request APEX makes to its printerengine.

For this I used node.js. Yes, I could have used any sniffer available and APEX also gives you the XML, but building it myself in node.js was more fun. The catcher writes the request to a textfile named body.txt.

File: index.js

var server = require("./server");
var router = require("./router");

server.start(router.route);

File: server.js
var http = require("http"),
  url = require("url");

var constants = {"Port": "80"};

function start(route) {
  http.createServer(function (request, response) {
    route(url.parse(request.url).pathname, request, response);
  }).listen(constants.Port, function () {
    console.log("Listening on " + constants.Port);
  });
}

exports.start = start;

File: router.js
var fs = require('fs');

function route(pathname, request, response) {
  console.log('In de router: ');
  console.log('Path: ' + pathname);
  console.log('Method:' + request.method);
  //console.log(request);
  var body = '';

  request.on('data', function (chunck) {
    body += chunck;
  });

  request.on('end', function () {
    fs.writeFile('body.txt', body, function (err) {
      if (err) {
        return console.log(err);
      }
      response.writeHead(200, {"Content-Type": "text/plain"});
      response.write(request.toString());
      response.end();
    });
  });
}

exports.route = route;

I started the catcher via

  node index.js

and ajusted the Apex Instance settings as admin to the following:
Print Server : Oracle BI Publisher  –> Apache FOP will also work
Print Server protocol : HTTP
Print Server Host Adress: The hostname of my machine
Print Server Port : 80 The Port where the catcher is listening
Print Server Script : /<dad>/converttopdf

After that I created an application with a Report Query and a simple page with a button to print. How to do that is out of the scope of this blog.

After pressing the print-button the catcher wrote the follwing information:

Path: /<dad>/converttopdf
Method: POST
xml=<?xml version="1.0" encoding="UTF-8"?>
<DOCUMENT>
   <DATE>01-FEB-13</DATE>
   <USER_NAME*****USER_NAME>
   <APP_ID*****APP_ID>
   <APP_NAME>*****</APP_NAME>
   <PAGE_ID>1</PAGE_ID>
   <TITLE>*****</TITLE>
   <REGION ID="some number">
      <ROWSET>
         <ROW>
            <NAME>*****</NAME>
            <EAN_GLN>*****</EAN_GLN>
         </ROW>
         ...
      </ROWSET>
   </REGION>
</DOCUMENT>
&template=<?xml version ....
..
</xsl:stylesheet>
&_xtype=xsl-fo&_xf=pdf

So, APEX used a POST method to /<dad>/converttopdf using a body with the following four parameters
xml, template, _xtype, _xf

So, I should be able to create a procedure converttopdf using these named parameters…. not. _xtype and _xf are not allowed as parameternames in PL/SQL. Of course you could do something like

create or replace procedure converttopdf
  ( "xml" in varchar2
  , "template" varchar2
  , "_xtype" in varchar2
  , "_xf" in varchar2
  );

but I tried it and it didn’t work eventually.

So I tried some old school flexible naming:

create or replace procedure converttopdf
  (   name_array  IN  owa.vc_arr
  ,   value_array IN  owa.vc_arr
  )

and altered the Print Server Script setting to:
/<dad>/!converttopdf –> notice the exclamation

Now make sure that APEX_PUBLIC_USER can “see” the procedure (grants, synonyms) and that APEX allows you to call the new procedure in the URL by altering the

apex_xxxx.wwv_flow_epg_include_mod_local function

Now, the only thing we shoud do is altering Print Server Host Adress to the hostname of the webserver.

And it works! When hitting the print button you will get the XML that’s generated by APEX in your procedere. With PLPDF its relatively easy to generate the pdf
in a blob and send it back with the respons:

create or replace procedure converttopdf
(   name_array  IN  owa.vc_arr
,   value_array IN  owa.vc_arr
)
is
  /* Proof of concept */
  l_blob          blob;
  l_font          plpdf_type.t_row_font;
  l_heading_font  plpdf_type.t_row_font;
  l_row_aligns    plpdf_type.t_row_aligns;
  l_row_borders   plpdf_type.t_row_borders;
  l_row_datas     plpdf_type.t_row_datas;
  l_row_fonts     plpdf_type.t_row_fonts;
  l_row_maxlines  plpdf_type.t_row_maxlines;
  l_row_widths    plpdf_type.t_row_widths;
  l_header_widths plpdf_type.t_row_widths;
  l_titel         varchar2(2000);
  l_xml           xmltype;
  l_xml_chr       varchar2(32767);
  --
  procedure reset_row
  is
  begin
    l_row_datas.delete;
    l_row_widths.delete;
    l_row_borders.delete;
    l_row_aligns.delete;     
    l_row_fonts.delete;    
    l_row_maxlines.delete;     
  end; 
  --
  procedure add_column
    ( p_naam in varchar2
    , p_id   in number
    )
  is
  begin
    if p_naam is not null
    then
      l_row_datas(p_id)    := p_naam;
      if NOT l_header_widths.exists(p_id)
      then
        l_row_widths(p_id)   := length(p_naam)*5;
        l_header_widths(p_id) := l_row_widths(p_id);
      else
        l_row_widths(p_id)   := l_header_widths(p_id);
      end if;
      l_row_borders(p_id)  := 1;
      l_row_aligns(p_id)   := 'L';     
      l_row_fonts(p_id)    := l_font;    
      l_row_maxlines(p_id) := 10;     
    end if;  
  end ;
begin
  l_xml_chr := value_array(1);
  l_xml := xmltype(l_xml_chr);
  --
  l_heading_font.family := 'Arial';
  l_heading_font.style  := '';
  l_heading_font.fsize  := 10;
  -- 
  plpdf.init;
  plpdf.NewPage;
  plpdf.SetLeftMargin(5);
  plpdf.SetPrintFont
    ( p_family => l_heading_font.family
    , p_style  => 'B'
    , p_size   => l_heading_font.fsize
    );
  select extractvalue(l_xml,'/DOCUMENT/TITLE') into l_titel from dual;  
  l_titel := coalesce(l_titel,'No title supplied');
  plpdf.PrintCell
    ( p_txt => 'Rapport '||l_titel||' of '
    ||to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') 
    );
  plpdf.LineBreak();
  plpdf.LineBreak();  
  FOR r_rows IN
    ( SELECT rownum as id
      ,      extract(column_value,'/ROW/*[1]').getRootElement()  as column_name_1
      ,      extractvalue(column_value,'/ROW/*[1]')              as column_value_1
      ,      extract(column_value,'/ROW/*[2]').getRootElement()  as column_name_2
      ,      extractvalue(column_value,'/ROW/*[2]')              as column_value_2
      ,      extract(column_value,'/ROW/*[3]').getRootElement()  as column_name_3
      ,      extractvalue(column_value,'/ROW/*[3]')              as column_value_3
      ,      extract(column_value,'/ROW/*[4]').getRootElement()  as column_name_4
      ,      extractvalue(column_value,'/ROW/*[4]')              as column_value_4
      ,      extract(column_value,'/ROW/*[5]').getRootElement()  as column_name_5
      ,      extractvalue(column_value,'/ROW/*[5]')              as column_value_5
      ,      extract(column_value,'/ROW/*[6]').getRootElement()  as column_name_6
      ,      extractvalue(column_value,'/ROW/*[6]')              as column_value_6
      ,      extract(column_value,'/ROW/*[7]').getRootElement()  as column_name_7
      ,      extractvalue(column_value,'/ROW/*[7]')              as column_value_7
      ,      extract(column_value,'/ROW/*[8]').getRootElement()  as column_name_8
      ,      extractvalue(column_value,'/ROW/*[8]')              as column_value_8
      ,      extract(column_value,'/ROW/*[9]').getRootElement()  as column_name_9
      ,      extractvalue(column_value,'/ROW/*[9]')              as column_value_9
      ,      extract(column_value,'/ROW/*[10]').getRootElement()  as column_name_10
      ,      extractvalue(column_value,'/ROW/*[10]')              as column_value_10
      FROM   TABLE(xmlsequence(l_xml.extract('//DOCUMENT/REGION/ROWSET/ROW'))) t
    )
  LOOP
    if r_rows.id = 1
    then
      reset_row;
       plpdf.SetPrintFont
        ( p_family => l_heading_font.family
        , p_style  => 'B'
        , p_size   => l_heading_font.fsize
        );
      add_column(initcap(r_rows.column_name_1),1); add_column(initcap(r_rows.column_name_6),6);
      add_column(initcap(r_rows.column_name_2),2); add_column(initcap(r_rows.column_name_7),7);
      add_column(initcap(r_rows.column_name_3),3); add_column(initcap(r_rows.column_name_8),8);
      add_column(initcap(r_rows.column_name_4),4); add_column(initcap(r_rows.column_name_9),9);
      add_column(initcap(r_rows.column_name_5),5); add_column(initcap(r_rows.column_name_10),10);
      --
      plpdf.Row_Print4
        ( p_data    => l_row_datas
        , p_border  => l_row_borders
        , p_width   => l_row_widths
        , p_align   => l_row_aligns
        , p_font    => l_row_fonts
        , p_maxline => l_row_maxlines
        ); 
      --
      plpdf.SetPrintFont
        ( p_family => l_heading_font.family
        , p_style  => null
        , p_size   => l_heading_font.fsize
        );         
    end if;
    --
    reset_row;
    add_column(r_rows.column_value_1,1); add_column(r_rows.column_value_6,6);
    add_column(r_rows.column_value_2,2); add_column(r_rows.column_value_7,7);
    add_column(r_rows.column_value_3,3); add_column(r_rows.column_value_8,8);
    add_column(r_rows.column_value_4,4); add_column(r_rows.column_value_9,9);
    add_column(r_rows.column_value_5,5); add_column(r_rows.column_value_10,10);                        
    --
    plpdf.Row_Print4
    ( p_data    => l_row_datas
    , p_border  => l_row_borders
    , p_width   => l_row_widths
    , p_align   => l_row_aligns
    , p_font    => l_row_fonts
    , p_maxline => l_row_maxlines
    );
  END LOOP;
  --  
  plpdf.SendDoc(l_blob);
  -- 
  htp.init;
  owa_util.mime_header('application/pdf', false);
  htp.p('Content-Disposition: attachment; filename="'||l_titel||'.pdf"');
  htp.p('Content-Length: ' || dbms_lob.getlength(l_blob));
  owa_util.http_header_close;
  wpg_docload.download_file(l_blob);
exception
  when others
  then
    htp.p(sqlerrm);
end;

What are the drawbacks?

Just look at the definition of the parameters of converttopdf. The array can just hold up to 32K values.
If you try to print something that’s more than 32K in size it won’t work. But for some simple reports for which the input data
doesn’t become larger that 32K, this concept does work.

Ajax call returned server error with QMS

We are building a new Apex frontend on an existing former Oracle Forms application.  After calling some packaged procedures from en Dynamic Action we were confronted with an “Ajax call returned server error”. It seems that an exception raised by the database but invoked by a DA bypasses the central exception handler function you can set in the application properties.

The problem is, that with QMS, a lot of checks are implemented using QMS$ERRORS, so the top-error returning to the “client” is an ORA-20998.  This is not however the real message. You have to walk the error-stack to find the real error.

I have created a packaged procedure DA_ERROR  (in the package APX_VAL, but that is not that important):

PROCEDURE DA_ERROR
IS
  l_err_rec     hil_message.message_rectype;
  l_has_errors  BOOLEAN := TRUE;
  l_error       varchar2(32676);
begin
  l_error := SQLERRM;
  if ( l_error like '%-20998%' )
  THEN
    WHILE l_has_errors
    LOOP
      cg$errors.pop(l_err_rec);
      IF ( l_err_rec.msg_code IS NOT NULL )
      THEN
        l_error := l_err_rec.msg_code || ': ' || l_err_rec.msg_text;
      ELSE
        l_has_errors := FALSE;
      END IF;
    END LOOP;
    cg$errors.cg$err_tab_i := 1;
  end if;
  --
  if ( l_error is not null )
  then
    htp.p('{"error":"'||l_error||'"}');
  end if;
end;

With this procedure in place we can alter our code in :

begin
  call the old procedure
exception
  when others
  then
    apx_val.da_error;
end;

This still doesn’t show the message in the normal errorposition, but the alert that’s raised is more informative that the ORA-20998

Using BIRT with Apex for PDF reports

One of the frequently asked questions in the Apex world is “How can I create a PDF report without BI Publisher”. The answers are always the same “use Apache FOP”, “use JasperReports”, but there is one solution that is not frequently given and that is “look at BIRT”. BIRT is an Eclipse-based open source reporting system for web applications, especially those based on Java and Java EE. BIRT has two main components: a report designer based on Eclipse, and a runtime component that you can add to your app server. BIRT also offers a charting engine that lets you add charts to your own application.

Installation

The BIRT site gives an excellent explanation how to install the Birt Report Designer in your Eclipse IDE or how to install a dedicated report designer, based on the same IDE. So no use for me to explain this. I will suffice to say that de report Designer is a Perspective within the IDE ( Window>Open Perspective > Other> Report Design).

The BIRT site also gives a lot of examples how to create a report. The report eventually will be a file with a rptdesign extension. In a nutshell, it goes like this:

  • Create a new project
  • Use as wizard the Business Intelligence and Reporting Tools > Report Project
  • Create a new report within the project using the appropriate template
  • Create the data source
  • Create the dataset
  • Drag and drop the dataset to the appropriate positions on the report
  • Do your layout thing
  • Save the report

Making a connection with Oracle

To create the data source that connects to an Oracle database you have to choose the JDBC Data Source option. However, after a fresh installation the Oracle Driver is not known yet. Click the button Manage Drivers…

making an connection

In the next screen you have to add the JAR file with the driver class. I had an ojdbc6.jar file from an Oracle client installation. As an alternative, if you have a JDeveloper or SQLDeveloper installation at hand, you should be able to find an ojdbcN.jar file.

After you added the JAR file, the Drivers tab will show you two extra drivers: oracle.jdbc.driver.OracleDriver and oracle.jdbc.OracleDriver. Edit the latter and fill in the template URL

edit driver

Now you can choose the Oracle driver in the data source creation wizard and you can fill in the right credentials

Runtime viewer

If you are like me you have always a Tomcat installation at hand. If not, it’s very simple to download at Apache Tomcat. On the BIRT-site you can download BIRT-runtime. From this zip-file, take the WebViewerExample and extract it to your Tomcat/webapps directory. Rename the directory to birt-viewer. Restart Tomcat and go to http://localhost:8080/ (or the dedicated Tomcatserver instead of localhost of course).
Choose the Manager. It should look something like this:

tomcat

Click the birt-viewer hyperlink and choose on the next page the View Example.

next page

But look at the URL

 http://localhost:8080/birt-viewer/frameset?__report=test.rptdesign&sample=my+parameter.

This gives us a clue how to integrate with Apex.

Your report must be copied to the birt-viewer root directory. Look in the BIRT documentation for the setting such that the servlet will look in an alternative location.

The ojdbcN.jar file you used within the IDE must be copied to the Tomcat/lib directory to enable the server to find the right driver. Restart Tomcat after you copied the file.

Integration with Apex

I created a simple report, connected as scott/tiger that selected everything from DEPT and with a parameter P_DEPTNO. The URL to test this report is

http://localhost:8080/birt-viewer/frameset?__report=departments.rptdesign&p_deptno=10&__format=pdf

The __format parameter in the URL controls, well, the format of course. You can also use values like doc and xls.

Knowing this, integration is rather trivial; just generate an hyperlink in any form that navigates to the URL with the right parameter(s).