Tibero and Oracle compatibility issues. Part 1. Conditional PL / SQL Compilation

Conditional PL / SQL compilation allows you to selectively compile portions of the source code depending on the condition, usually associated with the value of the package constant. This is often used to ensure compatibility of applications with different versions of the DBMS.

In December 2017, this possibility appeared in Tibero, which made it possible to ensure even greater compatibility of applications between Tibero and Oracle.

As an example, take a packet to declare and associate exceptions with system error codes. In Tibero, system error codes are different from Oracle.

First of all, you need to use standard exceptions from the SYS.STANDARD package, such as NO_DATA_FOUND. There will be no compatibility issues with them. For the rest of the exceptions used in your application, it is recommended to create a separate package.

In this package, user exceptions with error codes from -20999 to -20000 are also fine - both in Tibero and in Oracle. In other cases, if you plan to use this application to work with both Oracle and Tibero, you can use conditional compilation.

First, we need a packet with a constant to determine which DBMS we work with:

create or replace package tmax_ConstPkg is c_isTibero constant boolean := false; end tmax_ConstPkg; 

Now you can write the binding of exceptions depending on the DBMS:

 create or replace package tmax_ErrPkg is e_object_not_exists exception; pragma exception_init(e_object_not_exists, $if tmax_Constpkg.c_isTibero $THEN -7071 $ELSE -4043 $END ); -- e_compilation_error exception; pragma exception_init(e_compilation_error, $IF tmax_Constpkg.c_isTibero $THEN -15163 $ELSE -06550 $END ); end tmax_ErrPkg; 

If the package for the declaration and binding of exceptions in your application for Oracle is already used, then you need to get (empirically) the corresponding error code in Tibero and add it using conditional compilation.

In the event that the declaration and binding of exceptions are scattered throughout the code of your application, this is not a reason for panic.

We decided to use a great opportunity to analyze PL / SQL code using PL / Scope. It is impractical to list all the utility code here, it is available on Oracle2Tibero_Tools . The utility consists of several PL / SQL packages, two of which have already been discussed above. The easiest way to use them is if you install these packages into the schema of your application along with the PL / SQL objects being analyzed.

Since PL / Scope is used to analyze the code, it is necessary to compile your PL / SQL objects in the identifier search mode, for example:

 SQL> ALTER SESSION SET plscope_settings='IDENTIFIERS:ALL'; SQL> alter procedure p1 compile; 

To recompile all PL / SQL objects in the identifier search mode, just run:

 SQL> exec tmax_check4migrate.recompile4plscope 

Now you can run the utility as follows:

 SQL> set serveroutput on SQL> exec tmax_check4migrate.run 

All problematic exceptions will be displayed, suggesting the use of either standard exceptions or declaration blanks and linking exceptions with Oracle error codes for the Tmax_ErrPkg package:

 1. Exception LONG_TEXT(PACKAGE BODY HR.TEST line 2528) init with error code -6502(line 2529) ...replace it to VALUE_ERROR Reference list 1) PACKAGE BODY HR.TEST line(s) 2570,2615 3. Exception E_MONTH_ERR(PACKAGE BODY HR.TEST line 6128) init with error code -1843(line 6129) Reference list 1) PACKAGE BODY HR.TEST line(s) 6198 ... ...You need to use conditional compilation to define different system error codes for Tibero and Oracle Following error codes did not find in Tmax_ErrPkg package, need to add: /* ORA-01843: not a valid month */ ------------------------------ e_not_a_valid_month exception; pragma exception_init(e_not_a_valid_month, $if tmax_Constpkg.c_isTibero $THEN -????? $ELSE -1843 $END ); 

By the way, it will be useful at least to consolidate all the exceptions into one package. I'll warn you right away that, in addition to checking for exceptions, the utility solves a number of other compatibility issues, which we will discuss in the following sections. Therefore, at this stage, in the main Run procedure, you can comment out calls to other checks — procedures with the prefix Chk_.

After you add all the exception blanks to the Tmax_ErrPkg package, you can run the utility in fix mode:

 SQL> set serveroutput on SQL> exec tmax_check4migrate.run(p_modify => true) 

Old declarations and bindings will be commented out and instead, the code will use either standard exceptions or exceptions from the Tmax_ErrPkg package.

Now, as for Tibero. At the time of this writing, the conditional compilation is not yet included in the standard assembly available on technet.tmaxsoft.com . You can check the conditional compilation support on your Tibero instance or by trying to set the required initialization parameters:

 _PSM_PREPROCESS=Y _PSM_PREPROCESS_SELECTION=Y 

Or you can check the SQL request that such hidden parameters are:

 SQL> select NAME,VALUE from sys._vt_parameter where name like '_PSM_PREPROCESS%'; 

You can request a version of Tibero that supports conditional compilation in the Russian office of TmaxSoft.

In the following publications, we will look at some of the differences between the Tibero and Oracle syntax that you may encounter, as well as how to avoid the associated fixes manually.