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.

gReport.search in APEX 5

yes, gReport was undocumented, but it was sooo handy when you wanted to refresh your interactive report from within javascript.

Just google for it and you will find dozens of examples using gReport.search(‘SEARCH’);

And then you upgraded to APEX 5

Because now we can have multiple IR’s on one page and the team made the IR a jquery widget, gReturn no longer exists.

It’s a widget, so this has worked for me:

 

$('#your_static_id_of_the_report_ir').data('apex-interactiveReport')._search();

So If your static Id is “batchRuns” it becomes:

  $('#batchRuns_ir').data('apex-interactiveReport')._search();

And of course, it’s not documented, you’re not supposed to use the private method, but until the APEX team creates a public method to refresh my IR given the static Id of the region, I will be using this.

Until the next upgrade…

 

Be careful with dual and functions

Because I wanted an UUID I played around with sys_guid() and stumbled upon something that was new for me.

select substr(l,1,8)   ||'-'||substr(l,9,4)||'-'
     ||substr(l, 13, 4)||'-'||substr(l,17, 4)||'-'
     ||substr(l,21, 12) as final
,     l
from
( select sys_guid() as l
  from dual
) sub

The statement gave two different results!
So sub.l is not equal to sub.final minus the minus signs.

But when I do this:

select substr(l,1,8)   ||'-'||substr(l,9,4)||'-'
     ||substr(l, 13, 4)||'-'||substr(l,17, 4)||'-'
     ||substr(l,21, 12) as final
,     l
from
( select sys_guid() as l
  from other_table_with_1_row
) sub

The results are equal.

My hypothesis was that the fast dual rewrite replaces every occurrence of sub.l with sys_guid(). So, sys_guid is handled six times as a constant, but of course it isn’t.

drop sequence edwin_seq;

create sequence edwin_seq start with 1 increment by 1 nocache;

drop function edwin_f;

create function edwin_f
return number
is
begin
  return edwin_seq.nextval;
end;  

select l + l as sum
,      l
from 
(  select edwin_f as l
   from   other_table_with_1_row
) sub

The resultset is that the sub.sum equals 2 and sub.l equals to 1.
But now do the same thing with dual:

drop sequence edwin_seq;

create sequence edwin_seq start with 1 increment by 1 nocache;

drop function edwin_f;

create function edwin_f
return number
is
begin
  return edwin_seq.nextval;
end;  

select l + l as sum
,      l
from 
(  select edwin_f as l
   from   dual
) sub

Now sub.sum equals 3 and sub.l also equals to 3.
This is because the internal algorithm handles the function as a constant and the resultset is calculated as

first-column  = edwin_f + edwin_f = 1 + 2 = 3
second-column = edwin_f = 3

Every time the function is called the next value of the sequence is returned.

So, be careful with the use of dual in combination with functions that do not return constant values.

Oracle PL/SQL integration with Slack

Ever heard from Slack.com? Slack is the (not “a” but “the”) platform for team communication. It’s a sort of mixture of email, messaging and a lot more. Well, just go to their website. They are a lot better in explaining their own product of course.

For me it got interesting when I saw their API.
I came up with the idea of setting up a channel within our team and let our database post messages on this channel when some triggering event happen.

Now I get whatsapp-like messages on my mobile phone, tablets and desktops from our database. These are the things that makes me very happy!

This is how I did it:

Step 1 – Set up your company
Just go to Slack.com and follow the wizard. It is very self-explaining.

Step 2 – Set up a channel
You will get two channels: #random and #general.
For this demo I created a third channel #notifications by just clicking the “Create a channel” link. slack1

Step 3 – Configure the integration
Click on the name of your company and select “Configure integrations”
slack2

Just look at the wealth of services they offer. Scroll down to the “DIY Integrations & Customizations” and add a Slack API.
You will be directed to https://api.slack.com/. Just read it.
For now go to the section “Authentication” and add a token.

This token is what you need to authenticate the database as a client. (Yes, it will authenticate itself as you).

Step 4 – Get the certificate(s)
The API I use is a https-call that will be implemented in PL/SQL. For this we will need to store some certificates in an Oracle Wallet.

