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 it cannot be implemented as an external procedure in the C language, we need to seriously think about why it should be done 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 the 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, the message index out of bounds takes off., very scary message type java.lang.Exception: commitment Succeeded . 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 ...

Let's start the experiment.

Table transfer

Select the scheme in T-UP, click Migrate, first select the “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

altersessionset 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 the INDEX, CONSTRAINT checkboxes in T-UP, and go ahead. The problems arose with CHECK due to the same situation with the 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 schema, it is necessary 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 не заполнен: но такой код компилируется в OracleFORALL j IN1 .. 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.

CREATEORREPLACETYPE S1.TYPE_PAY_HIST ASOBJECT
(
  pay_status_id NUMBER(1),
  stamp         DATE
);
CREATEORREPLACETYPE S1.TABLE_PAY_HIST ASVARRAY(10) OF type_pay_hist;
createtable S2.RECEIPT
(
  receipt_id    NUMBER(8) notnull,
  pay_sum     NUMBER(12,2) notnull,
  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;
BEGINBEGINSELECT receipt_hist
    INTO l_receipt_hist
    FROM receipt p
    WHERE p.receipt_id = p_cmr_receipt_id
    FORUPDATE;
  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:

createorreplaceFUNCTION ....
AS
  l_receipt_hist  table_pay_hist;
  l_type_pay_hist type_pay_hist;              
  l_status        NUMBER;
  cmr_receipt_row cmr_receipt%rowtype;
BEGINBEGINSELECT   * INTO cmr_receipt_row
    FROM cmr_receipt p
    WHERE p.cmr_receipt_id = p_cmr_receipt_id
    FORUPDATE;
    SELECT 
    TYPE_PAY_HIST(r.pay_status_id,r.stamp)
    bulkcollectINTO 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.

CREATEORREPLACETYPE t_tar_object ASOBJECT
(
   idNUMBER(12),
   smth NUMBER(12),
  CONSTRUCTORFUNCTION t_tar_object RETURNSELFASRESULT,
  MEMBERFUNCTION target(param INNUMBERDEFAULTNULL) RETURNNUMBER,
  MEMBERFUNCTION inside(o t_tar_object) RETURNNUMBER,
 MEMBERFUNCTIONcloneRETURN t_tar_object 
) NOTFINAL;
-- ТелоCREATEORREPLACETYPEBODY t_tar_object ASCONSTRUCTORFUNCTION t_tar_object RETURNSELFASRESULTASBEGINRETURN;
END;
MEMBER FUNCTION target(param IN NUMBER DEFAULT NULL) RETURN NUMBER
AS
BEGINRETURNid;
END;
MEMBER FUNCTION clone  RETURN t_tar_object
AS
BEGINRETURNNULL ;
END;
END;

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

CREATEORREPLACETYPE t_tar_service UNDER t_tar_object
(
  is_virtual        NUMBER(1),
  CONSTRUCTORFUNCTION t_tar_service (p_serv_obj  t_tar_object, p_main_id NUMBER, p_pack_id NUMBER) RETURNSELFASRESULT,
  OVERRIDINGMEMBERFUNCTION target(param INNUMBERDEFAULTNULL)  RETURNNUMBER,
  OVERRIDINGMEMBERFUNCTIONcloneRETURN t_tar_object
) NOTFINAL;

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');
endif;
if i1  < 0 then
dbms_output.put_line ('small');
endif;
if i1  = 0 then
dbms_output.put_line ('zero');
endif;
if i1  is null then
dbms_output.put_line ('null');
endif;
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 I remembered a great movie

. This does not end the oddity. 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');
endif;
if i1  < 0 then
dbms_output.put_line ('small');
endif;
if i1  = 0 then
dbms_output.put_line ('zero');
endif;
if i1  is null then
dbms_output.put_line ('null');
endif;
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');
endif;
if i1  < 0 then
dbms_output.put_line ('small');
endif;
if i1  = 0 then
dbms_output.put_line ('zero');
endif;
if i1  is null then
dbms_output.put_line ('null');
endif;
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.