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



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:
Tijdstempel van toepassing: 35aa8e2f
Naam van foutmodule: ORA805.dll
Versie van foutmodule:
Tijdstempel van foutmodule: 3b1f22b0
Uitzonderingscode: c0000005
Uitzonderingsmarge: 000b4f04
Versie van besturingssysteem: 6.1.7601.


Fatal NI connect error 12203, connecting to:

TNS for 32-bit Windows: Version – Production
Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version – 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


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:


Testing again with SQL*Plus became funky:

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

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

Enter user-name: *****
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.

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
  if p_status not in ( 'AVAILABLE'        , 'AVAILABLE_W_EDIT_LINK', 'DEVELOPER_ONLY'
                     , 'RESTRICTED_ACCESS', 'UNAVAILABLE'          , 'UNAVAILABLE_PLSQL'
	             , 'UNAVAILABLE_URL')
    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
      ( p_security_group_id => r_i.workspace_id
      ( p_flow_id     => r_i.application_id
      , p_flow_status => p_status
  end loop;

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
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
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.
Here you can set some developer specific preferences, like


This will change the original panel to

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-00600: with arguments: [12811], [154970]

We encountered this bug on Windows Enterprise Edition.

When dropping a table and you get a ORA-00 600 with arguments: [12811] and [154970], you will have to check your application for the following sequence of events:

– You created the table with an identity column GENERATED BY DEFAULT ON NULL AS IDENTITY
– you have code that tries to alter the value the sequence by an alter table statement in dynamic sql.

When the alter table results in an error, the sequence is dropped and you are stuck with a corrupted database.

Don’t do this in a production database, as we have ūüė¶

CREATE TABLE edwin_test2
      MINVALUE 1 MAXVALUE 9999999999999999999999999999 
      CREATION_DATE date, 
      CONSTRAINT edwin_test_pk2 PRIMARY KEY (ID)

  l_max_id number:= 10;
   insert into edwin_test2 
     ( creation_date
     ( sysdate
   l_max_id := -1;  -- force the error
   -- Let's blow things up...
   execute immediate 
   'alter table edwin_test2  modify id number '
   ||'generated by default on null as identity '
   ||'( start with ' ||l_max_id || ' )';

drop table edwin_test2;

You can check if you’re have this corruption by the following script:

  cursor c_seq
    ( cp_name in varchar2
    select 'exists' bla
    from   dba_sequences seq
    where  seq.sequence_name = cp_name
  r_seq c_seq%rowtype;
  for r_i in 
    ( select col.table_name
      ,      col.data_default as seq
      from   dba_tab_columns col
      where  col.identity_column = 'YES'
      and    not exists ( select 1
                          from   dba_views vw
                          where  vw.view_name = col.table_name
    r_i.seq := substr( r_i.seq, instr(r_i.seq,'.')+2);
    r_i.seq := substr( r_i.seq, 1, instr(r_i.seq,'"')-1);   
    r_seq:= null;
    open c_seq(r_i.seq);
    fetch c_seq into r_seq;
    close c_seq;
      ( rpad(r_i.table_name,40,' ')
      ||' '
      ||nvl(r_seq.bla, ' ------ohoh!')
  end loop;

To handle this corruption, just open a SR with Oracle. They should be able to help you.

No, recreating the sequence by hand doesn’t work. I tried. The sys.seq.FLAGS column has¬†a value of 8 for a hand-created sequence and 40 for an identity column; I see no problem in selecting from the datadictionairy, but updating it is even for me a big NO.


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:

,{title:'DEPT ',height:'500',width:'720',maxWidth:'960',modal:true,dialog:null}

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:

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

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.

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
( select sys_guid() as l
  from dual
) sub

The statement gave two different results!
So sub.l is not equal to 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
( 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
  return edwin_seq.nextval;

select l + l as sum
,      l
(  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
  return edwin_seq.nextval;

select l + l as sum
,      l
(  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 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 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”

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 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
  • Right-click the green lock and go to the certificates. This one is in Dutch but you will get the picture.
  • 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 *

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

  p_url            varchar2(2000):= '';
  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';
  utl_http.set_detailed_excp_support ( true );  
  l_http_request  := utl_http.begin_request
                       ( url=>p_url||'?token='||token
                       , method => 'POST'
    ( r      =>  l_http_request
    , name   =>  'Content-Type'
    , value  =>  'application/x-www-form-urlencoded'
    ( r      =>   l_http_request
    , name   =>   'Content-Length'
    , value  =>   length(l_param)
    ( r      =>   l_http_request
    , data   =>   l_param
  l_http_response := UTL_HTTP.get_response(l_http_request);
      UTL_HTTP.read_text(l_http_response, l_text, 32766);
      DBMS_OUTPUT.put_line (l_text);
    WHEN utl_http.end_of_body 

Happy testing!

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 mirrors the investigation I did.

    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


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

    EDWIN           no archival
    EDWIN_BENCHMARK with archival    
  cursor c_prop
    from   tab$ t
    ,      obj$ o
    where  o.obj# = t.obj#
    and = '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
    l_power  number;
    l_return varchar2(4000);
    for l_i in 0..126
      l_power := power(2,l_i);
      if ( l_power > p_n )
      end if;
      if bitand(p_n,l_power) != 0
        l_return := l_return||' '||l_i;
      end if;
    end loop;
    return trim(l_return);
  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
      ||     decode( bitand(, power(2,47))
                   , 0, ' no archival'
                   , ' with archival'
                   ) as text
      from   tab$ t
      ,      obj$ o
      where  o.obj# = t.obj#
      and in ('EDWIN','EDWIN_BENCHMARK')
  end loop;             

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


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(
          if (p.readyState == 4){
            var get = new htmldb_Get(null,$v('pFlowId')
            gReturn = get.get();
            alert('Mail has been send');
        clobObj._set(dataUrl );      
  } else {
    var get = new htmldb_Get(null,$v('pFlowId')
    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.

  cursor c_variables
    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
    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;
  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
    if ( c_variables%ROWCOUNT = 1 ) 
      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'
      ( 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||''
      , p_from => ''
      , p_subj => 'Issue for application '
                 ||:APP_ID||' Page '||:APP_PAGE_ID
      , p_body => l_body
  if dbms_lob.getlength(l_blob) > 0
     ( p_mail_id    => l_id
     , p_attachment => l_blob
     , p_filename   => 'screenprint.jpg'
     , p_mime_type  => 'image/jpeg'
  end if;

And again, that’s it!