Striim 3.10.1 documentation

Oracle Reader 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"
}