AntDB-Oracle Compatibility Developer's Manual P1-1

Update Notes

This section lists the new Oracle-compatible features that have been added to the standalone version of AntDB.

Writing Conventions in this Guide

Specific typographical conventions are used throughout this manual to explain the meaning and usage of different commands, statements, procedures, examples, etc. This section provides a summary of typographic conventions.

In the following description, a term is a word or group of words that can be programming language keywords, user-supplied values, text, and so on. The exact meaning of a term is determined by the context in which it is used.

• For terms that occur frequently, such as SQL commands used in examples, table and column names, programming language keywords, etc., use a gray background font.

• The pipe symbol｜ indicates the choice of terms on either side of the pipe. This symbol is used to separate 2 or more different terms within square brackets (which are optional) or parentheses (which are mandatory).

• The square brackets [] indicate that a term in the brackets can be replaced. For example, the expression [a\|b] means that you can choose either "a" or "b" or neither of them.

• The curly brackets ｛｝ indicate that one of the terms in the brackets must be used. For example, the expression {a\|b} means that one of "a" or "b" must be used.

• The ellipsis... is used to indicate that the term being processed can be repeated. For example, we can use the expression [ a | b] to get the sequence "b a a b a".

Set Oracle compatible configuration parameter

The default database syntax is postgres, AntDB supports compatibility switch settings at server level, session level, and statement level.

**Server level

**

Login to adbmgr and set all coordinator's grammar parameters.

postgres=# set coordinator all (grammar=oracle);
SET PARAM
postgres=# show param cd1 grammar;
          type          | status |             message              
------------------------+--------+------------------------------------
 coordinator master cd1 | t      | debug_print_grammar = off       +
                        |        | grammar = oracle
(1 row)

Connect to the Coordinator node, log in to the database, view the syntax parameters, and execute the Oracle syntax statement.

show grammar ;

 grammar 
----------------
 oracle
(1 row)

select * from dual;

 DUMMY 
--------------
 X
(1 row)

Session Level

If no server level setting is made, the default syntax after logging into the database is postgres.

show grammar ;
 grammar  
-----------------
 postgres
(1 row)

At this time, the statement executing Oracle syntax will report an error.

select * from dual;
ERROR:  relation "dual" does not exist
LINE 1: select * from dual;   

The session level is switched to Oracle syntax, and the Oracle syntax statement is executed again.

postgres=# set grammar to oracle;
SET
postgres=# show grammar ;
 grammar 
---------
 oracle
(1 row)
postgres=# select * from dual;
 DUMMY 
-------
 X
(1 row) 

Execution is successful.

Statement level

If you just want to use Oracle grammar for a particular statement, you can specify the grammar by hint, and add the "/ora/" mark at the beginning of the executed SQL statement.

postgres=# show grammar ;
 grammar  
----------
 postgres
(1 row)
postgres=# /*ora*/select * from dual;
 DUMMY 
-------
 X
(1 row)

The grammar parameter

This parameter determines the type of compatibility when using the database. The parameter type is a database server-side session-level variable parameter, and the values that can be set include:

postgres: uses a type compatible with PostgreSQL. It is the default value for this parameter.

oracle: the type compatible with Oracle is used.

About the examples in this guide

The examples in this guide are demonstrated using the PSQL program. To make the main points of the examples clearer, some of the prompts that appear when running PSQL are omitted.

Note the following points:

In order to produce the results of running the examples in this guide, a choice must be made between an Oracle-compatible configuration and the default configuration (postgres syntax), as described in the previous subsection. We can use the following command in PSQL to detect the default Oracle-compatible configuration and then see if this output is shown below.

postgres=# show grammar ;
 grammar 
---------
 oracle
(1 row)