This section describes the configuration of an apply process that will apply changes to a non-Oracle database.

When applying changes to a non-Oracle database, an apply process applies changes made to columns of only the following data types:

• CHAR

• VARCHAR2

• NCHAR

• NVARCHAR2

• NUMBER

• DATE

• RAW

• TIMESTAMP

• TIMESTAMP WITH TIME ZONE

• TIMESTAMP WITH LOCAL TIME ZONE

• INTERVAL YEAR TO MONTH

• INTERVAL DAY TO SECOND

The apply process does not apply changes in columns of the following data types to non-Oracle databases: CLOB, NCLOB, BLOB, BFILE, LONG, LONG RAW, ROWID, UROWID, user-defined types (including object types, REFs, varrays, and nested tables), and Oracle-supplied types (including Any types, XML types, spatial types, and media types). The apply process raises an error when an LCR contains a data type that is not listed, and the transaction containing the LCR that caused the error is moved to the error queue in the Oracle database.

Each Oracle Database Gateway might have further limitations regarding data types. For a data type to be supported in an Oracle to non-Oracle environment, the data type must be supported by both Oracle Streams and the Oracle Database Gateway being used.

When you specify that DML changes made to certain tables should be applied at a non-Oracle database, an apply process can apply only the following types of DML changes:

• INSERT

• UPDATE

• DELETE

1. Use the DBMS_HS_PASSTHROUGH package or the tools supplied with the non-Oracle database to create the table at the non-Oracle database.

   The following is an example that uses the DBMS_HS_PASSTHROUGH package to create the hr.regions table in the het.example.com non-Oracle database:

   DECLARE 
    ret INTEGER; 
   BEGIN 
   ret := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@het.example.com ( 
     'CREATE TABLE regions (region_id INTEGER, region_name VARCHAR(50))'); 
   END; 
   / 
   COMMIT; 

   See Also:

   Oracle Database Heterogeneous Connectivity User's Guide and the Oracle documentation for your specific Oracle Database Gateway for more information about Heterogeneous Services and Oracle Database Gateway

2. If the changes that will be shared between the Oracle and non-Oracle database are captured by a capture process or synchronous capture at the Oracle database, then prepare all tables that will share data for instantiation.

   See Also:

   "Preparing Database Objects for Instantiation at a Source Database"

3. Create a PL/SQL procedure (or a C program) that performs the following actions:

   • Gets the current SCN using the GET_SYSTEM_CHANGE_NUMBER function in the DBMS_FLASHBACK package.

   • Invokes the ENABLE_AT_SYSTEM_CHANGE_NUMBER procedure in the DBMS_FLASHBACK package to set the current session to the obtained SCN. This action ensures that all fetches are done using the same SCN.

   • Populates the table at the non-Oracle site by fetching row by row from the table at the Oracle database and then inserting row by row into the table at the non-Oracle database. All fetches should be done at the SCN obtained using the GET_SYSTEM_CHANGE_NUMBER function.

   For example, the following PL/SQL procedure gets the flashback SCN, fetches each row in the hr.regions table in the current Oracle database, and inserts them into the hr.regions table in the het.example.com non-Oracle database. Notice that flashback is disabled before the rows are inserted into the non-Oracle database.

   SET SERVEROUTPUT ON
   CREATE OR REPLACE PROCEDURE insert_reg IS
     CURSOR c1 IS
       SELECT region_id, region_name FROM hr.regions;
     c1_rec c1 % ROWTYPE;
     scn NUMBER;
   BEGIN
     scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
     DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(
        query_scn  =>  scn);
     /* Open c1 in flashback mode */
     OPEN c1;
     /* Disable Flashback */
     DBMS_FLASHBACK.DISABLE;
     LOOP
       FETCH c1 INTO c1_rec;
       EXIT WHEN c1%NOTFOUND;
      /*
        Note that all the DML operations inside the loop are performed
        with Flashback disabled
      */
      INSERT INTO hr.regions@het.example.com VALUES (
        c1_rec.region_id,
        c1_rec.region_name);
     END LOOP;
     COMMIT;
     DBMS_OUTPUT.PUT_LINE('SCN = ' || scn);
     EXCEPTION WHEN OTHERS THEN 
       DBMS_FLASHBACK.DISABLE; 
       RAISE;
   END;
   /
   

   Make a note of the SCN returned.

   If the Oracle Database Gateway you are using supports the Heterogeneous Services callback functionality, then you can replace the loop in the previous example with the following SQL statement:

   INSERT INTO hr.region@het.example.com SELECT * FROM hr.region@!;
   

   Note:

   The user who creates and runs the procedure in the previous example must have EXECUTE privilege on the DBMS_FLASHBACK package and all privileges on the tables involved.

   See Also:

   Oracle Database Heterogeneous Connectivity User's Guide and the Oracle documentation for your specific Oracle Database Gateway for information about callback functionality and your Oracle Database Gateway

4. Set the instantiation SCN for the table at the non-Oracle database. Specify the SCN you obtained in Step 3 in the SET_TABLE_INSTANTIATION_SCN procedure in the DBMS_APPLY_ADM package to instruct the apply process to skip all LCRs with changes that occurred before the SCN you obtained in Step 3. Ensure that you set the apply_database_link parameter to the database link for the remote non-Oracle database.

   See Also:

   "Setting Instantiation SCNs at a Destination Database" and Oracle Database PL/SQL Packages and Types Reference for more information about the SET_TABLE_INSTANTIATION_SCN procedure