Custom converter for oracle

[Anssi Heiska]

Hi

I have a oracle database which number type-usage is bit all over the place. I however know that number stored to db fit to long, int, double or float. My consumer has a bit hard time dealing with precise number mode, so I tried to make a plugin that looks at number-type and produces either int, long, float or double.

I took kafka and zookeeper docker-compose from psql example and they work great. For oracle I tried using:

docker run --name oracle_db --rm -it -p 1521:1521 -p 5500:5500 -e ORACLE_PWD=foo --network debezium-integer-converter_default container-registry.oracle.com/database/express:21.3.0-xe

And after the database is up, I ran:

docker exec -u 0 -it 9588655469ed /bin/bash
# in bash
su oracle
sqlplus / as sysdba
# in sqlplus
archive log list;
shutdown immediate;
startup mount
alter database archivelog;
alter database open;
archive log list;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

// And create a rather wide table, with various NUMBER-fields using external DB-tool.

I also made a custom image based on quay.io/debezium/connect:2.4.0.Final that has my plugin jar and oracle driver in /kafka/connect/debezium-connector-oracle directory.

This seems to work. But when i post debezium config to connector I can only see debug-log from configure()-method. There is log also at the beginning of converterFor(), but it is never called.

My debezium config is:

{
  "name": "converter-test",
  "config": {
    "connector.class": "io.debezium.connector.oracle.OracleConnector",
    "topic.prefix": "test.prefix",
    "database.user": "system",
    "database.password": "foo",
    "database.dbname": "XE",
    "database.port": "1521",
    "database.hostname": "oracle_db",
    "schema.history.internal.kafka.bootstrap.servers": "kafka:9092",
    "schema.history.internal.kafka.topic": "schema-changes.system",
    "table.include.list": "SYSTEM.NUMBERTEST",
    "converters": "number",
    "number.type": "com.test.debezium.DebeziumNumberConverter"
  }
}

I can see debezium starting hitting my plugins configure-method and performing snapshot, but in logs there are lines like:

2023-12-11 11:11:37,015 INFO   Oracle|test.prefix|snapshot  Snapshot step 3 - Locking captured tables []   [io.debezium.relational.RelationalSnapshotChangeEventSource]

So the list of captured tables is empty.

Am I just being defeated by XE here? I know that xe is not supported, but I was hopeful that I could test converter schema generation even if actual capturing would not work.

[Chris Cranford]

Hi,

No the issue in this case isn't XE, it's the fact you've placed the table in the SYSTEM schema, which is a built-in excluded schema [1] [2].  Please create your table in a separate schema and it should be picked up.

Thanks,
Chris

[1]: https://github.com/debezium/debezium/blob/425543d1e017e93baf955e047ce705568fb95a1d/debezium-connector-oracle/src/main/java/io/debezium/connector/oracle/OracleConnectorConfig.java#L675-L677
[2]: https://debezium.io/documentation/reference/2.5/connectors/oracle.html#schemas-that-the-debezium-oracle-connector-excludes-when-capturing-change-events

--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/a7f8c0f3-8fb0-4a39-b6a0-a24671d68952n%40googlegroups.com.

[Anssi Heiska]

Hi

Thanks, somehow I missed that one.

Got this to work. This script (https://github.com/debezium/oracle-vagrant-box/blob/main/setup-logminer.sh) was very useful when configuring oracle container.