Efficient Solution for Real-Time Monitoring of Changes in Oracle Tables Using the oracledb Library

[technervs]

Problem Description:

I'm currently working on a project involving real-time monitoring of changes in Oracle tables. I'm using the oracledb library to establish a connection to the Oracle database, and I'm trying to implement a monitoring mechanism similar to Change Data Capture (CDC) or Continuous Query Notification (CQN). However, I'm encountering some limitations in the implementation.

Problem Details:

1. I tried using the CQN feature of oracledb to receive real-time notifications about changes in the tables, but this resulted in ORA-29976 errors.

2. Additionally, it's not feasible to set up triggers on the tables to capture changes as we don't have permission to modify table settings in the environment.

Request:

I'm curious to know if there's a more effective solution for implementing real-time monitoring of changes in Oracle tables using the oracledb library. If anyone has insights into how to improve the current approach or if there are library features that I'm not utilizing correctly, I would greatly appreciate any guidance.

Alternative Suggestion:

If anyone has any suggestions for enhancing this implementation, especially regarding how to receive real-time notifications about changes without the need for triggers or table settings modifications, it would be extremely helpful. My goal is to find a solution that is more responsive and efficient for monitoring changes in Oracle tables without altering existing settings.

I'm thankful in advance for any assistance or insights you can provide. This would be highly valuable in refining this functionality and benefiting the community working with the oracledb library.

[cjbj]

@technervs Can you explain more about the CQN issue? What are the details of what you tried (versions, options, etc)?

[technervs]

@technervs Can you explain more about the CQN issue? What are the details of what you tried (versions, options, etc)?

I have a custom node on n8n using the lib "oracledb": "^6.2.0"

I managed to do it, but I have a problem now, when running CQN I can capture the insert update events in a given table as I wanted, but when running a second time it always gives an error. ERROR: Error executing Oracle trigger: Error: internal error in file D:\git_files\node-oracledb\src\njsConnection.c, line 2314 (no error message)

here is a part of the trigger function code for my node on n8n:

async poll(this: IPollFunctions): Promise<INodeExecutionData[][] | null> {
		try {
			const returnItems: INodeExecutionData[] = [];
			const fieldName = this.getNodeParameter('fieldName') as string;
			const tableName = this.getNodeParameter('table') as string;
			const condition = this.getNodeParameter('condition') as string;

			const credentials = await this.getCredentials('oracleSqlApi');
			if (!credentials) {
				throw new NodeOperationError(this.getNode(), 'OracleSqlApi credentials not provided!');
			}

			const pool = await connectToOracle.call(this as any);
			const connection = await pool.getConnection();

			// Variável de controle para verificar se alguma notificação foi recebida
			let notificationReceived = false;

			// Função de callback para processar as notificações
			function myCallback(message: any) {
				console.log('Notificação do Oracle recebeu:', message);
				notificationReceived = true;

				console.log('Nome do banco de dados:', message.dbName);
				console.log('ID da transação:', message.txId);
				for (const table of message.tables) {
					console.log('--> Nome da tabela:', table.name);
					console.log('--> Operação da tabela:', table.operation);
					if (table.rows) {
						for (const row of table.rows) {
							console.log('--> --> Row Rowid:', row.rowid);
							console.log('--> --> Row Operation:', row.operation);
							console.log(Array(61).join('-'));
						}
					}
					console.log(Array(61).join('='));
				}

				// Crie um objeto INodeExecutionData para conter os dados da notificação
				const executionData: INodeExecutionData = {
					json: {
						type: message.type,
						dbName: message.dbName,
						txId: message.txId,
						registered: message.registered,
						tables: message.tables,
					},
				};
				// Adicione 'rowId' ao objeto JSON 'executionData'
				if (message.tables && message.tables.length > 0 && message.tables[0].rows) {
					executionData.json.rowId = message.tables[0].rows[0].rowid;
					executionData.json.tableName = message.tables[0].name;
				}

				returnItems.push(executionData);
			}

			const query = `SELECT ${fieldName} FROM ${tableName} WHERE ${condition}`;

			const options: oracledb.SubscribeOptions = {
				sql: query,
				callback: myCallback,
				timeout: 60,
				qos: oracledb.SUBSCR_QOS_ROWIDS,
				groupingClass: oracledb.SUBSCR_GROUPING_CLASS_TIME,
				groupingValue: 10,
				groupingType: oracledb.SUBSCR_GROUPING_TYPE_SUMMARY,
			};

			await connection.subscribe('mysub', options);
			console.log('Assinatura criada...');

			// Aguarde até que alguma notificação seja recebida
			while (!notificationReceived) {
				await new Promise((resolve) => setTimeout(resolve, 1000)); // Aguarde 1 segundo antes de verificar novamente
			}

			// Após processar as notificações, libere a conexão de volta para o pool
			connection.release();

			// Retorne os dados contidos em 'returnItems'
			return [returnItems];
		} catch (error) {
			throw new NodeOperationError(
				this.getNode(),
				`Erro ao executar o gatilho Oracle: ${(error as Error).message}`,
			);
		}
	}