Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
technervs opened this issue Aug 29, 2023 · 2 comments
Labels

Comments

@technervs
Copy link

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
Copy link
Member

cjbj commented Aug 31, 2023

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

@technervs technervs reopened this Oct 11, 2023
@technervs
Copy link
Author

technervs commented Oct 13, 2023

@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}`,
			);
		}
	}

image

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

2 participants