Skip to main content

Search

Welcome to Tibero GTS!

DBLINK Workaround (Using select sysdate function in insert)

Document Type | Technical Information

Category | Interface/Integration

Document Number | TIITI022

 

Overview

This document guides a workaround solution applicable when there are restrictions performing insert and select using DBLink from Oracle to Tibero.
  • When the insert table exists in Tibero and the select table exists in Oracle
  • When using the sysdate function in the select statement
Note
In this document, the Oracle DBMS_HS_PASSTHROUGH package, which allows executing the entire query remotely through the LINK, is used.
 
 

Method

Performing Insert (Using Tibero to Oracle LINK)

Move the entire query to Tibero and perform the insert using the Tibero to Oracle link.
 

1. Create test tables

DROP TABLE T3_TIB;
CREATE TABLE T3_TIB (LAST_MOD_TS DATE);

DROP TABLE T3_ORA;
CREATE TABLE T3_ORA (TM_STMP DATE);

INSERT INTO T3_ORA VALUES (SYSDATE);

INSERT INTO T3_ORA VALUES (SYSDATE);

INSERT INTO T3_ORA VALUES (SYSDATE);

INSERT INTO T3_ORA VALUES (SYSDATE);


2. Query before workaround

INSERT INTO T3_TIB@OT_0627
                 (
                                    LAST_MOD_TS
                         )
                 (

                 SELECT
                                    SYSDATE AS LAST_MOD_TS
                              FROM T3_ORA
                 WHERE
                 );


3. Query after workaround

DECLARE

C CHAR;

NR CHAR;

BEGIN

C := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@OT_0627;

DBMS_HS_PASSTHROUGH.PARSE@OT_0627(C,โ€™

INSERT INTO T3_TIB

                                  (

                                          LAST_MOD_TS

                                  )

                                  (SELECT

                                                   SYSDATE AS LAST_MOD_TS

                                         FROM T3_ORA@TO_0627

                                         WHERE TO_CHAR(TM_STMP,โ€YYYYMMDDโ€)>20200627)โ€™);

NR := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@OT_0627(C);

DBMS_OUTPUT.PUT_LINE(NR||โ€™ ROW INSERTED.โ€™);

DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@OT_0627(C);

commit;

END;

/
SQL


4. How to use DBMS_HS_PASSTHROUGH

--Create test tables--

CREATE TABLE T3_TIB (CD CHAR(1) , CD_NO NUMBER , TEST_TEXT VARCHAR(20));

INSERT INTO T3_TIB VALUES (โ€˜Aโ€™,1,โ€™ORACLEโ€™);

INSERT INTO T3_TIB VALUES (โ€˜Aโ€™,2,โ€™ORACLEโ€™);

INSERT INTO T3_TIB VALUES (โ€˜Aโ€™,3,โ€™ORACLEโ€™);

INSERT INTO T3_TIB VALUES (โ€˜Bโ€™,4,โ€™ORACLEโ€™);

INSERT INTO T3_TIB VALUES (โ€˜Cโ€™,5,โ€™ORACLEโ€™);

CREATE TABLE T3_ORA (ORA_CD CHAR(1) , ORA_CD_NO NUMBER);

INSERT INTO T3_ORA VALUES (โ€˜Aโ€™,1);

INSERT INTO T3_ORA VALUES (โ€˜Aโ€™,1);

INSERT INTO T3_ORA VALUES (โ€˜Bโ€™,2);

INSERT INTO T3_ORA VALUES (โ€˜Bโ€™,3);

INSERT INTO T3_ORA VALUES (โ€˜Cโ€™,1);

DECLARE

C CHAR;

NR CHAR;

BEGIN

C := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@OT_0627;

DBMS_HS_PASSTHROUGH.PARSE@OT_0627(C,โ€™

UPDATE T3_TIB T

               SET T.TEST_TEXT=โ€TIBEROโ€

WHERE (T.CD, T.CD_NO) IN

(SELECT * FROM T3_ORA@TO_0627 WHERE ORA_CD=โ€Aโ€ AND ORA_CD_NO < 3)โ€™);

NR := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@OT_0627(C);

DBMS_OUTPUT.PUT_LINE(NR||โ€™ ROW UPDATE.โ€™);

DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@OT_0627(C);

commit;

END;

/

 

 

 

Related to