Oracle Forms 6i with Oracle Cloud Infrastructure

Everybody wants to work with the latest tools, the latest feautures. But the world of enterprise IT isn’t like that.

My customer started to move all of his Oracle Databases to the Oracle Cloud Infrastructure (OCI). But one of these databases had an Oracle Forms C/S frontend .
We are talking Oracle Forms 6i client/server on Windows, 100% generated with Oracle Designer.
We could rebuild/redesign the old application to Oracle Apex, but that would be an entire project by itself.
But first we investigated if it was possible to connect this software from 1995 to the 21th century.

This is the road I traveled:

Step 1: Disable SQL*Net

First I tried making a connection with SQL*Plus from the same Oracle-home as Forms to the new database:

ORA-12660: Encryption or crypto-checksumming parameters incompatible

A standard OCI Database installation enables secure SQL*Net. So we have to disable that:

we are not using an Autonomous database, so we still have a “server” we can connect to in OCI.
On this database database server secure SQL*Net is enabled by

/u01/app/oracle/product/****/dbhome_1/network/admin/sqlnet.ora

SQLNET.ENCRYPTION_SERVER=REQUIRED
SQLNET.CRYPTO_CHECKSUM_SERVER=REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER=(AES256,AES192,AES128)
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER=(SHA1)
SQLNET.ENCRYPTION_CLIENT=REQUIRED
SQLNET.CRYPTO_CHECKSUM_CLIENT=REQUIRED
SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256,AES192,AES128)
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA1)

This was removed.

Step 2: Correcting the characterset of the database

Testing, again with SQL*Plus, the executable crashed big time with a General Protection Fault.
[Sorry, dutch only]

Gebeurtenisnaam van probleem: APPCRASH
Naam van de toepassing: PLUS80.exe
Versie van toepassing: 0.0.0.0
Tijdstempel van toepassing: 35aa8e2f
Naam van foutmodule: ORA805.dll
Versie van foutmodule: 0.0.0.0
Tijdstempel van foutmodule: 3b1f22b0
Uitzonderingscode: c0000005
Uitzonderingsmarge: 000b4f04
Versie van besturingssysteem: 6.1.7601.2.1.0.256.48

Sqlnet.log

Fatal NI connect error 12203, connecting to:
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle80)(ARGV0=oracle80ORCL)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))’)))(CONNECT_DATA=(SID=ORCL)(CID=(PROGRAM=***\ifrun60.EXE)(HOST=*****)(USER=****))))

VERSION INFORMATION:
TNS for 32-bit Windows: Version 8.0.6.0.0 – Production
Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 8.0.6.0.0 – Production
Time: 01-FEB-19 09:29:12
Tracing not turned on.
Tns error struct:
nr err code: 12203
TNS-12203: TNS:unable to connect to destination
ns main err code: 12560
TNS-12560: TNS:protocol adapter error
ns secondary err code: 0
nt main err code: 530
TNS-00530: Protocol adapter error
nt secondary err code: 0
nt OS err code: 0

After some research I found out that the characterset of the database was the culprit, so we provisioned a cloud database with the same characterset as the old on-premises database

WE8ISO8859P1

Step 3: Allowed versie of the database

To connect with an older client to database with a higher version, you have to allow this in the sqlnet.ora on the serverside. So we did:

SQLNET.ALLOWED_LOGON_VERSION_SERVER = 8

Testing again with SQL*Plus became funky:

SQL*Plus: Release 8.0.5.0.0 – Production on Fri Feb 1 14:49:43 2019

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Enter user-name: *****
ERROR:
ORA-03137: malformed TTC packet from client rejected: [kpoal8Check-5] [0]
[0x000000000] [0x000000000] [0] [0] [2080] []

First I ignored this “notification”, but later I found that IFRUN60.EXE got the same message and stopped. So I had to address the problem.

It seems that in database version 12.2 some extra checks where build in SQL*Net (Bug 26092744) causing that older clients can’t connect any more to the database:
the on-premises database was 12.1, the Cloud 12.2.

So we provisioned a 12.1 database with the correct characterset with secure sqlnet disabled in OCI

SQL*Plus stopped complaining and we were able to connect with Oracle Forms.

We are now in production with this configuration, and the responstime within the screens are beter than using the on-premises database.
We stille have some issues of course:

  • We have to end-of-life the Forms application.
  • 12.1 will be desupported by Oracle
  • WE8ISO8859P1 is missing some vital characters ( euro sign).

Our migration pad will be to migrate to Oracle Apex on a 19.2+ database version with UTF8 as characterset with secure SQL*Net.

But that will be another story.

Using a serviceworker with Oracle APEX

After a question from a colleague of mine about caching JavaScript, css, images ed. in APEX I started to look at the new way : service workers.
With service workers we have the opportunity to manage caching programmatically with JavaScript.

I’m not going to tell about service workers. There are a lot of people who know more about it and have excellent posts on blogs and YouTube.
This post is more about how I implemented a service worker in a website I developed.

First, in the template of the LOGIN page I added a script section with the code:

function printState(state) {
  console.log(state);
}
if ('serviceWorker' in navigator) {
  navigator.serviceWorker.register('/m2b_service_worker.js', {
    scope : './'
  }).then(function (registration) {
    var serviceWorker;
    if (registration.installing) {
      serviceWorker = registration.installing;
      printState('installing');
    } else if (registration.waiting) {
      serviceWorker = registration.waiting;
      printState('waiting');
    } else if (registration.active) {
      serviceWorker = registration.active;
      printState('active');
    }
    if (serviceWorker) {
      printState(serviceWorker.state);
      serviceWorker.addEventListener('statechange', function (e) {
        printState(e.target.state);
      });
    }
  }).catch (function (error) {
    printState(error);
  });
}

