Tibero part 2. Relocation of the project with Oracle

The first part of my adventures with Tibero is here

To test Tibero compatibility with Oracle, I decided to check whether my favorite PL / SQL libraries will work on it , namely:
https://github.com/mortenbra/alexandria-plsql-utils/
Testing has begun with JSON_UTIL_PKG . A very useful feature that allows you to quickly generate a JSON response.

and so do

wget https://raw.githubusercontent.com/mortenbra/alexandria-plsql-utils/2213e3df2f24a00322239d442c54bf31039f2588/setup/types.sql
wget https://raw.githubusercontent.com/mortenbra/alexandria-plsql-utils/master/ora/json_util_pkg.pks
wget https://raw.githubusercontent.com/mortenbra/alexandria-plsql-utils/master/ora/json_util_pkg.pkb

To simplify life, add to the beginning of the pkb file

set define off

types.sql installed successfully.
When installing the package, we unexpectedly run into problems. If in oracle the installation of these three files went smoothly with the receipt of a compiled package, then in the case of tibero, when filling in the package body, I received the following output:

tbsql sys/syspass @json_util_pkg.pkb
tbSQL 5 SP1
TmaxData Corporation Copyright (c) 2008-. All rights reserved.
Connected to Tibero.
Warning: PACKAGE BODY created with compilation errors.
File finished.

By calling

show errors 

I get an error

TBR-15046: Identifier is out of scope.
at line 251, column 5:
	dbms_xmlgen

It turns out that the dbms_xmlgen package is not in tibero . Faced with this problem, I decided to see what DBMS% packages actually are. Inquiry

select distinct name from all_source where name like 'DBMS%' order by 1

Got the following list:

DBMS_APM	DBMS_APM_INTERNAL	DBMS_APPLICATION_INFO	DBMS_CRYPTO
DBMS_DEBUG	DBMS_DEBUG_JDWP	DBMS_ERRLOG	DBMS_EXPRESSION
DBMS_FLASHBACK	DBMS_GEOM	DBMS_JAVA	DBMS_JOB
DBMS_JOB_WITH_NAME	DBMS_JOB_WITH_NAME_INTERNAL	DBMS_LOB	DBMS_LOCK
DBMS_METADATA	DBMS_MVIEW	DBMS_MVIEW_REFRESH_UTIL	DBMS_MVIEW_UTIL
DBMS_OBFUSCATION_TOOLKIT	DBMS_OUTPUT	DBMS_PIPE	DBMS_RANDOM
DBMS_REDEFINITION	DBMS_REDEFINITION_STATS	DBMS_REPAIR	DBMS_RESULT_CACHE
DBMS_ROWID	DBMS_SESSION	DBMS_SPACE	DBMS_SPACE_ADMIN
DBMS_SPH	DBMS_SPH_INTERNAL	DBMS_SQL	DBMS_STATS
DBMS_STATS_INTERNAL	DBMS_STATS_UTIL	DBMS_SYSTEM	DBMS_TRANSACTION
DBMS_TYPES	DBMS_UTILITY	DBMS_VERIFY	DBMS_XMLGEN
DBMS_XPLAN

Total 45 vs 195 in my version of oracle . It looks sad. Of the packages that I had to use in Oracle , the lack of DBMS_SCHEDULER , DBMS_XMLDOM , etc. is frustrating .
I think that many in the list of 150 diberms that didn’t get into tibero will find packages that they miss .
But what is most interesting: the dbms_xmlgen package , due to which there was an error, is present in Tibero . The problem turned out to be that the package does not have the setmaxrows function .
By making

desc dbms_xmlgen

Got this package content in Tibero

FUNCTION CONVERT (XMLDATA IN VARCHAR, FLAG IN NUMBER) RETURNS VARCHAR

FUNCTION CONVERT (XMLDATA IN CLOB, FLAG IN NUMBER) RETURNS CLOB

FUNCTION NEWCONTEXT (QUERY IN VARCHAR) RETURNS NUMBER

FUNCTION NEWCONTEXT (QUERY IN REF CURSOR) RETURNS NUMBER

PROCEDURE SETNULLHANDLING (CTX IN NUMBER, FLAG IN NUMBER)

PROCEDURE CLOSECONTEXT (CTX IN NUMBER)

FUNCTION GETXML ​​(CTX IN NUMBER, DTDORSCHEMA IN NUMBER) RETURNS CLOB

FUNCTION GETXML ​​(SQLQUERY IN VARCHAR, DTDORSCHEMA IN NUMBER) IN RETURNS CLOB