I use chrome as a browser. Other browsers have comparable functionality.

  • Go to https://api.slack.com/
  • Right-click the green lock and go to the certificates. This one is in Dutch but you will get the picture.
    slack3
  • Export the GeoTrust Global CA and the GeoTrust SSL CA-G2 certificates to file as Base64 encoded x.509 (*.cer)
    You don’t need the leaf certificate *.slack.com

Step 5 – Create a wallet
Using the Oracle Wallet Manager open your wallet or create a new wallet. You don’t need a certificate request.
slack4Under “Operations” choose Import Trusted certificate and import both *.cer files we created before.
slack5
Save the wallet to e.g. E:\oracle\Wallet, open the Wallet menu again and enable Auto login. If you don’t do this you will have to embed the password of the wallet in your PL/SQL.
slack6But what if you are using Oracle XE and you don’t have Wallet manager?
Than it’s good to know that the wallet is “just” an PKCS#12 file. Take a look at this excellent post https://blog.hazrulnizam.com/openssl-workaround-oracle-xe-wallet/ that explains how to use Openssl for creating the

So execute

  openssl pkcs12 -export -in slack.cer -out ewallet.p12 - nokeys 

and place this wallet in a directory. E.g. C:\Oracle\Wallet ( remember your password in this case. You will need it when you set your wallet in PL/SQL

Step 6 – Create a procedure in the database
The following PL/SQL implements the chat.postmessage API from slack to send a message to the #notifications channel. It uses the wallet in E:\oracle\Wallet with autologin enabled:

declare
  p_url            varchar2(2000):= 'https://slack.com/api/chat.postMessage';
  l_http_request   utl_http.req;
  l_http_response  utl_http.resp;
  l_text           varchar2(32767);
  l_token          varchar2(1000)  := 'thetokenfromtheslackapisite';
  l_param          varchar2(32676) := 'channel=#notifications'||chr(38)
                                    ||'text=Hello world from the database';
BEGIN
  utl_http.set_detailed_excp_support ( true );  
  utl_http.set_wallet('file:E:\oracle\wallet');
  -- 
  l_http_request  := utl_http.begin_request
                       ( url=>p_url||'?token='||token
                       , method => 'POST'
                       );
  utl_http.set_header
    ( r      =>  l_http_request
    , name   =>  'Content-Type'
    , value  =>  'application/x-www-form-urlencoded'
    );				
  utl_http.set_header 
    ( r      =>   l_http_request
    , name   =>   'Content-Length'
    , value  =>   length(l_param)
    );
  utl_http.write_text
    ( r      =>   l_http_request
    , data   =>   l_param
    );
  -- 
  l_http_response := UTL_HTTP.get_response(l_http_request);
  BEGIN
    LOOP
      UTL_HTTP.read_text(l_http_response, l_text, 32766);
      DBMS_OUTPUT.put_line (l_text);
    END LOOP;
  EXCEPTION
    WHEN utl_http.end_of_body 
    THEN
      utl_http.end_response(l_http_response);
  END;
EXCEPTION
  WHEN OTHERS 
  THEN
    utl_http.end_response(l_http_response);
    RAISE;
END;

Happy testing!

Clientside executables e.g. Forms from webpages

Customers will always come up with requirements that will make your head spin.
Some of our applications are of course Oracle APEX based, but there are still some
old client-side applications like Oracle Forms [yes, the ancient client-server version].

We created a web-based intranet with an application overview. With one click on the application the -web- application would start.

Of course they wanted to start the old Oracle Forms from this portal as well and it should work in all the main browsers.

First of all, all client machines are Windows (XP and 7)

Secondly, we were able to deploy a registry setting on all clients.

With the registry settings we defined a custom protocol

[HKEY_CLASSES_ROOT\edwin]
"URL Protocol"=""
@="URL:edwin Protocol"

[HKEY_CLASSES_ROOT\edwin\shell]

[HKEY_CLASSES_ROOT\edwin\shell\open]

[HKEY_CLASSES_ROOT\edwin\shell\open\command]
@="\"X:\\PROGS\\EdwinProtocol\\dispatcher.exe\" \"%1\""

The X-drive is a network drive that is available to all clients.

The dispatcher.exe is a fairly simple custom-made executable that
receives an argument, matches the argument in a configuration-file to an executable
and then starts that executable.

E.g. our configuration file looks like this

[PROTOCOL]
NAME=edwin://

[FORMA]
EXE=X:\Appl\Oracle8_clientT\BIN\ifrun60.EXE XXX00.fmx
WORKING_DIR=X:\progs\XXX\Produktion

With all this in place, starting up an executable from our intranet is as easy as creating a hyperlink with our custom protocol

edwin://FORMA

As always, that’s it.

Tables with row archiving in Oracle 12c … bit 47

This week I followed the Oracle 12c New Features course in the Netherlands by Harald van Breederode. In this course the new row archiving feature was mentioned. Very interesting feature, but if you want to look up which table has row archiving enabled, it seems that the only thing you can do is to look up all tables with an ORA_ARCHIVE_STATE column present.

That seems odd to me, because the feature is a property of the table, so CDB/DBA_TABLES should have a column indicating the feature. The following PL/SQL block, executed as sys in a 12.1.0.1.0 mirrors the investigation I did.

declare
  /* 
    Create a table in another schema.
    When you don't, the alter table will give an 
    ORA-12988: cannot drop column from table owned by SYS.
    and we are dropping the hidden column ORA_ARCHIVE_STATE 
    from our new table when we alter the row archival

    output:

    Version: 12.1.0.1.0
    Before : 140755205095424 Bits unset: 29 34 47
    After  :     17716740096 Bits unset: 29 34

    EDWIN           no archival
    EDWIN_BENCHMARK with archival    
  */
  cursor c_prop
  is
    select t.property 
    from   tab$ t
    ,      obj$ o
    where  o.obj# = t.obj#
    and    o.name = 'EDWIN'
  ;
  l_after   tab$.property%type;
  l_before  tab$.property%type;
  l_version v$instance.version%type;
  -- 
  function BITS_UNSET
    ( p_n number 
    ) return varchar2
  is
    l_power  number;
    l_return varchar2(4000);
  begin
    for l_i in 0..126
    loop
      l_power := power(2,l_i);
      -- 
      if ( l_power > p_n )
      then
        exit;
      end if;
      --
      if bitand(p_n,l_power) != 0
      then
        l_return := l_return||' '||l_i;
      end if;
    end loop;
    return trim(l_return);
  end;        
begin
  select version into l_version from v$instance;
  dbms_output.put_line('Version: '||l_version);
  --
  begin execute immediate 'drop table scott.edwin'; exception when others then null; end;
  begin execute immediate 'drop table scott.edwin_benchmark'; exception when others then null; end;
  --
  execute immediate 'create table scott.edwin_benchmark (kol number) row archival'; 
  execute immediate 'create table scott.edwin (kol number) row archival'; 
  --
  open c_prop;
  fetch c_prop into l_before;
  close c_prop;
  dbms_output.put_line('Before : '||l_before||' Bits unset: '||BITS_UNSET(l_before));
  --
  execute immediate 'alter table scott.edwin no row archival';
  --
  open c_prop;
  fetch c_prop into l_after;
  close c_prop;
  dbms_output.put_line('After  : '||l_after||' Bits unset: '||BITS_UNSET(l_after));  
  --
  for r_check in 
    ( select o.name
      ||     decode( bitand(t.property, power(2,47))
                   , 0, ' no archival'
                   , ' with archival'
                   ) as text
      from   tab$ t
      ,      obj$ o
      where  o.obj# = t.obj#
      and    o.name in ('EDWIN','EDWIN_BENCHMARK')
    )
  loop
   dbms_output.put_line(r_check.text);      
  end loop;             
end;    
/

So, the flag is bit 47 in the property column of tab$.

I guess we can wait for a future upgrade of 12c where they unlock this information. In the mean time, you can use the information above for a custom view….or just look for the existence of ORA_ARCHIVE_STATE.