Striim 3.9.4 / 3.9.5 documentation

OracleReader WAEvent fields for DDL operations

For DML operations, see OracleReader WAEvent fields.

The output data type for OracleReader is WAEvent. For DDL operations, the fields are:

data: contains the command string, for example, CREATE TABLE EMP (EMPID INT PRIMARY KEY, ENAME VARCHAR2(10)) or DROP TABLE EMP.

metadata: includes the following fields:

  • CatalogName: reserved

  • CatalogObjectType: TABLE / VIEW / INDEX / etc.

  • ColumnMetadata: a sub-field of TableMetadata containing descriptions of the columns

  • COMMITSCN: the SCN of the commit for the transaction

  • CURRENTSCN: the SCN of the current statement

  • ObjectName: the name of the table / view / index / etc. being acted on or, in the case of a RENAME, the new name

  • OperationName: the first keyword in the DDL command

  • OperationSubName: any additional keywords in the DDL command besides the first

  • OperationType: value for DDL operations is always DDL

  • OwnerName: owner of the object being acted on

  • SchemaName: schema containing the object being acted on

  • SCN: reserved

  • STARTSCN: the SCN of the start of the transaction

  • TableMetadata: description of the table, including sub-field

  • TableName: the name of the table (omitted in metadata for non-table operations)

  • TimeStamp: timestamp from the CDC log

  • TxnID: transaction ID

Example WAEvent values for supported DDL commands

DDL command

example WAEvent values

CREATE TABLE ...

data:

CREATE TABLE TEST (
  EMPID INT PRIMARY KEY,
  ENAME VARCHAR2(10))

metadata:

{
  "OperationSubName": "CREATE_TABLE",
  "TxnID": "8.15.261430",
  "TimeStamp": "2017-02-10T17:46:19.000-08:00",
  "COMMITSCN": "403795721",
  "CatalogName": null,
  "CURRENTSCN": "403795718",
  "STARTSCN": "403795694",
  "ObjectName": "TEST",
  "OperationName": "CREATE",
  "SCN": "403795718",
  "OperationType": "DDL",
  "TableMetadata": {
    "ColumnMetadata": [
      {
        "ColumnPrecision": 0,
        "ColumnIsKey": true,
        "ColumnLength": 0,
        "ColumnType": "NUMBER",
        "ColumnScale": 0,
        "ColumnName": "EMPID",
        "ColumnIsNullable": true,
        "ColumnIndex": 0
      },
      {
        "ColumnPrecision": 0,
        "ColumnIsKey": false,
        "ColumnLength": 10,
        "ColumnType": "VARCHAR2",
        "ColumnScale": 0,
        "ColumnName": "ENAME",
        "ColumnIsNullable": true,
        "ColumnIndex": 1
      }
    ],
    "TableName": "TEST",
    "OwnerName": "STRIIM"
  },
  "SchemaName": "STRIIM",
  "CatalogObjectType": "TABLE"
}
ALTER TABLE ... 
  ADD (<name> <data type>)

data:

ALTER TABLE TEST ADD (
  SALARY NUMBER)

metadata:

{
  "OperationSubName": "ALTER_TABLE_ADD_COLUMN",
  "TxnID": "4.12.411190",
  "TimeStamp": "2017-02-10T17:46:30.000-08:00",
  "COMMITSCN": "403795759",
  "CatalogName": null,
  "CURRENTSCN": "403795755",
  "STARTSCN": "403795749",
  "ObjectName": "TEST",
  "OperationName": "ALTER",
  "SCN": "403795755",
  "OperationType": "DDL",
  "TableMetadata": {
    "ColumnMetadata": [
      {
        "ColumnPrecision": 0,
        "ColumnIsKey": true,
        "ColumnLength": 0,
        "ColumnType": "NUMBER",
        "ColumnScale": 0,
        "ColumnName": "EMPID",
        "ColumnIsNullable": true,
        "ColumnIndex": 0
      },
      {
        "ColumnPrecision": 0,
        "ColumnIsKey": false,
        "ColumnLength": 10,
        "ColumnType": "VARCHAR2",
        "ColumnScale": 0,
        "ColumnName": "ENAME",
        "ColumnIsNullable": true,
        "ColumnIndex": 1
      },
      {
        "ColumnPrecision": 0,
        "ColumnIsKey": false,
        "ColumnLength": 0,
        "ColumnType": "NUMBER",
        "ColumnScale": 0,
        "ColumnName": "SALARY",
        "ColumnIsNullable": true,
        "ColumnIndex": 2
      }
    ],
    "TableName": "TEST",
    "OwnerName": "STRIIM"
  },
  "SchemaName": "STRIIM",
  "CatalogObjectType": "TABLE"
}
ALTER TABLE ...
  DROP COLUMN ...

