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.

ORA-00600: with arguments: [12811], [154970]

We encountered this bug on Windows 12.1.0.2.0 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
   (  ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY 
      MINVALUE 1 MAXVALUE 9999999999999999999999999999 
      INCREMENT BY 1 START WITH 179193 CACHE 20 NOORDER  
      NOCYCLE  CONSTRAINT edw_con2 NOT NULL ENABLE, 
      CREATION_DATE date, 
      CONSTRAINT edwin_test_pk2 PRIMARY KEY (ID)
      USING INDEX  ENABLE
   ) NO INMEMORY ;

declare
  l_max_id number:= 10;
begin
   insert into edwin_test2 
     ( creation_date
     ) 
   values 
     ( sysdate
     );
   commit;
   -- 
   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 || ' )';
end;
/

drop table edwin_test2;

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

declare
  cursor c_seq
    ( cp_name in varchar2
    )
  is
    select 'exists' bla
    from   dba_sequences seq
    where  seq.sequence_name = cp_name
  ;
  r_seq c_seq%rowtype;
begin
  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
                        )
    )
  loop
    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;
    --- 
    dbms_output.put_line
      ( rpad(r_i.table_name,40,' ')
      ||r_i.seq
      ||' '
      ||nvl(r_seq.bla, ' ------ohoh!')
      );
  end loop;
end;               

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.

 

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

Preventing the usage of partitioning in Oracle

In the situation that you have installed the Partitioning option and you don’t want your developers to use this feature, you could use the installer or “chopt” to remove the option.

However, with these utilities you need to shut down the database and that is not always desirable.

You could also use an INSTEAD OF CREATE on DATABASE to prevent your developers to create partitioned tables:

create trigger wi_prevent_partitioning
instead of create on database
when (ora_dict_obj_type = 'TABLE')
declare
  l_sql      varchar2(32767);
  l_sql_list ora_name_list_t;
begin
  for r_i in 1 .. ora_sql_txt(l_sql_list)
  loop
    l_sql := l_sql||l_sql_list(r_i);
  end loop;
  --   
  if ( instr(lower(l_sql),'partition') > 0 )
  then
    raise_application_error
      ( -20999
      , 'Partitioning is not allowed'
      );
  else
    l_sql := replace(l_sql,chr(10),' '); 
    -- 
    l_sql := replace( l_sql
                    , 'CREATE TABLE '
                    , 'CREATE TABLE '
                    ||ora_login_user||'.'
                    );
    execute immediate l_sql;
  end if;
end;

A word of caution: I have tested this with success in a 11GR2 database with APEX 4.2.
In 10.2.0.4 with APEX 4.1 I got an ORA-600 error when I went to the application section of the builder.
So of course test, test, test.

Rollback after commit using flashback

Normally when someone asks if it is possible to rollback a single, complete transaction after a commit I have to say no. But this time I was confronted myself with this requirement. We are migrating an Oracle Forms client-server application to Oracle Apex and in one of the forms the transaction-handling was as follows:

  1. Execute some complex pl/sql that updates/inserts into several tables
  2. After completion the user can switch between several screens within the same session to look at several parameters.
  3. If necessary the user can rollback the complete transaction.

Well, in C/S the third step is just a simple ROLLBACK command, but in Apex life is not that simple.

In Apex a commit is issued after every pl/sql block – in our case a Dynamic Action- so step 3 is not that trivial any more. As we saw it we had 2 options:

  • Write some complex pl/sql that reverses the complex logic of the first step
  • Something else.

I went for “Something else”. The database version is an Oracle 10g EE, so I didn’t have all the new flashback features at hand, but it was enough to make something that works.

I created the following package like this:

create or replace package asl_transaction
as
  procedure capture_transaction
    ( p_session_id number
    );
  procedure reverse_transaction
    ( p_session_id number
    );
  procedure confirm_transaction
    ( p_session_id number
    );    
  procedure purge_old_transactions ;
end;
/
create or replace package body asl_transaction
as

type t_cache 
is
  table of rowid
  index by varchar2(2000)
;
g_cache t_cache;

procedure capture_transaction
  ( p_session_id number
  )
is
begin
  insert into apx_transaction
    ( session_id
    , xid
    , creation_date
    )
  select p_session_id
  ,      xid
  ,      sysdate
  from  v$transaction
  where addr = ( select taddr
                 from v$session
                 where sid = ( select sid
                               from v$mystat
                               where rownum = 1  ));
end;

procedure push_rowid
  ( p_rowid rowid
  )
is
begin
  g_cache(p_rowid) := p_rowid;
end;

function check_rowid
  ( p_rowid in rowid
  ) return rowid
is
begin
  if g_cache.exists(p_rowid)
  then
    return g_cache(p_rowid);
  else
    return p_rowid;
  end if;
end;

procedure reverse_transaction
  ( p_session_id number
  )
is
  l_new_rowid rowid;
