Can I use Tibero instead of Oracle? And whether

In this article I will tell you about how seriously I thought about the alternative to Oracle. What about Postgre, you say? Yes, but there are nuances. At first we will deal with the question "Why the Oracle?".
Business logic in our database. In the book of Oracle for Professionals, Tom Kite writes

When developing database applications, I use a very simple mantra:

if possible, do it with a single SQL statement;
if this cannot be done with a single SQL statement, do it in PL / SQL;
if this cannot be done in PL / SQL, try using a stored procedure in Java;
if this cannot be done in Java, do it in the form of an external C procedure;
if this cannot be implemented as an external procedure in the C language, we must seriously think about why we should do it at all ...

and in system design, I follow this rule. Object types in Oracle are especially gratifying; with their help, complex business logic is beautifully and conveniently implemented according to all OOP canons.

Oracle is expensive. Buy it and not use everything that is in it will be a mistake.
And yet, there is always a factor of team and competence. If you have a team of ten years developing everything in Oracle, releasing yourself to Postgre can be painful.

Oracle is expensive. It is so expensive that you can write about it several times, and not thinking about the need for Oracle in the new project will be a mistake.

Already several times I came across publications about the Korean product Tibero, supposedly created to replace Oracle. And now they have an attraction of unprecedented generosity - Standard licenses are distributed to developers practically free of charge, for a dollar per socket. So, we understand: what Koreans can offer at the moment. With cars because they already (almost) turned out!

Experiment Description

Representatives of TMaxSoft say that Tibero is almost 100% compatible with Oracle, and there is a utility for migrating the database. I decided to take my product base, with business logic in Oracle, using all the OOP charm in PL \ SQL, and transfer it to Tibero. In this publication, we do not consider the migration of the data itself, it is less interesting, and I have not yet tried a full transfer.

The task is as follows:

1. Transfer tables.
2. Transfer indexes, keys, etc.
3. Transfer packages and triggers.
4. Transfer of object types.

But first, let's deal with the tools.

Instruments

Google knows little about Tibero. We downloaded the base itself as a virtual machine, on which everything is already deployed. There are only two tools: a utility for migrating T-UP, and an IDE for DBA and a developer tbAdmin. Everything is done in Java, runs anywhere, in theory.

T-UP looks like this:


Main window: connections to databases.


If you click on Options, you can customize something.


Most importantly, you can select the types of objects to migrate.

No instructions, help, tips could be found.
It seems like a self-made utility. Sometimes she flew out with eksepshenami, but mostly worked as it should.

The second tool is IDE Tibero Admin. It can be downloaded from the TMaxSoft website, if you pre-register there. You can also get a demo license.


Tibero Admin looks like a typical old IDE

I’m used to the great PL / SQL Developer tool from Allround Automations. In Tibero Admin, forget about contextual prompts, nothing will appear on your screen after clicking "dots", it will not add you the names of tables and objects. Just type the code, you are programmers. Help, documentation? Not. There is documentation on the DBMS on the manufacturer’s website, an interesting one ... without a search. No documentation found for IDE. However, there is nothing complicated. There were problems with authorization - it turned out that the user needs to be given DBA rights in order to just log into tbAdmin. And it is interesting with ports , 8630 is for SYS, for all other 8629 .

IDE podglyuchivaet. From time to time, when you poke somewhere, a message index out of bounds takes off, a message like java.lang.Exception: commitment Succeeded is very scary. It is necessary to take into account the different types of SQL and PSM windows: In the first one you can hardly compile the program code, in the second you will not execute the query. After PL / SQL Developer - a pitiful semblance of the left hand ...

We start the experiment.

Table transfer

We select the scheme in T-UP, click Migrate, first select “tables” in the options and the transfer process begins. I ran into two problems.

TABLESPACES. I decided to move them to the tables, but Tibero seemed to try to reserve as much space for them as they occupy in the source database. And this is a lot, and he could not. I created the table spaces manually, and then everything went fine.

In addition to tables with DEFAULT date values ​​of the type '31 .12.2019 '. We have registered this format in Oracle settings, it helps for Tibero

alter session set nls_date_format='DD.MM.YYYY'; 

, but for T-UP there is no place to do this. Colleagues from TMaxSoft advised to set the variable TB_NLS_DATE_FORMAT = "DD.MM.YYYY", but I personally did not help. Maybe I did something wrong. We had to create tables with such parameters manually, there are not so many of them.

The result of the first step: transferring the table structure from Oracle to Tibero works well.

Keys and indexes.

We select ticks INDEX, CONSTRAINT in T-UP, and forward. Problems arose with CHECK because of the same situation with date. In general, indexes, primary keys, checks - were created. But I could not find foreign keys in the newly created database. And the migration of quaverins ends in the T-UP log with the message "Migration Failed: java.lang.NullPointerException". Coincidence? I do not think ...