PROCEDURE GETXML ​​(CTX IN NUMBER, TMPCLOB IN OUT CLOB, DTDORSCHEMA IN NUMBER)

As can be seen from the above, Tibero has less not only packages, but also procedures inside them. It turns out that most likely the migration of the finished Oracle product to Tibero will be quite difficult, since there is a good chance that packages using standard Oracle packages will not compile and you will have to rewrite them or implement standard Oracle packages yourself .

Having received such a result, I decided to study two more packages in more detail: DBMS_LOB and DBMS_LOCK . They were chosen because they seem to me the most popular among PL / SQL developers.

DBMS_LOB.
The comparison showed that all the basic functions are present, but you can’t work with fragments in Tibero , neither with DBFS_LINKs , nor are there some convenient methods. Speaking dryly, the DBMS_LOB package in tibero does not contain the following methods:

COPY_DBFS_LINK	COPY_FROM_DBFS_LINK	DBFS_LINK_GENERATE_PATH	FRAGMENT_DELETE
FRAGMENT_INSERT	FRAGMENT_MOVE	FRAGMENT_REPLACE	Getchunksize
Getconntentpe	Getoptions	GET_DBFS_LINK	GET_DBFS_LINK_STATE
GET_DEDUPLICATE_REGIONS	GET_STORAGE_LIMIT	ISSECUREFILE	LOADBLOBFROMFILE
LOADCLOBFROMFILE	LOADFROMFILE	MOVE_TO_DBFS_LINK	SETCONTENTTYPE
SETOPTIONS	SET_DBFS_LINK

DBMS_LOCK is fully implemented.

With UTL_% packets, the situation is close to DBMS_% . In total, 11 packages are presented in Tibero . Of which 8 ( UTL_ENCODE , UTL_FILE , UTL_HTTP , UTL_I18N , UTL_MATCH , UTL_RAW , UTL_TCP , UTL_URL ) are similar to standard Oracle packages , the remaining three are their own. In Oracle , in turn, package 21, thereby Tibero not implemented 13 following packages:

UTL_CALL_STACK	UTL_COLL	UTL_COMPRESS	UTL_GDK
UTL_IDENT	UTL_INADDR	UTL_LMS	UTL_NLA
UTL_RECOMP	UTL_REF	UTL_SMTP	UTL_SYS_COMPRESS
UTL_XML

I wonder why Tibero does not like XML so much ... The

state of the libraries is not very optimistic. But not always, we use DBMS and UTL packages! Sometimes there are enough standard functions, and with them everything is more or less optimistic. TMax is actively expanding the list of supported functions and now steadily supports the following list of methods:
Mathematics

ABS	Acos	Asin	ATAN	ATAN2
Avg	Bitand	Ceil	Cos	Cosh
COUNT	EXP	FLOOR	Ln	LOG (M, N)
MOD	Power	ROUND	Sign	Sin
Sinh	SQRT	Tan	Tanh	TRUNC

Lines

Concat	CHR	Lower	UPPER	LPAD
LTRIM	Rtrim	RPAD	SUBSTR	REPLACE
INITCAP	NLS_INITCAP	NLS_SORT	TRANSLATE	TO_CHAR
Ascii	INSTR	Length	LENGTHB

Dates

+ integer	-date	ADD_MONTHS	CURRENT_DATE
CURRENT_TIME	CURRENT_TIMESTAMP	LAST_DAY	NEW_TIME
NEXT_DAY	ROUND	TRUNC	EXTRACT
SYSTIMESTAMP	MONTHS_BETWEEN	TO_CHAR	TO_NUMBER
TO_DATE	TO_DSINTERVAL	SYSDATE

Office

GREATEST	LEAST	Cast	CONVERT
HEXTOCHAR	ROWIDTOCHAR	TO_CLOB	TO_LOB
Decode	Dump	VSIZE	COALEASE
Lnnvl	Nullif	NVL	NVL2

If we make a request in all_procedures , we will see more functions, but they are not mentioned in the official documentation.

After the results obtained above, I decided that downloading complex packages does not make sense: most likely the problems will be similar and will rest on the lack of certain packages. But there are simple systems that can easily do without DBMS packages and complex functions. I decided to try to evaluate what problems you might encounter when transferring such systems. For this, I took the Example HR scheme.
It was taken from here:
http://www.oracle.com/technetwork/developer-tools/datamodeler/hr-30-ddl-246035.zip
The execution of this script revealed incomplete support for ddl from Oracle .