begin
  savepoint inlineTransaction;
  for r_transactie in ( select xid 
                        from   apx_transaction 
                        where  session_id = p_session_id
                      )
  loop
    for r_dml in ( select rtrim(qry.undo_sql,';') as undo_sql
                   ,      row_id
                   from   sys.flashback_transaction_query qry
                   where  qry.xid         = r_transactie.xid
                   and    qry.table_name != 'ASL_APX_TRANSACTION'
                 )
    loop
      if upper(r_dml.undo_sql) like '%INSERT INTO%'
      then
        push_rowid(r_dml.row_id);
        l_new_rowid := null;
        execute immediate r_dml.undo_sql
        ||' returning rowid into :b1' returning into l_new_rowid;        
        g_cache(r_dml.row_id) := l_new_rowid;
      else
        l_new_rowid := check_rowid(r_dml.row_id);
        if ( l_new_rowid != r_dml.row_id)
        then
          r_dml.undo_sql := replace(r_dml.undo_sql,r_dml.row_id, l_new_rowid);
        end if;
        execute immediate r_dml.undo_sql;        
      end if;
    end loop;
  end loop;
  confirm_transaction(p_session_id);
end;

procedure confirm_transaction
  ( p_session_id number
  )
is
begin
  delete from apx_transaction where session_id = p_session_id;  
end;

procedure purge_old_transactions
is
begin
  delete from apx_transaction where creation_date < sysdate - 1;  
end;

end;
/

I did not include the DDL for the table APX_TRANSATION. I think you will be able to reverse engineer the statement.
Before you commit your transaction, so in Apex before the end of your PL/SQL block, you call

ASL_TRANSACTION.CAPTURE_TRANSACTION(:SESSION_ID).

The code that is executed by the former Rollback-button now has to be

ASL_TRANSACTION.REVERSE_TRANSACTION(:SESSION_ID).

In my opinion there also has to be some Confirm button that has to call

ASL_TRANSACTION.CONFIRM_TRANSACTION(:SESSION_ID).

That’s all. I included a full  test script at the end of this post.

For this package to work you’ll have to have give grant select any transaction to the package owner. If you don’t you can’t see the view sys.flashback_transaction_query.

The logic behind the package is this:

  • Before the commit, capture all the XID’s from v$transaction of all the DML that are executed in the current transaction
  • Save the XID’s with some identifying number, e.g. the session_id
  • By reversal, get the undo_sql from flashback_transaction_query and play back the transaction.
  • If original transaction deleted the row he first updated c.q. inserted, the reversal has to catch the new rowid of the insert to play back the latter playback c.q. delete.

Some words of caution:

Don’t, really don’t try to access flashback_transaction_query with anything else than a XID.

One of the features you get with an ordinary rollback is locking. The above solution does not involve a locking mechanism, yet. Ghost updates are therefore a possibility: when transaction A updates a table to X, session B updates to Y and A reverses, the update to Y is lost. In the client-server situation session B was blocked for update until A commits or rollbacks. It’s simple Oracle 1:1

However, when we look back at our options we also would have had the same problem with option 1.

Test, test and test if the given solution is appropriate in your case.

drop table emp_2
/

drop table dept_2
/

delete from apx_transaction
where session_id = -1
/

CREATE TABLE emp_2 
   (empno   NUMBER PRIMARY KEY, 
    empname VARCHAR2(16), 
    salary  NUMBER
    )
/
CREATE TABLE dept_2 
   (deptno   NUMBER, 
    deptname VARCHAR2(32)
   )
/

Prompt Insert in EMP_2 111,mike,555
INSERT INTO emp_2 VALUES (111, 'Mike', 555);
COMMIT;

prompt insert in dept_2 10,accounting
INSERT INTO dept_2 VALUES (10, 'Accounting');
COMMIT;

Prompt
Prompt Start an erroneous transaction
Prompt

UPDATE emp_2 SET salary = salary + 100 WHERE empno = 111;
INSERT INTO dept_2 VALUES (20, 'Finance'); 
DELETE FROM emp_2 WHERE empno = 111;

Prompt
Prompt Capture the transactiondata and commit
Prompt
execute asl_transaction.capture_transaction(-1);
COMMIT;

Prompt New data in the tables
Prompt EMP_2
select * from emp_2
/
Prompt DEPT_2
select * from dept_2
/
prompt
Prompt The undo-statements
begin
  for r_transactie in ( select xid from apx_transaction)
  loop
    for r_dml in ( select rtrim(qry.undo_sql,';') as undo_sql
                   ,      row_id
                   from   sys.flashback_transaction_query qry
                   where  qry.xid  = r_transactie.xid
                   and    qry.table_name != 'ASL_GTT_TRANSACTION'
                 )
    loop               
      dbms_output.put_line( r_transactie.xid||'...'||r_dml.undo_sql);
    end loop;
  end loop;
end;  
/
Prompt
Prompt Reverse the transaction
exec asl_transaction.reverse_transaction(-1)
Prompt
Prompt Check 
Prompt Has to be: 111,mike,555
select * from emp_2
/

prompt Has to be:  10,accounting
select * from dept_2
/