Packages and triggers.

In triggers I have nothing complicated, they were created perfectly. How to work, however, did not check, it will be a separate story.

Let's talk about the procedures and functions that implement part of the logic. Here, unfortunately, not everything went perfectly.

From the simple: in the army Tibero there is no word NEW . The o: = NEW t_my_type () construct does not compile. In Oracle, it is, however, not mandatory, but I always wrote for some reason. I had to delete.

The DBA role allows access to all tables in the SQL Window. However, when compiling a package or procedure in a schema with such a role, when using tables and objects of another scheme, you need to give an appropriate grant to the object. The magic of the DBA role does not help here.

From the specific. I got a strange FORALL in my database, which, however, works.

 --     PROCEDURE save_tar_test AS TYPE number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE date_table IS TABLE OF DATE INDEX BY BINARY_INTEGER; TYPE varchar50_table IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER; TYPE char1_table IS TABLE OF CHAR(1) INDEX BY BINARY_INTEGER; TYPE t_tar_det_out_upd_rec_test IS RECORD ( --   cmr_tar_det_id number_table, in_stamp date_table, err_code_id number_table ); tdou_rec t_tar_det_out_upd_rec_test; BEGIN --   ,  tdou_rec  :      Oracle FORALL j IN 1 .. tdou_rec.cmr_tar_det_id.COUNT UPDATE cmr_tar_det dd SET err_code_id = tdou_rec.err_code_id(j) WHERE dd.cmr_tar_det_id = tdou_rec.cmr_tar_det_id(j) AND in_stamp = tdou_rec.in_stamp(j); END; 

Tibero says "dml statement must have bulk-in parameter ia forall close" . And I understand it in my heart, but I had to redo this code into a normal loop.

The situation with tables containing objects is worse.

 CREATE OR REPLACE TYPE S1.TYPE_PAY_HIST AS OBJECT ( pay_status_id NUMBER(1), stamp DATE ); CREATE OR REPLACE TYPE S1.TABLE_PAY_HIST AS VARRAY(10) OF type_pay_hist; create table S2.RECEIPT ( receipt_id NUMBER(8) not null, pay_sum NUMBER(12,2) not null, receipt_hist S1.TABLE_PAY_HIST ); FUNCTION receipt_status_change(p_cmr_receipt_id NUMBER, p_new_status NUMBER) RETURN NUMBER AS l_receipt_hist s1.table_pay_hist; l_type_pay_hist s1.type_pay_hist; l_status NUMBER; BEGIN BEGIN SELECT receipt_hist INTO l_receipt_hist FROM receipt p WHERE p.receipt_id = p_cmr_receipt_id FOR UPDATE; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN c_err_not_find_status; END; ... END; 

We have an error when compiling, type mistmatch.

The guys from TMaxSoft offered this version of the code:

 create or replace FUNCTION .... AS l_receipt_hist table_pay_hist; l_type_pay_hist type_pay_hist; l_status NUMBER; cmr_receipt_row cmr_receipt%rowtype; BEGIN BEGIN SELECT * INTO cmr_receipt_row FROM cmr_receipt p WHERE p.cmr_receipt_id = p_cmr_receipt_id FOR UPDATE; SELECT TYPE_PAY_HIST(r.pay_status_id,r.stamp) bulk collect INTO l_receipt_hist FROM cmr_receipt p,table(p.receipt_hist) r WHERE p.cmr_receipt_id = p_cmr_receipt_id; EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('NO_DATA_FOUND'); RETURN null;--c_err_not_find_status; END; ... END; 

Work it probably works. But not so convenient, and it is necessary to alter the code.

The rest is compiled normally, except for specific things like SDO_GEOM. And, by the way, there is an analogue in Tibero. Before studying his hands have not reached.

Types

In one of the projects, we use the power of OOP by Oracle on a hollow coil.
The most exciting question to Tibero was about that.

Create a type that is the base for a set of other types.

 CREATE OR REPLACE TYPE t_tar_object AS OBJECT ( id NUMBER(12), smth NUMBER(12), CONSTRUCTOR FUNCTION t_tar_object RETURN SELF AS RESULT, MEMBER FUNCTION target(param IN NUMBER DEFAULT NULL) RETURN NUMBER, MEMBER FUNCTION inside(o t_tar_object) RETURN NUMBER, MEMBER FUNCTION clone RETURN t_tar_object ) NOT FINAL; --  CREATE OR REPLACE TYPE BODY t_tar_object AS CONSTRUCTOR FUNCTION t_tar_object RETURN SELF AS RESULT AS BEGIN RETURN; END; MEMBER FUNCTION target(param IN NUMBER DEFAULT NULL) RETURN NUMBER AS BEGIN RETURN id; END; MEMBER FUNCTION clone RETURN t_tar_object AS BEGIN RETURN NULL ; END; END; 