data:

ALTER TABLE TEST
  DROP (SALARY)

metadata:

  "OperationSubName": "ALTER_TABLE_DROP_COLUMN",
  "TxnID": "10.1.258799",
  "TimeStamp": "2017-02-11T09:59:51.000-08:00",
  "COMMITSCN": "403888603",
  "CatalogName": null,
  "CURRENTSCN": "403888599",
  "STARTSCN": "403888593",
  "ObjectName": "TEST",
  "OperationName": "ALTER",
  "SCN": "403888599",
  "OperationType": "DDL",
  "TableMetadata": {
    "ColumnMetadata": [
      {
        "ColumnPrecision": 0,
        "ColumnIsKey": false,
        "ColumnLength": 0,
        "ColumnType": "NUMBER",
        "ColumnScale": 0,
        "ColumnName": "EMPID",
        "ColumnIsNullable": true,
        "ColumnIndex": 0
      },
      {
        "ColumnPrecision": 0,
        "ColumnIsKey": false,
        "ColumnLength": 10,
        "ColumnType": "VARCHAR2",
        "ColumnScale": 0,
        "ColumnName": "ENAME",
        "ColumnIsNullable": true,
        "ColumnIndex": 1
      }
    ],
    "TableName": "TEST",
    "OwnerName": "STRIIM"
  },
  "SchemaName": "STRIIM",
  "CatalogObjectType": "TABLE"
}
ALTER TABLE ...
  MODIFY (<column> ...

data:

ALTER TABLE TEST MODIFY
  SALARY INT

metadata:

{
  "OperationSubName": "ALTER_TABLE_MODIFY_COLUMN",
  "TxnID": "2.12.258663",
  "TimeStamp": "2017-02-11T10:02:20.000-08:00",
  "COMMITSCN": "403896354",
  "CatalogName": null,
  "CURRENTSCN": "403896349",
  "STARTSCN": "403896343",
  "ObjectName": "TEST",
  "OperationName": "ALTER",
  "SCN": "403896349",
  "OperationType": "DDL",
  "TableMetadata": {
    "ColumnMetadata": [
      {
        "ColumnPrecision": 0,
        "ColumnIsKey": true,
        "ColumnLength": 0,
        "ColumnType": "NUMBER",
        "ColumnScale": 0,
        "ColumnName": "EMPID",
        "ColumnIsNullable": true,
        "ColumnIndex": 0
      },
      {
        "ColumnPrecision": 0,
        "ColumnIsKey": false,
        "ColumnLength": 10,
        "ColumnType": "VARCHAR2",
        "ColumnScale": 0,
        "ColumnName": "ENAME",
        "ColumnIsNullable": true,
        "ColumnIndex": 1
      },
      {
        "ColumnPrecision": 0,
        "ColumnIsKey": false,
        "ColumnLength": 0,
        "ColumnType": "NUMBER",
        "ColumnScale": 0,
        "ColumnName": "SALARY",
        "ColumnIsNullable": true,
        "ColumnIndex": 2
      }
    ],
    "TableName": "TEST",
    "OwnerName": "STRIIM"
  },
  "SchemaName": "STRIIM",
  "CatalogObjectType": "TABLE"
}
ALTER ... RENAME ...

data:

ALTER TABLE TEST
  RENAME TO CUSTOMER

metadata:

{
  "OperationSubName": "ALTER_TABLE_RENAME",
  "TxnID": "10.3.258647",
  "TimeStamp": "2017-02-10T17:53:31.000-08:00",
  "COMMITSCN": "403796279",
  "CatalogName": null,
  "CURRENTSCN": "403796276",
  "STARTSCN": "403796267",
  "ObjectName": "CUSTOMER",
  "OperationName": "ALTER",
  "SCN": "403796276",
  "OperationType": "DDL",
  "TableMetadata": {
    "ColumnMetadata": [
      {
        "ColumnPrecision": 0,
        "ColumnIsKey": false,
        "ColumnLength": 0,
        "ColumnType": "NUMBER",
        "ColumnScale": 0,
        "ColumnName": "EMPID",
        "ColumnIsNullable": true,
        "ColumnIndex": 0
      },
      {
        "ColumnPrecision": 0,
        "ColumnIsKey": false,
        "ColumnLength": 10,
        "ColumnType": "VARCHAR2",
        "ColumnScale": 0,
        "ColumnName": "ENAME",
        "ColumnIsNullable": true,
        "ColumnIndex": 1
      },
      {
        "ColumnPrecision": 0,
        "ColumnIsKey": false,
        "ColumnLength": 0,
        "ColumnType": "NUMBER",
        "ColumnScale": 0,
        "ColumnName": "SALARY",
        "ColumnIsNullable": true,
        "ColumnIndex": 2
      }
    ],
    "TableName": "CUSTOMER",
    "OldTblName": "STRIIM.TEST",
    "OwnerName": "STRIIM"
  },
  "SchemaName": "STRIIM",
  "CatalogObjectType": "TABLE"
}
DROP ...

data:

DROP TABLE TEST

metadata:

{
  "OperationSubName": "DROP_TABLE",
  "TxnID": "8.18.261385",
  "TimeStamp": "2017-02-10T17:48:29.000-08:00",
  "COMMITSCN": "403795874",
  "CatalogName": null,
  "CURRENTSCN": "403795870",
  "STARTSCN": "403795837",
  "ObjectName": "TEST",
  "OperationName": "DROP",
  "SCN": "403795870",
  "OperationType": "DDL",
  "SchemaName": "STRIIM",
  "CatalogObjectType": "TABLE"
}
CREATE UNIQUE INDEX ...

data:

CREATE UNIQUE INDEX TESTINDEX
  ON TEST (EMPID, ENAME)

metadata:

{
  "OperationSubName": "CREATE_INDEX",
  "TxnID": "5.21.258597",
  "TimeStamp": "2017-02-10T17:55:57.000-08:00",
  "COMMITSCN": "403796469",
  "CatalogName": null,
  "CURRENTSCN": "403796466",
  "STARTSCN": "403796460",
  "ObjectName": "TESTINDEX",
  "OperationName": "CREATE",
  "SCN": "403796466",
  "OperationType": "DDL",
  "SchemaName": "STRIIM",
  "CatalogObjectType": "INDEX"
}
ANALYZE TABLE ...
  VALIDATE STRUCTURE

data:

ANALYZE TABLE TEST VALIDATE STRUCTURE

metadata:

{
  "OperationSubName": "ANALYZE_TABLE",
  "TxnID": "6.13.240947",
  "TimeStamp": "2017-02-10T17:56:45.000-08:00",
  "COMMITSCN": "403796529",
  "CatalogName": null,
  "CURRENTSCN": "403796527",
  "STARTSCN": "403796520",
  "ObjectName": "TEST",
  "OperationName": "ANALYZE",
  "SCN": "403796527",
  "OperationType": "DDL",
  "SchemaName": "STRIIM",
  "CatalogObjectType": "TABLE"
}
CREATE VIEW ...

data:

CREATE VIEW MYVIEW AS
  SELECT EMPID,ENAME
  FROM TEST
  WHERE SALARY>43679

metadata:

{
  "OperationSubName": "CREATE_VIEW",
  "TxnID": "6.9.241087",
  "TimeStamp": "2017-02-10T17:59:07.000-08:00",
  "COMMITSCN": "403796658",
  "CatalogName": null,
  "CURRENTSCN": "403796653",
  "STARTSCN": "403796649",
  "ObjectName": "MYVIEW",
  "OperationName": "CREATE",
  "SCN": "403796653",
  "OperationType": "DDL",
  "SchemaName": "STRIIM",
  "CatalogObjectType": "VIEW"
}



	



CREATE OR REPLACE VIEW ...

(updating SELECT statement)

data:

CREATE OR REPLACE VIEW MYVIEW AS
  SELECT EMPID,ENAME
  FROM TEST 
  WHERE SALARY>100000

metadata:

{
  "OperationSubName": "CREATE_VIEW",
  "TxnID": "2.30.258751",
  "TimeStamp": "2017-02-10T18:00:51.000-08:00",
  "COMMITSCN": "403796921",
  "CatalogName": null,
  "CURRENTSCN": "403796917",
  "STARTSCN": "403796913",
  "ObjectName": "MYVIEW",
  "OperationName": "CREATE",
  "SCN": "403796917",
  "OperationType": "DDL",
  "SchemaName": "STRIIM",
  "CatalogObjectType": "VIEW"
}
CREATE OR
  REPLACE PACKAGE ...

data:

CREATE OR REPLACE PACKAGE emp_mgmt AS FUNCTION hire (
last_name VARCHAR2, job_id VARCHAR2, manager_id NUMBER, 
salary NUMBER, commission_pct NUMBER, department_id NUMBER) 
RETURN NUMBER;\nFUNCTION create_dept( department_id NUMBER, 
location_id NUMBER) RETURN NUMBER;
\nPROCEDURE remove_emp(employee_id NUMBER);
\nPROCEDURE remove_dept(department_id NUMBER);
\nPROCEDURE increase_sal(employee_id NUMBER, salary_incr NUMBER);
\nPROCEDURE increase_comm(employee_id NUMBER, comm_incr NUMBER);
\nno_comm EXCEPTION;
\nno_sal EXCEPTION;
\nEND emp_mgmt;

metadata:

{
  "OperationSubName": "CREATE_PACKAGE",
  "TxnID": "2.0.258774",
  "TimeStamp": "2017-02-11T17:05:55.000-08:00",
  "COMMITSCN": "404005562",
  "CatalogName": null,
  "CURRENTSCN": "404005557",
  "STARTSCN": "404005553",
  "ObjectName": "EMP_MGMT",
  "OperationName": "CREATE",
  "SCN": "404005557",
  "OperationType": "DDL",
  "SchemaName": "ROBERT",
  "CatalogObjectType": "PACKAGE"
} 
GRANT ...

data:

GRANT SELECT ON TEST TO STRIIM

metadata:

{
  "OperationSubName": "GRANT",
  "TxnID": "6.9.241213",
  "TimeStamp": "2017-02-11T10:07:21.000-08:00",
  "COMMITSCN": "403898105",
  "CatalogName": null,
  "CURRENTSCN": "403898102",
  "STARTSCN": "403898098",
  "ObjectName": null,
  "OperationName": "GRANT",
  "SCN": "403898102",
  "OperationType": "DDL",
  "SchemaName": "STRIIM",
  "CatalogObjectType": "UNKNOWN"
}
REVOKE ...

data:

REVOKE SELECT ON TEST FROM STRIIM

metadata:

{ 
 "OperationSubName": "REVOKE",
  "TxnID": "5.28.258661",
  "TimeStamp": "2017-02-11T10:07:31.000-08:00",
  "COMMITSCN": "403898124",
  "CatalogName": null,
  "CURRENTSCN": "403898121",
  "STARTSCN": "403898116",
  "ObjectName": null,
  "OperationName": "REVOKE",
  "SCN": "403898121",
  "OperationType": "DDL",
  "SchemaName": "STRIIM",
  "CatalogObjectType": "UNKNOWN"
}