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;