And now we are trying to create him a worthy heir.

 CREATE OR REPLACE TYPE t_tar_service UNDER t_tar_object ( is_virtual NUMBER(1), CONSTRUCTOR FUNCTION t_tar_service (p_serv_obj t_tar_object, p_main_id NUMBER, p_pack_id NUMBER) RETURN SELF AS RESULT, OVERRIDING MEMBER FUNCTION target(param IN NUMBER DEFAULT NULL) RETURN NUMBER, OVERRIDING MEMBER FUNCTION clone RETURN t_tar_object ) NOT FINAL; 

The compiler will refuse to override the target function. At the same time, there are no questions to the clone function. It turned out that Tibero does not like OVERRIDING when the method has parameters. Okay, remove the word OVERRIDING. But it is alarming, and we write test scripts. While with parent type.

 declare o1 t_tar_object; i1 number := 100; begin o1 := t_tar_object; o1.id := 1; i1 := o1.target; -- dbms_output.put_line (i1)   . --      ,  ,     i1   -- if i1 > 0 then dbms_output.put_line ('big'); end if; if i1 < 0 then dbms_output.put_line ('small'); end if; if i1 = 0 then dbms_output.put_line ('zero'); end if; if i1 is null then dbms_output.put_line ('null'); end if; end; 

It seems that we have left no choice to the program, no matter what value the variable i1 takes, something should appear in output. But ... nothing appears!


Immediately remembered a great movie

On this weirdness does not end. We aggravate the experiment

 declare o1 t_tar_object; i1 number := 100; begin o1 := t_tar_object; o1.id := 1; i1 := o1.target; --  ,      -    i1 := 2; if i1 > 0 then dbms_output.put_line ('big'); end if; if i1 < 0 then dbms_output.put_line ('small'); end if; if i1 = 0 then dbms_output.put_line ('zero'); end if; if i1 is null then dbms_output.put_line ('null'); end if; end; 

Even the fact that after all the manipulations with the methods of the object, we rigidly set the value of the variable, does not change anything - output is empty.

Control experiment, test yourself for insanity:

 declare o1 t_tar_object; i1 number ; begin o1 := t_tar_object; o1.id := 1; --i1 := o1.target;    i1 := 2; if i1 > 0 then dbms_output.put_line ('big'); end if; if i1 < 0 then dbms_output.put_line ('small'); end if; if i1 = 0 then dbms_output.put_line ('zero'); end if; if i1 is null then dbms_output.put_line ('null'); end if; end; 

The treasured “big” appears in the output. Scary, is not it? By the method of scientific tyke, the guys from TMaxSoft found out that if the nuance “NOT FINAL” is removed from the t_tar_object specification, the object will behave adequately. But why does he need such a one then ... without heirs.

Further about OOP in Tibero it makes no sense to tell. As, actually, OOP in Tibero. After that, another question arises: does the code of the procedures and functions compiled - does it work correctly? I do not know yet. A huge amount of code has been migrated and compiled. For a project that does not contain the types of exercises described above, this is an unambiguous success. But testing the correctness of code execution is a serious task. And frankly, I did not expect to encounter her. Whether I will have a project on this DBMS is not ready yet. But if it does, development will take place on Oracle, using normal handy tools, and with regular migration to Tibero. Writing code in the IDE without contextual prompts and with periodic errors of the interface itself is a pleasure below average.

findings

Is there a future for Tibero in its current state? Not sure. After all, if you look for the cost of licenses without megascapes, then one Standard socket costs about 800,000. Which is cheaper than Oracle, but not at times. And as I was convinced from my own experience, so far it is not even an Oracle.

Does it make sense to use the almost free Tibero that is being offered now? Maybe yes. They say that when paying the cost of technical support (99,000 rubles per socket), it is allowed to use this base in commercial projects. If you have a team of Oraklists available, and you need to create and place something that is not too tricky on your server, but cheaper and quicker is an interesting option. You can still play the sanctions card, telling restless customers that Korea is not the United States.

Do existing projects translate from Oracle to Tibero? Never. There is no reason to look for such adventures. It's easier to refuse Oracle technical support and not pay anything to anyone.

And if you need to create a new instance of an old project running on Oracle? And as a result, buy new licenses? And here think. It is possible that your base will migrate qualitatively and will work. But immediately think about supporting two directions in the project, or transfer everything to Tibero. We consider labor costs, risks, benefits from cheaper licenses. Compare, decide.

Perhaps in the next version of Tibero everything will be different. We just need to finish the types, make a normal IDE, maybe change the pricing policy. And if with the DBMS is like with cars, then after 5 years, Koreans can take a significant market share and press down the hegemon. We will see.

PS

A major advantage when choosing a DBMS for a new project can be the work of TMaxSoft technical support. I didn’t even buy a license for one dollar, and the guys answered me very promptly, obviously interested. Helped with both stupid questions and those described here. Feedback is excellent.