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.

Kernel32.dll with Oracle 12c

In OS commands in Oracle using Kernel32.dll I explained a way to link the kernel32.dll library to execute host commands.

But what if you had to migrate to the latest 12c version (64 bits) and you don’t want to rebuild your application to use the new features at the same time.

Well, it seems that it is still working the way I explained, but with one addition:

In %ORACLE_HOME%\hs\admin\extproc.ora set EXTPROC_DLLS to ANY (or any value that is appropriate for you).

Without it you will get an ORA-28595: Extproc agent : Invalid DLL Path

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.

Drag and drop an excel file to an APEX collection.

On apex-plugin.com I found an excellent process type plugin Excel2Collection for uploading an excel file
into a collection. A nice piece of work from Anton Scheffer.

However, in this age of HTML5 our customer wanted to drag and drop his CSV-file instead of uploading it with a button.
So I took the plugin from Anton and tweaked it such that with older browser you get the original upload button, but with
the more current browsers you will be able to drag and drop your file.

First of all you have to follow the instructions that come with the plugin (create a filebrowser item, create a process etc.)
But then….

1. You need a container to drop your file.
I created an HTML Text Region with the source

<div id="dropbox">
<span id="droplabel">Drop file here...</span>
</div>

2. You need some styling. I used

#dropbox {
  width: 425px;
  height: 300px;
  border: 2px solid #DDD;
  -moz-border-radius: 8px;
  -webkit-border-radius: 8px;
  background-color: #FEFFEC;
  text-align: center;
  color: #BBB;
  font-size: 2em;
  font-family: Arial, sans-serif;
}

#droplabel {
  position: relative;
  top: 40%;
}

3. This step depends on what you want to do with your collection. here, we are just showing the results.
To show the collection you can create a classic report, based on the collection you will be filling:
eg. to show the contents of a CSV file with three columns:

  select c001
  ,      c002
  ,      c003
  from   apex_collections
  where  collection_name = 'EDWINS_COLLECTION'

The name of the collection is of course the same name you entered in the attribute of the plugin.

4. The magic I used in the footer of the page

if (window.File && window.FileList && window.FileReader) {
  // Our functionality is supported by the browser
  var dropbox = document.getElementById("dropbox");
  $("#P1_FILE").hide(); //The Id of the filebrowser item
  $('#P1_SUBMIT_BUTTON').hide();
  //
  // If the browser doesn't support draganddrop we have to
  // show the filebrowser and a submit button
  // when supported, don't show them
  //
  dropbox.addEventListener("dragenter", dragEnter, false);
  dropbox.addEventListener("dragexit", dragExit, false);
  dropbox.addEventListener("dragover", dragOver, false);
  dropbox.addEventListener("drop", drop, false);
}
else {
  $("#dropbox").hide(); //No drag and drop supported. Hide the container
}

function dragEnter(evt) {
  evt.stopPropagation();
  evt.preventDefault();
}

function dragExit(evt) {
  evt.stopPropagation();
  evt.preventDefault();
}

function dragOver(evt) {
  evt.stopPropagation();
  evt.preventDefault();
}

function drop(evt) {
  var files, count;
  evt.stopPropagation();
  evt.preventDefault();

  files = evt.dataTransfer.files;
  count = files.length;
  if (count > 0)
    handleFiles(files);
}

function handleFiles(files) {
  var file, reader;
  file = files[0];
  $("#droplabel").html("Processing " + file.name);
  reader = new FileReader();
  reader.readAsDataURL(file);
  reader.onloadend = handleReaderLoadEnd;
}

function handleReaderLoadEnd(evt) {
  // This callback will insert the base64 results of the filereader
  // in the CLOB001 column of the CLOB_CONTENT collection
  var clobObj = new apex.ajax.clob( function(p){
    if (p.readyState == 4){
      var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=dummy',0);
      gReturn = get.get();
      apex.submit('TEST');
     //Fire the submit to get the proces plugin working
    }
  });
  clobObj._set(evt.target.result);
}

So when the file is dropped in the container area, the CLOB001 column is filled with the base64 string of the file.
Now alter the code of the plugin from:

 begin
    select aaf.id
         , aaf.blob_content
         , aaf.filename
    into t_file_id
       , t_document
       , t_filename
    from apex_application_files aaf
    where aaf.name = t_filename;
    --
    delete from apex_application_files aaf
    where aaf.id = t_file_id;
    --
    log( 'retrieved!'  );
  exception
    when no_data_found
    then
      raise e_no_doc;
  end;

to

 begin
    select aaf.id
         , aaf.blob_content
         , aaf.filename
    into t_file_id
       , t_document
       , t_filename
    from apex_application_files aaf
    where aaf.name = t_filename;
    --
    delete from apex_application_files aaf
    where aaf.id = t_file_id;
    --
    log( 'retrieved!'  );
  exception
    when no_data_found
    then
     log( 'looking in CLOB_CONTENT'  );
     begin
       /* 
         We didn't use the filebrowser, but the container
       */
       select apex_web_service.clobbase642blob
                (dbms_lob.substr( col.clob001
                                ,  dbms_lob.getlength(col.clob001)
                                , dbms_lob.instr(col.clob001,',',1,1) + 1 
                                )
                )
       into   t_document
       from   apex_collections col
       where  col.collection_name = 'CLOB_CONTENT'
       and    col.clob001 is not null ;
       --
       log ('Found');
     exception
       when no_data_found
       then
         log('Not found');
         raise e_no_doc;
     end ;
  end;

and you are done.