With this script I registered the service worker file m2b_service_worker.js. Notice that the printState function is just some overhead for debugging.
This seems all to simple, but it has one small pitfall: scoping.

As an APEX developer I wanted to upload the script as static file in the framework. However, when you do that the maximum scope of the caching would be something like domain/pls/dad/workspace/r/….
That’s no good. I also want to cache static files like domain/i/apex.min.css and those files are out of the mentioned scope. Uploading it as a static file will result in a console.log message:

DOMException: Failed to register a ServiceWorker: The path of the provided scope ('/i/') is not 
under the max scope allowed ('/pls/apex/workspace/r/****'). Adjust the scope, move the Service 
Worker script, or use the Service-Worker-Allowed HTTP header to allow the scope.

Luckily I use a reverse proxy in front of our ORDS so I was able to install the script at the root of the reverse proxy and register it at root /, such that all requests to the domain could be cached if necessary.

The Service Worker:

var
VERSIE = "2",
FILES = [
  '/i/myimage.gif',
  'offline.html'
],
CACHENAME = 'omy-cache-' + VERSIE,
EXTENTIES = ['gif', 'jpg', 'ico', 'css', 'js', 'png'];

self.addEventListener('install', function (event) {
  event.waitUntil(caches.open(CACHENAME).then(function (cache) {
      return cache.addAll(FILES);
    }));
1});

self.addEventListener('activate', function (event) {
  return event.waitUntil(caches.keys().then(function (keys) {
      return Promise.all(keys.map(function (k) {
          if (k != CACHENAME && k.indexOf('omy-cache-') == 0) {
            return caches.delete (k);
          } else {
            return Promise.resolve();
          }
        }));
    }));
});

self.addEventListener('fetch', function (event) {
  var isGet = event.request.method;
  
  event.respondWith(
    caches.match(event.request)
    .then(function (response) {
    // Cache hit - return the response from the cached version
      if (response) {
        return response;
      }

    // Not in cache - return the result from the live server
      return fetch(event.request)
          .then(function (response) {
        var 
      shouldCache = false,
      reqWithoutQuery = event.request.url.split("?")[0],
          ext = reqWithoutQuery.split(".").pop();
      
    if (EXTENTIES.indexOf(ext) >= 0 ) {
      shouldCache = true;
    }
        if (shouldCache) {
      //before we return the response from the server
      //we cache the response for the next time
          return caches.open(CACHENAME).then(function (cache) {
            cache.put(event.request, response.clone());
            return response;
          });
        } else {
          return response;
        }
      }).catch(function(error){
      // Is I understand it, fetch throws an exception when offline
      // but a valid HTTP response, e.g. 404, will go tho then(), not to catch()
      return caches.open(CACHENAME).then(function(cache){return cache.match('offline.html');});
    });
    }));
});

With APEX you don’t want to cache all GET requests. E.g. all GETs from \f are dynamic, dependent from session state. Your application will behave not as expected when you’ll cache \f.
I only want to cache the components that are truly static. Hence the array with exceptions.
I also want to show a static file [offline.hml] when the user has no internet connected, to increase user experience. This static file (and its image) is added to the cache on installation of the service worker.

The meat of the worker is the fetch event. When the request is found in the cache, the cached response is returned. When the request is unknown in the cache, the request is fetched from the server and when the requested item is within the array, it is cached for the next cycle.

When you look in Developer Tools > Application > Cache Storage you will notice your new cache with all the static files that were cached.

To emulate an offline connection, just set the checkbox “offline” in Network and hit F5. This should serve the mentioned offline.html from cache.

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”.

ora-01403 in wwv_flow_api.import_begin

Two new databases, two fresh new apex installations. Yeah! One for development, one for test. Let’s create a new Workspace X in every environments.
Everything is ready, let the development begin!

After a while the first iteration of the application was ready and we imported it in the the second environment and everything failed miserably.

The site exploded in an error page giving the dreaded ORA-01403 after the first statement in the install script WWV_FLOW_API.IMPORT_BEGIN.

But after running the script in SQLPlus with spooling enabled I got the following :

ORA-02291: Integriteitsbeperking (APEX_050000.WWV_FLOWS_FK) is geschonden – bovenliggende sleutel
is niet gevonden.
ORA-06512: in “APEX_050000.WWV_FLOW_API”, regel 2750
ORA-06512: in regel 2

That’s dutch for an integrity constraint violation, parent key not found

Digging into dba_constraints it gave me that the workspace was not found.

The provisioning_company_id of wwv_flow_companies for workspace X had a different value than development!

Note to myself: when deploying into a new environment, don’t create the workspaces by hand, but import it from a source installation.

Note to the APEX development team: it wouldn’t kill you when you stop the process with a simple message like ‘Workspace-id XXXXXX not found’ instead of “I didn’t find it” without giving the “it” some meaning.

Node.js scripts for Oracle Cloud Storage Service

Working with Oracle Cloud Storage Service I noticed that it’s not really customer-ready (in my humble opinion).

e.g. Creation of a storage container is not yet supported from the dashboard. You’ll have to create a container using a magical Java library or a REST-API using Curl.

But we are on Windows.

So we don’t have Curl.

And I refused to install Cygwin just for this purpose.

However, node.js is installed in our Windows environment, so I created a small repository of node.js scripts to handle some of the basics of the Oracle Cloud Storage Service.

For everybody who is interested : https://github.com/emoracle/OracleCloudStorage

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.

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.

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!