I. Halo Database Has Multi-Mode Parsing Engine Functionality
The Halo database has a unique multi-mode parsing engine technology, capable of being compatible with mainstream database syntax such as PostgreSQL, Oracle, Sybase, MySQL, SQL Server, DB2, thereby significantly reducing code modifications in migration projects. Currently, it mainly focuses on Oracle syntax compatibility, reducing code modifications by at least 95% for Oracle database migrations, greatly lowering migration costs and significantly reducing migration risks.
Halo uses a configurable parsing engine, allowing flexible switching between various database engines.
II. How to Enable the Oracle Parsing Engine
2.1 Set the parameter database_compat_mode in the postgresql.conf file to 'oracle'
database_compat_mode = 'oracle'2.2 Restart the database
pg_ctl restart2.3 Create the Oracle extension for the database that needs to enable the Oracle parsing engine
create extension aux_oracle cascade;III. Other Parameters Related to Oracle Engine
3.1 transform_null_equals
Since any operation on NULL only returns NULL, determining whether a value is NULL generally can only be done with IS NULL.
Halo provides the parameter transform_null_equals to control whether the "=" operator can be used to determine if a value is NULL. You can set the transform_null_equals parameter to on (default is off).
3.1.1 There is a record with a null value in the database. When the transform_null_equals parameter state is off, using "=null" cannot query the null value record
halo0root=# select * from test where id=null;
id
----
(0 rows)3.1.2 There is a record with a null value in the database. When the transform_null_equals parameter state is on, using "=null" can query the null value record
halo0root=# select * from test where id=null;
id
----
(1 row)3.2 use_datetime_as_date
Oracle's date type carries date and time information, which differs from Halo's default setting. By default, Halo's date type only contains the date, while the datetime type contains date and time information. To achieve the same date type as Oracle, you can set the parameter use_datetime_as_date to true (default value is off).
use_datetime_as_date = true3.3 standard_parserengine_auxiliary
This parameter is used to enable the auxiliary parser module to enhance support for Oracle-specific syntax (such as packages, procedures, etc.). It is recommended to enable it together when enabling Oracle mode.
standard_parserengine_auxiliary = onIV. Halo Database Choosing Oracle Mode
The Halo database supports most functions and syntax, packages, views, etc. from Oracle. This means that during the transition from Oracle to Halo, you do not need to spend a lot of time learning new content, and you can smoothly achieve the migration from Oracle to Halo.
4.1 Compatible Oracle Functions
The Halo database supports compatible functions: character types, numeric types, date types, timestamp types, interval types, large object types, binary types, conversion functions, aggregate functions, time functions, etc.
Let's do some simple function examples:
The ADD_MONTHS function increases (or decreases if the second parameter is negative) a specified number of months based on a given date. Before performing the calculation, any fractional part of the month parameter is truncated. If the given date contains a time part, it will be carried over to the result.
halo0root=# SELECT ADD_MONTHS('13-JUN-07',4) FROM DUAL;
add_months
------------------------
2013-10-07 00:00:00+08
(1 row)The BITAND function performs a bitwise AND operation and returns a value based on the data type of the input parameters.
Syntax: BITAND(<expr1>, <expr2>)
Return Type: The BITAND function returns a value of the same data type as the input parameters.
halo0root=# SELECT BITAND(10,11) FROM DUAL;
bitand
--------
10
(1 row)The BTRIM function trims a string by removing leading and trailing spaces, or by removing characters that match an optional specified string.
Syntax: BTRIM(string [, matching_string ] )
halo0root=# select 'xyzaxyzbxyzcxyz' as untrim,btrim('xyzaxyzbxyzcxyz', 'xyz') as trim;
untrim | trim
-----------------+-----------
xyzaxyzbxyzcxyz | axyzbxyzc
(1 row)The CONCAT function concatenates multiple RAW values into a single RAW value.
This function returns a RAW value.
halo0root=# select concat('aba','df') from dual;
concat
--------
abadf
(1 row)4.2 Compatible Oracle Syntax
The Halo database supports compatible syntax: outer join operator (+), sequences, SYSDATE (view date), NULL and empty string, DECODE, ROWNUM, DBLINK, DUAL pseudo table, special IN syntax, etc.
Let's do some simple syntax examples:
Outer Join Operator (+): Includes left outer join (left table unrestricted), right outer join (right table unrestricted), and can support very complex join operations.
Create 10 tables ta; tb; tc; td; te; tf; tg; th; ti; tj:
halo0root=# CREATE TABLE ta (id NUMBER, name VARCHAR2(30), location VARCHAR2(30));
CREATE TABLE
halo0root=# INSERT INTO ta VALUES (1, 'A', 'CN');
INSERT 0 1
halo0root=# INSERT INTO ta VALUES (2, 'B', 'CN');
INSERT 0 1
halo0root=# INSERT INTO ta VALUES (3, 'C', 'US');
INSERT 0 1
halo0root=# INSERT INTO ta VALUES (4, 'D', 'JP');
INSERT 0 1
halo0root=# CREATE TABLE tb (id NUMBER, aid NUMBER, class VARCHAR2(30), location VARCHAR2(30));
CREATE TABLE
halo0root=# INSERT INTO tb VALUES (1, 1, 'S1', 'CN');
INSERT 0 1
halo0root=# INSERT INTO tb VALUES (2, 2, 'S1', 'CN');
INSERT 0 1
halo0root=# INSERT INTO tb VALUES (3, 3, 'S2', 'US');
INSERT 0 1
halo0root=# CREATE TABLE tc (id NUMBER, name VARCHAR2(30), bid NUMBER, location VARCHAR2(30));
CREATE TABLE
halo0root=# INSERT INTO tc VALUES (1, 'T1', 1, 'CN');
INSERT 0 1
halo0root=# INSERT INTO tc VALUES (2, 'T1', 2, 'CN');
INSERT 0 1
halo0root=# INSERT INTO tc VALUES (3, 'T2', 3, 'US');
INSERT 0 1
halo0root=# CREATE TABLE td (id NUMBER, grade NUMBER, cid NUMBER);
CREATE TABLE
halo0root=# INSERT INTO td VALUES (1, 1, 1);
INSERT 0 1
halo0root=# INSERT INTO td VALUES (2, 1, 2);
INSERT 0 1
halo0root=# INSERT INTO td VALUES (3, 2, 3);
INSERT 0 1
halo0root=# CREATE TABLE te (id NUMBER, grade2 NUMBER, did NUMBER);
CREATE TABLE
halo0root=# INSERT INTO te VALUES (1, 1, 1);
INSERT 0 1
halo0root=# INSERT INTO te VALUES (2, 1, 2);
INSERT 0 1
halo0root=# INSERT INTO te VALUES (3, 2, 3);
INSERT 0 1
halo0root=# CREATE TABLE tf (id NUMBER, grade3 NUMBER, eid NUMBER);
CREATE TABLE
halo0root=# INSERT INTO tf VALUES (1, 1, 1);
INSERT 0 1
halo0root=# INSERT INTO tf VALUES (2, 1, 2);
INSERT 0 1
halo0root=# INSERT INTO tf VALUES (3, 2, 3);
INSERT 0 1
halo0root=# CREATE TABLE tg (id NUMBER, grade4 NUMBER, fid NUMBER);
CREATE TABLE
halo0root=# INSERT INTO tg VALUES (1, 1, 1);
INSERT 0 1
halo0root=# INSERT INTO tg VALUES (2, 1, 2);
INSERT 0 1
halo0root=# INSERT INTO tg VALUES (3, 2, 3);
INSERT 0 1
halo0root=# CREATE TABLE th (id NUMBER, grade5 NUMBER, gid NUMBER);
CREATE TABLE
halo0root=# INSERT INTO th VALUES (1, 1, 1);
INSERT 0 1
halo0root=# INSERT INTO th VALUES (2, 1, 2);
INSERT 0 1
halo0root=# INSERT INTO th VALUES (3, 2, 3);
INSERT 0 1
halo0root=# CREATE TABLE ti (id NUMBER, grade6 NUMBER, hid NUMBER);
CREATE TABLE
halo0root=# INSERT INTO ti VALUES (1, 1, 1);
INSERT 0 1
halo0root=# INSERT INTO ti VALUES (2, 1, 2);
INSERT 0 1
halo0root=# INSERT INTO ti VALUES (3, 2, 3);
INSERT 0 1
halo0root=# CREATE TABLE tj (id NUMBER, grade7 NUMBER, iid NUMBER);
CREATE TABLE
halo0root=# INSERT INTO tj VALUES (1, 1, 1);
INSERT 0 1
halo0root=# INSERT INTO tj VALUES (2, 1, 2);
INSERT 0 1
halo0root=# INSERT INTO tj VALUES (3, 2, 3);
INSERT 0 1Left Join:
halo0root=# SELECT a.name, a.location, b.class FROM ta a, tb b WHERE a.id = b.aid(+);
name | location | class
------+----------+-------
A | CN | S1
B | CN | S1
C | US | S2
D | JP |
(4 rows)Right Join:
halo0root=# SELECT a.name, a.location, b.class FROM ta a, tb b WHERE a.id = b.aid( + );
name | location | class
------+----------+-------
A | CN | S1
B | CN | S1
C | US | S2
D | JP |
(4 rows)Complex Join:
halo0root=# SELECT a.name, b.class, c.name, d.grade, e.grade2, f.grade3, g.grade4, h.grade5, i.grade6, j.grade7
halo0root-# FROM ta a, tb b, tc c, td d, te e, tf f, tg g, th h, ti i, tj j
halo0root-# WHERE b.location(+) = 'CN'
halo0root-# AND f.id = g.id(+)
halo0root-# AND a.id = e.id(+)
halo0root-# AND c.id = e.id(+)
halo0root-# AND c.id = d.id(+)
halo0root-# AND a.id = b.id(+)
halo0root-# AND d.id = f.id(+)
halo0root-# AND a.id + e.id = b.id(+)
halo0root-# AND e.id = g.id(+)
halo0root-# AND g.id = h.id(+)
halo0root-# AND a.id = h.id(+)
halo0root-# AND i.hid(+) = 2
halo0root-# AND d.id = i.id(+)
halo0root-# AND h.id = j.id(+)
halo0root-# AND i.id = j.id(+);
name | class | name | grade | grade2 | grade3 | grade4 | grade5 | grade6 | grade7
------+-------+------+-------+--------+--------+--------+--------+--------+--------
A | | T1 | 1 | 1 | 1 | 1 | 1 | |
B | | T1 | 1 | | 1 | | | |
C | | T1 | 1 | | 1 | | | |
D | | T1 | 1 | | 1 | | | |
A | | T1 | 1 | | 1 | | | 1 |
B | | T1 | 1 | 1 | 1 | 1 | 1 | 1 | 1
C | | T1 | 1 | | 1 | | | 1 |
D | | T1 | 1 | | 1 | | | 1 |
A | | T2 | 2 | | 2 | | | |
B | | T2 | 2 | | 2 | | | |
C | | T2 | 2 | 2 | 2 | 2 | 2 | |
D | | T2 | 2 | | 2 | | | |
(12 rows)A sequence (SEQUENCE) is a sequence number generator that can automatically generate sequence numbers for rows in a table, producing a set of equally spaced values (numeric type). It does not occupy disk space, only memory. Its main purpose is to generate primary key values for tables. Additionally, it supports the ORDER keyword for creating sequences.
Create a sequence:
halo0root=# CREATE SEQUENCE a_seq;
CREATE SEQUENCEAlso supports creating sequences with the ORDER keyword
CREATE SEQUENCE a_seq2 ORDER;Initialize the sequence:
halo0root=# SELECT a_seq.nextval FROM dual;
nextval
---------
1
(1 row)Query the current sequence value:
halo0root=# SELECT a_seq.currval FROM dual;
currval
---------
1
(1 row)MINUS in Oracle is also used for subtraction operations, but it's not subtraction on numbers in the traditional sense; it's subtraction on query result sets.
halo0root=# select * from td;
id
----
1
2
3
(3 rows)
halo0root=# select * from te;
id
----
1
2
0
(3 rows)
halo0root=# select * from td MINUS SELECT * FROM te;
id
----
3
(1 row)The <winagg_function>(distinct..) over(partition by) syntax function is to obtain the result of the target after grouping and deduplication, supporting commonly used aggregate functions such as count, sum, listagg, etc.
Create the Per table
halo0root=# CREATE TABLE Per(Id int,Name varchar(255));
CREATE TABLE
halo0root=# INSERT INTO Per VALUES (1, 'ww');
INSERT 0 1
halo0root=# INSERT INTO Per VALUES (1, 'ee');
INSERT 0 1
halo0root=# INSERT INTO Per VALUES (1, 'ee');
INSERT 0 1
halo0root=# INSERT INTO Per VALUES (2, 'ee');
INSERT 0 1
halo0root=# INSERT INTO Per VALUES (2, 'dd');
INSERT 0 1
halo0root=# select * from Per;
id | name
----+------
1 | ww
1 | ee
2 | ee
2 | dd
1 | ee
(5 rows)The count(distinct.. ) over(partition by) syntax finds the result after grouping and deduplication:
halo0root=# select name, count(distinct name) over(partition by id) from Per;
name | count
------+-------
ww | 2
ee | 2
ee | 2
ee | 2
dd | 2
(5 rows)As a comparison, the result without DISTINCT operation
halo0root=# select name, count(name) over(partition by id) from Per;
name | count
------+-------
ww | 3
ee | 3
ee | 3
ee | 2
dd | 2
(5 rows)4.3 Compatible Oracle System Packages
The Halo database supports compatible system packages:
DBMS_ALERT, DBMS_ASSERT, DBMS_OUTPUT, DBMS_PIPE, DBMS_RANDOM, DBMS_UTILITY, UTL_FILE, etc.
Let's use the DBMS_ASSERT package for a simple example:
The DBMS_ASSERT package provides an interface to validate the properties of input values.
halo0root=# \df dbms_assert.*dbms_assert.enquote_literal: Used to add a leading quote and trailing single quote to a string literal.
halo0root=# select DBMS_ASSERT.ENQUOTE_LITERAL (ename) from emp;
enquote_literal
-----------------
'ALLEN'
'WARD'
'JONES'
'MARTIN'
'BLAKE'
(5 rows)dbms_assert.qualified_sql_name: Verifies if the input string is a qualified SQL name.
halo0root=# select dbms_assert.qualified_sql_name ('wy') from dual;
qualified_sql_name
--------------------
wy
(1 row)4.4 Compatible Oracle Views
The Halo database supports compatible views: DBA_SEGMENTS, PRODUCT_COMPONENT_VERSION, user_con_columns, USER_CONSTRAINTS, USER_IND_COLUMNS, USER_OBJECTS, USER_PROCEDURES, USER_SOURCE, USER_TAB_COLUMNS, USER_TABLES, etc.
Let's use DBA_SEGMENTS and PRODUCT_COMPONENT_VERSION as examples:
The DBA_SEGMENTS view describes storage and allocation information for all segments in the database.
halo0root=# \dS dba_segments
View "oracle.dba_segments"
Column | Type | Collation | Nullable | Default
-----------------+-----------------------+-----------+----------+--------
owner | name | | |
segment_name | name | | |
segment_type | character varying(18) | | |
tablespace_name | name | | |
header_file | oid | | |
header_block | oid | | |
bytes | bigint | | |
blocks | integer | | |PRODUCT_COMPONENT_VERSION contains version and status information of component products.
halo0root=# \dS product_component_version
View "oracle.product_component_version"
Column | Type | Collation | Nullable | Default
---------+------+-----------+----------+--------
product | text | C | |
version | text | C | |
status | text | | |Notes:
Product: Product name
Version: Product version number
Status: Product status includes compatibility and high availability
The above only shows a part of the Oracle compatible functions. If you want to learn more, please leave a comment and follow us! Thank you