Publishing events from Oracle updates

I am trying to publish a message whenever a particular column in a table is changed. I  was first going to use Oracle's Advanced Queue (AQ) which is similar to MS SQL's SQL Service Broker (SSB). Upon finding there to be no Oracle specific adapters, I've had to improvise.

I've created a table and trigger in Oracle to be used as a queue. When the column is changed, a row is inserted into the queue table with the ID, old value and new value.

I'm trying to configure an ODBC adapter in Neuron to poll this table, but keep running into difficulties. I cannot seem to find any documentation on setting up and ODBC adapter.

I have created an Adapter Registration called "ODBC Adapter". I have created an ODBC DSN for my database. I have created an Adapter Endpoint using the ODBC Adapter in Publish mode. I am able to test the connection successfully in the adapter connection properties dialog.

With my current configuration, I get the following in my logs. The table exists and can be queried from SQL*PLUS using the same credentials assigned to the ODBC DSN.

2018-07-06 10:32:04.359-06:00 [148] DEBUG - Creating the '******ChangeEndpoint' Adapter Endpoint ...2018-07-06 10:32:04.359-06:00 [148] DEBUG - Preparing to run the '******ChangeEndpoint' Adapter Endpoint under the current service identity '' ...2018-07-06 10:32:04.359-06:00 [148] DEBUG - Loading the 'ODBC Adapter' Adapter assembly for the '******ChangeEndpoint' Adapter Endpoint ...
2018-07-06 10:32:04.374-06:00 [148] DEBUG - Configuring the '******ChangeEndpoint' Adapter Endpoint for MODE 'Publish' ...

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: ConnectionString = *******************

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: VerifyConnect = True

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: Transactional = None

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: IsolationLevelString = Serializable

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: TransactionTimeout = 60

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: ConnectionTimeout = 15

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: CommandTimeout = 30

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: EnforceTypedParams = True

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: SqlEnabled = False

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: SqlCheckingEnabled = False

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: SendTopic = ******Change

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: PollingInterval = 1

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: OdbcCommandType = TableDirect

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: SelectStatement = 

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: SelectParameters = 

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: UpdateDeleteStatement = 

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: UpdateDeleteParameters = 

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: TableDirectName = *****_QUEUE

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: ForXmlClause = False

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: RootNodeName = ******ChangeMessage

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: RootNodeNameSpace = 

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: RowNodeName = ******Change

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: GenerateSchema = False

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: LimitRows = -1

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: DeleteTable = True

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: PublishOnNoData = False

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: RenameSourceFileExtension = dbq

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: ErrorMode = Error

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: ErrorHandling = StopPollingOnError

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: QueryRootNodeName = 

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: QueryRowNodeName = 

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: QueryRootNodeNameSpace = 

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: QueryGenerateSchema = False

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: QueryLimitRows = -1

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: BatchSize = -1

2018-07-06 10:32:04.374-06:00 [148] DEBUG - Connecting the '******ChangeEndpoint' Adapter Endpoint using '******ChangePublisher' Party...

2018-07-06 10:32:06.374-06:00 [287] ERROR - A general publishing error occurred in the ODBC Adapter.



THE ADAPTER WILL NOW BE DISCONNECTED.

Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information.



Stack Trace:

   at System.Data.Common.DbCommandBuilder.BuildInformation(DataTable schemaTable)

   at System.Data.Common.DbCommandBuilder.BuildCache(Boolean closeConnection, DataRow dataRow, Boolean useColumnsForParameterNames)

   at System.Data.Common.DbCommandBuilder.GetDeleteCommand(DataRow dataRow, Boolean useColumnsForParameterNames)

   at System.Data.Odbc.OdbcCommandBuilder.GetDeleteCommand()

   at Neuron.Esb.Adapters.OdbcAdapter.GetXMLAndDelete(OdbcCommand command, OdbcCommand deleteCommand, String rootName, String rootNamespace, Boolean generateSchema, List`1 properties, Int32& rowsReturned)

   at Neuron.Esb.Adapters.OdbcAdapter.ReceiveByTableDirect()

   at Neuron.Esb.Adapters.OdbcAdapter.ReceiveFromDatasource()

   at Neuron.Esb.Adapters.OdbcAdapter.TryReceive()



System.InvalidOperationException: Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information.

   at System.Data.Common.DbCommandBuilder.BuildInformation(DataTable schemaTable)

   at System.Data.Common.DbCommandBuilder.BuildCache(Boolean closeConnection, DataRow dataRow, Boolean useColumnsForParameterNames)

   at System.Data.Common.DbCommandBuilder.GetDeleteCommand(DataRow dataRow, Boolean useColumnsForParameterNames)

   at System.Data.Odbc.OdbcCommandBuilder.GetDeleteCommand()

   at Neuron.Esb.Adapters.OdbcAdapter.GetXMLAndDelete(OdbcCommand command, OdbcCommand deleteCommand, String rootName, String rootNamespace, Boolean generateSchema, List`1 properties, Int32& rowsReturned)

   at Neuron.Esb.Adapters.OdbcAdapter.ReceiveByTableDirect()

   at Neuron.Esb.Adapters.OdbcAdapter.ReceiveFromDatasource()

   at Neuron.Esb.Adapters.OdbcAdapter.TryReceive()

I've tried setting the Delete Table property to false, which results in the following log:

2018-07-06 10:30:09.260-06:00 [291] ERROR - A general publishing error occurred in the ODBC Adapter.THE ADAPTER WILL NOW BE DISCONNECTED.
ERROR [42S02] [Oracle][ODBC][Ora]ORA-00903: invalid table name





Stack Trace:

   at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)

   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)

   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)

   at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)

   at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)

   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)

   at Neuron.Esb.Adapters.OdbcAdapter.ProcessAdapter(DataTable dt, OdbcDataAdapter adapter, String rootNamespace, Int32 limitRowsForOutput, Boolean generateSchema, Int32& rowsReturned)

   at Neuron.Esb.Adapters.OdbcAdapter.GetXMLAndDelete(OdbcCommand command, OdbcCommand deleteCommand, String rootName, String rootNamespace, Boolean generateSchema, List`1 properties, Int32& rowsReturned)

   at Neuron.Esb.Adapters.OdbcAdapter.ReceiveByTableDirect()

   at Neuron.Esb.Adapters.OdbcAdapter.ReceiveFromDatasource()

   at Neuron.Esb.Adapters.OdbcAdapter.TryReceive()



System.Data.Odbc.OdbcException (0x80131937): ERROR [42S02] [Oracle][ODBC][Ora]ORA-00903: invalid table name



   at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)

   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)

   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)

   at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)

   at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)

   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)

   at Neuron.Esb.Adapters.OdbcAdapter.ProcessAdapter(DataTable dt, OdbcDataAdapter adapter, String rootNamespace, Int32 limitRowsForOutput, Boolean generateSchema, Int32& rowsReturned)

   at Neuron.Esb.Adapters.OdbcAdapter.GetXMLAndDelete(OdbcCommand command, OdbcCommand deleteCommand, String rootName, String rootNamespace, Boolean generateSchema, List`1 properties, Int32& rowsReturned)

   at Neuron.Esb.Adapters.OdbcAdapter.ReceiveByTableDirect()

   at Neuron.Esb.Adapters.OdbcAdapter.ReceiveFromDatasource()

   at Neuron.Esb.Adapters.OdbcAdapter.TryReceive()