Skip to main content

Sample TQL application using change data

The following sample application uses OracleReader but the approach is the same for all CDC readers.

CREATE APPLICATION SampleCDCApp;
CREATE SOURCE OracleCDCIn USING OracleReader (
  Username:'striim',
  Password:'passwd',
  ConnectionURL:'203.0.113.49:1521:orcl',
  Tables:'MYSCHEMA.POSAUTHORIZATIONS',
  FetchSize:1
) 
OUTPUT TO OracleCDCStream;
 
CREATE TYPE PosMeta(
 tableName String,
 operationName String,
 txnID String,
 timestamp String
);
CREATE STREAM PosMetaStream OF PosMeta; 
CREATE TYPE PosData(
  businessName String,
  accountName String,
  pos String,
  code String
);
CREATE STREAM PosDataStream OF PosData;
-- extract the metadata values
CREATE CQ OracleToPosMeta
INSERT INTO PosMetaStream
SELECT
    META(m,"TableName").toString(),
    META(m,"OperationName").toString(),
    META(m,"TxnID").toString(),
    META(m,"TimeStamp").toString()
       FROM OracleCDCStream m;
-- write the metadata values to SysOut
CREATE TARGET Metadump USING SysOut(name:meta) INPUT FROM PosMetaStream;
 
-- extract the data values
CREATE CQ OracleToPosData
INSERT INTO PosDataStream
SELECT
    CASE WHEN IS_PRESENT(x,data,0)==true THEN data[0].toString()
        ELSE "NOT_PRESENT"
    END,
    CASE WHEN IS_PRESENT(x,data,1)==true THEN data[1].toString()
        ELSE "NOT_PRESENT"
    END,
    CASE WHEN IS_PRESENT(x,data,2)==true THEN data[2].toString()
        ELSE "NOT_PRESENT"
    END,
    CASE WHEN IS_PRESENT(x,data,3)==true THEN data[3].toString()
        ELSE "NOT_PRESENT"
    END
FROM OracleCDCStream x;
-- write dump the data values to SysOut
CREATE TARGET  Datadump USING SysOut(name:data) INPUT FROM PosDataStream;
END APPLICATION SampleCDCApp;

The output for the three operations described in OracleReader example output would be similar to:

meta: PosMeta_1_0{
  tableName: "SCOTT.POSAUTHORIZATIONS"
  operationName: "INSERT"
  txnID: "4.0.1742"
  timestamp: "2015-12-11T16:31:30.000-08:00"
};
data: PosData_1_0{
  businessName: "COMPANY 1"
  accountName: "D6RJPwyuLXoLqQRQcOcouJ26KGxJSf6hgbu"
  pos: "6705362103919221351"
  code: "0"
};
meta: PosMeta_1_0{
  tableName: "SCOTT.POSAUTHORIZATIONS"
  operationName: "UPDATE"
  txnID: "4.0.1742"
  timestamp: "2015-12-11T16:31:30.000-08:00"
};
data: PosData_1_0{
  businessName: "COMPANY 5A"
  accountName: "D6RJPwyuLXoLqQRQcOcouJ26KGxJSf6hgbu"
  pos: "6705362103919221351"
  code: "0"
};
meta: PosMeta_1_0{
  tableName: "SCOTT.POSAUTHORIZATIONS"
  operationName: "DELETE"
  txnID: "4.0.1742"
  timestamp: "2015-12-11T16:31:30.000-08:00"
};
data: PosData_1_0{
  businessName: "COMPANY 5A"
  accountName: "D6RJPwyuLXoLqQRQcOcouJ26KGxJSf6hgbu"
  pos: "6705362103919221351"
  code: "0"
};