How OpenGauss Handles Oracle SQL Compatibility: Features, Limits, and Workarounds

1. Query Syntax Compatibility

openGauss supports several Oracle‑specific query constructs. The ROWNUM keyword, used to limit result rows, works natively. The virtual table DUAL behaves identically to Oracle. However, the hierarchical query clause CONNECT BY is not supported and must be rewritten using recursive CTEs.

postgres=# select sysdate from test where rownum < 2;
sysdate
---------------------
2020-10-26 22:31:09
(1 row)

2. HINT Support

Hints are still available but the syntax differs from Oracle. In openGauss hints are expressed as comments, and some hint operators have different names (e.g., ixscan in Oracle becomes indexscan in openGauss). The official documentation lists the complete set of supported hint operators.

postgres=# CREATE INDEX my_index ON product USING btree (name);
CREATE INDEX
postgres=# explain select name from product;
Seq Scan on product  (cost=0.00..24.08 rows=1408 width=24)
postgres=# explain select /*+ indexonlyscan(product my_index) */ name from product;
Index Only Scan using my_index on product  (cost=0.00..65.37 rows=1408 width=24)

3. Execution‑Plan Commands

openGauss does not provide the autotrace utility. Execution plans can be obtained directly with EXPLAIN. The EXPLAIN PLAN command stores the plan in the PLAN_TABLE system table, similar to Oracle, but the format differs slightly.

postgres=# explain plan for select * from test;
EXPLAIN SUCCESS
postgres=# SELECT * FROM PLAN_TABLE;
statement_id | plan_id | id | operation | options | object_name | object_type | object_owner | projection
--------------+--------+----+-----------+---------+-------------+-------------+--------------+------------
| 281474976710867 | 1 | TABLE ACCESS | SEQ SCAN | test | TABLE | public | id
(1 row)

4. Functions

DECODE

The Oracle‑only DECODE function is fully supported in openGauss.

select DECODE(3, 1,'One', 2,'Two', 3,'Three', 'Not found');
---
Three
(1 row)

SYSDATE & SYSTIMESTAMP

SYSDATE

works as expected. SYSTIMESTAMP is not available; the equivalent is LOCALTIMESTAMP.

select sysdate;
2020-10-21 17:04:14
select systimestamp from dual;
ERROR: column "systimestamp" does not exist
select localtimestamp from dual;
2020-11-02 09:39:22.382455

NVL & NVL2

NVL

is fully compatible. NVL2 is missing; its behavior can be reproduced with DECODE.

select NVL(9,0) from dual;
9
select nvl2(100,1,2) from dual;
ERROR: function nvl2(integer, integer, integer) does not exist

UDF (User‑Defined Functions)

PL/SQL‑style functions can be created without modification.

CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar2, v_version varchar2)
RETURNS varchar2 IS
BEGIN
  IF v_version IS NULL THEN
    RETURN v_name;
  END IF;
  RETURN v_name || '/' || v_version;
END;
/

5. PL/SQL Stored Procedures

Procedures are also compatible and can be migrated directly.

CREATE OR REPLACE PROCEDURE cs_parse_url(
  v_url IN VARCHAR2,
  v_host OUT VARCHAR2,
  v_path OUT VARCHAR2,
  v_query OUT VARCHAR2)
IS
  a_pos1 INTEGER;
  a_pos2 INTEGER;
BEGIN
  v_host := NULL;
  v_path := NULL;
  v_query := NULL;
  a_pos1 := instr(v_url, '//');
  IF a_pos1 = 0 THEN RETURN; END IF;
  a_pos2 := instr(v_url, '/', a_pos1 + 2);
  IF a_pos2 = 0 THEN
    v_host := substr(v_url, a_pos1 + 2);
    v_path := '/';
    RETURN;
  END IF;
  v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
  a_pos1 := instr(v_url, '?', a_pos2 + 1);
  IF a_pos1 = 0 THEN
    v_path := substr(v_url, a_pos2);
    RETURN;
  END IF;
  v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
  v_query := substr(v_url, a_pos1 + 1);
END;
/

6. Triggers

Trigger syntax differs significantly; openGauss requires rewriting the definition.

create or replace trigger modify_stu
before insert on student
for each row
DECLARE
  next_id number;
BEGIN
  select seq_test.nextval into next_id from dual;
  :new.id := next_id;
END;
/

7. Cursors

Oracle’s CURSOR … IS form is not accepted. Use the FOR clause instead.

-- Oracle style (fails)
CURSOR prd_cursor IS select name from product;
-- openGauss style
CURSOR prd_cursor FOR select name from product;

8. VARRAYs (Arrays)

VARRAY definitions map directly to openGauss arrays.

type integer_varray is varray(3) of integer;
var_int integer_varray := integer_varray();
BEGIN
  FOR i IN 1..3 LOOP
    var_int.extend;
    var_int(i) := 10 + i;
  END LOOP;
END;
/

9. Compatibility Summary

ROWNUM – fully compatible

DUAL – fully compatible

CONNECT BY – not compatible (rewrite with CTE)

HINT – partially compatible (different keyword names)

Execution plan – partially compatible (no autotrace)

DECODE – fully compatible

SYSDATE – fully compatible; SYSTIMESTAMP – replace with LOCALTIMESTAMP

NVL – fully compatible; NVL2 – replace with DECODE

Custom functions – fully compatible

Stored procedures – fully compatible

Triggers – not compatible (must be rewritten)

Cursors – not compatible (use FOR instead of IS)

VARRAYs – fully compatible