Skip to main content

Search

Welcome to Tibero GTS!

DBLINK Workaround (Sequence Call)

Document Type | Technical Information

Category | Interface/Integration

Document Number | TIITI020

 

Overview

This provides a workaround that can be applied when there are restrictions calling a sequence via DBLink from Oracle to Tibero.
 
 

Method

1. Create Function

 Create a function for the sequence as a view in Tibero.
 

2. Apply View Call and Usage Form

Modify the usage to call a view instead of the sequence when using Oracle to Tibero Link.
-- Create test sequence

CREATE SEQUENCE SEQ_TEST_0627 -- Sequence name EX_SEQ
INCREMENT BY 1 -- Increment number 1

START WITH 1 -- Starting number 1
MINVALUE 1 -- Minimum value 1
MAXVALUE 1000 -- Maximum value 1000
NOCYCLE -- No cycling
noCACHE;


-- Create test table

CREATE TABLE T2_TIB (SEQ NUMBER, TEXT VARCHAR(20));
INSERT INTO T2_TIB VALUES (SEQ_TEST_0627.NEXTVAL, 'Test');

-- Query before workaround

SELECT SEQ.NEXTVAL@OT_0627 AS SEQ_TIB,
                          'TEXT' AS TEXT_TIB
FROM DUAL;


-- Query after workaround

-- Create function to call sequence

CREATE OR REPLACE FUNCTION SEQ_TEST_0627_FUNC RETURN NUMBER
IS V_NEXTVAL NUMBER;

BEGIN
       SELECT SEQ_TEST_0627.NEXTVAL INTO V_NEXTVAL FROM DUAL;
       RETURN V_NEXTVAL;

END SEQ_TEST_0627_FUNC;


-- Create view that calls the function

CREATE OR REPLACE VIEW SEQ_TEST_0627_VIEW AS
SELECT SEQ_TEST_0627_FUNC AS NEXTVAL FROM DUAL;

SELECT
        (SELECT NEXTVAL FROM SEQ_TEST_0627_VIEW@OT_0627) AS SEQ_TIB,
         'TEXT' AS TEXT_TIB
FROM DUAL;

 

 

 

Related to