Problem number 1

ALTER TABLE JOB_HISTORY 
    ADD CONSTRAINT JHIST_DATE_CHECK 
    CHECK (end_date > start_date)
        INITIALLY IMMEDIATE 
        ENABLE 
        VALIDATE 
;

Gives an error message:

TBR-7001: General syntax error.   
at line 4, column 9:
        INITIALLY IMMEDIATE 

In the documentation for details, I could not find how to set this. But judging by the tests, it works according to the DEFERRABLE value . But there are nuances with it (see problem 2).

Problem No. 2

ALTER TABLE COUNTRIES 
    ADD CONSTRAINT COUNTR_REG_FK FOREIGN KEY 
    ( 
     REGION_ID
    ) 
    REFERENCES REGIONS 
    ( 
     REGION_ID
    ) 
    NOT DEFERRABLE 
;

Gives an error message:

TBR-7001: General syntax error.   
at line 10, column 5:
    NOT DEFERRABLE

Again, I did not find any documentation on creating constraint , but in practice, if we want deferred constraints, we write DEFERRABLE , if we want to always check this constraint, we do not specify anything (this is the default value).

The scheme is created, now you can run scripts to test the capabilities of the DBMS. I took scripts from here:
http://www.srikanthtechnologies.com/oracle/dec9/hrqueries.html

Problem No. 3
Request:

SELECT SYSDATE - to_date('01-jan-2011') FROM DUAL

Gives an error:

TBR-5113: Invalid month value 'jan-2011'. (01-jan-2011)   

The correct request for Tibero was:

SELECT SYSDATE - to_date('20110101') from dual   

This trifle seems to me a problem because the company claims that one of the main directions of their development is compatibility with Oracle . And compatibility is not only support for high-quality code, but also the correct operation of bad code. And when something by default works in Oracle , then with such a policy it is logical to expect this to work by default in Tibero .

I could not get other errors from these scripts. I checked pivot separately, various join notations, hierarchical queries: everything works, pipeline functions work strange and everything connected with them. Here's a simple example:

Problem # 4:
Creating Types

CREATE OR REPLACE TYPE TEST_TYPE IS OBJECT( TESTYPE NUMBER );
CREATE OR REPLACE TYPE TEST_TYPE_TABLE IS TABLE OF TEST_TYPE;

I create a function issuing table

CREATE OR REPLACE FUNCTION TEST_FUNCTION(PARAM IN NUMBER)
RETURN TEST_TYPE_TABLE
IS
	RSLT TEST_TYPE_TABLE;
BEGIN
	RSLT := TEST_TYPE_TABLE();
	RSLT.EXTEND;
	RSLT(RSLT.LAST) := TEST_TYPE(PARAM);
	RETURN RSLT;
END;

I make a request:

SELECT * FROM TABLE( test_function( 2 )  )

Got:

TBR-8051: User-defined functions are not permitted in the expression.
at line 1, column 23:
select * from table( test_function(2) )

I tried to fix the situation:

select * from table( cast(test_function(2) as test_type_table) )

The result is even worse:

TBR-8021: Invalid table name. 
at line 1, column 47:
select * from table( cast(test_function(2) as test_type_table) )

But this manufacturer is about to fix it, but so far we have what we have.

Problem 5
Connecting database managers. This is the whole problem and dancing with tambourines. Most do not want to communicate with the database, or the setting is not very obvious. The manufacturer promises about to be tbrliboci.dll , but so far it is not.

Problem No. 6
I tried to connect Oracle Forms 9 to Tibero . Got a checksum error while connecting. It was not possible to advance further.

Problem number 7
I found it rather painful to use Explain Plan without dbms_xplan.display .

According to the results of tests, loading ready-made packages and some ready-made PL / SQLsystems I can say that fairly simple and light systems are well tolerated. Everything works, no problems. You can write on it with 0 and, probably, quite successfully. Unless, of course, you know English and am ready to endure that something works on the same way as in Oracle . In the case of large systems like an online store, an abs bank, a warehouse accounting system, problems with packages, poor work with the pipeline , the migration process is painful and involves rewriting the finished code, which is not very pleasant and sometimes quite expensive.

In general, it seems to me that this product has a chance of survival. And now, as I said in the first part, there are Russian companies that work with him. Our guys work there, but there is no sensible information in Russian. Therefore, I really want the Russian-speaking community of people around this product to start to suggest, help or share experience with this Tibero sub .

PS Many thanks to Peter Zhitnikov for help in writing.