Issue

When calling Tibero's Sequence using a DB Link from Oracle, an ORA-02070 error occurs.

SQL> select test_seq.nextval@ott from dual;
select test_seq.nextval@ott from dual
       *
ERROR at line 1:
ORA-02070: database OTT does not support sequence references in this context

Cause

This error occurs because direct calls are not supported due to limitations of the Oracle Gateway.

Therefore, it is necessary to bypass and query through Functions and Views.

Solution

1. Create a Function to Query the Sequence

Create a Function in Tibero to query the Sequence.

-- #Create function to get the current sequence value
CREATE OR REPLACE
FUNCTION TIBERO.FN_GET_SEQ_CURRENT RETURN number
AS
v_current number;
BEGIN
select test_seq.currval into v_current from dual;
RETURN v_current;
END;

-- #Create function to get the next sequence value
CREATE or REPLACE
FUNCTION TIBERO.FN_GET_SEQ_NEXTVAL RETURN number
AS
v_nextval number;
BEGIN
select test_seq.nextval into v_nextval from dual;
RETURN v_nextval;
END;

2. Create a View that Queries the Function

Create a View in Tibero that calls the Function to query the Sequence value.

-- #Create view that calls the function to get the current sequence value
CREATE OR REPLACE FORCE VIEW tibero.SEQ_VIEW_CURRENT (SEQ_CURRENTVAL)
AS
select tibero.FN_GET_SEQ_CURRENT() as seq_currentval from dual;

-- #Create view that calls the function to get the next sequence value
CREATE OR REPLACE FORCE VIEW tibero.SEQ_VIEW_NEXTVAL (SEQ_NEXTVAL)
AS
select tibero.FN_GET_SEQ_NEXTVAL() as seq_nextval from dual;

Note

Because Sequences cannot be used as result columns in the Subquery that defines a View, do not create Views in Tibero that directly query Sequences.

Please refer to the Tibero_7_SQL-Reference-Guide_v7.2.3 manual, create view (page 604).

3. Query Tibero's View via DB Link

Query the View created in Tibero using the DB Link.

-- #DB Link query
SQL> select * from dba_db_links where db_link = 'OTT';
OWNER		                     DB_LINK		       USERNAME	               HOST		    CREATED	    HIDDEN SHARD_INT      VALID INTRA_CDB
-------------------- -------------------- -------------------- -------------------- ------------------ --------- --------- --------- ---------
SYS		                             OTT		         TIBERO	            tiero7		    31-OCT-25	    NO	      NO	   YES	     NO

-- #Query nextval
SQL> select * from SEQ_VIEW_NEXTVAL@ott;

SEQ_NEXTVAL
-----------
	  3
	  
-- #Query currval
SQL> select * from SEQ_VIEW_CURRENT@ott;

SEQ_CURRENTVAL
--------------
	     3