edit-icon download-icon

Configure Oracle writer

Last Updated: Apr 03, 2018

The Oracle Writer plug-in provides the ability to write data into the target tables of the master Oracle database. At the underlying implementation level, Oracle Writer connects to a remote Oracle database through JDBC, and runs the “insert into…” SQL statement to write data into Oracle. You must configure the data source before configuring the Oracle Writer plug-in. For more information, see Oracle data source config.

Oracle Writer is designed for ETL developers to import data from data warehouses to Oracle.It can also be used as a data migration tool by DBA and other users.

Oracle Writer uses the data synchronization framework to get the protocol data generated by Oracle Reader. Then it connects to a remote Oracle database through JDBC, and runs the “insert into…” SQL statement to write data into Oracle.

Similar to Oracle Reader, Oracle Writer currently supports most data types in Oracle. Check whether your data type is supported.

Oracle Writer converts the data types in Oracle as follows:

Category Oracle data type
Integer NUMBER, RAWID, INTEGER, INT, SMALLINT
Floating point NUMERIC, DECIMAL, FLOAT, DOUBLE PRECISION, REAL
String LONG, CHAR, NCHAR, VARCHAR, VARCHAR2, NVARCHAR2, CLOB, NCLOB, CHARACTER, CHARACTER VARYING, CHAR VARYING, NATIONAL CHARACTER, NATIONAL CHAR, NATIONAL CHARACTER VARYING, NATIONAL CHAR VARYING, NCHAR VARYING
Date and time TIMESTAMP, DATE
Boolean BIT, BOOL
Binary BLOB, BFILE, RAW, LONG RAW

Parameter description

  • dataSource

    • Description: Data source name. It must be identical to the data source name added. Adding data source is supported in script mode.

    • Required: Yes

    • Default value: None
  • table

    • Description: Target table name. If the schema information of table is not consistent with the username in the preceding configuration, enter the table information in the schema.table format.

    • Required: Yes

    • Default value: None

  • column

    • Description: The fields of the target table into which data is required to be written. These fields are separated by commas. For example: “column”: [“id”, “name”, “age”]. Use * if it is required to write data into all columns in sequence. For example: “column”: [“*”]

    • Required: Yes

    • Default value: None
  • preSql

    • Description: The SQL statement that is run before the data synchronization task is run. Currently, you can run only one SQL statement in wizard mode, and more than one SQL statement in script mode. For example, clear old data.

    • Required: No

    • Default value: None
  • postSql

    • Description: The SQL statement that is run after the data synchronization task is run. Currently, you can run only one SQL statement in wizard mode, and more than one SQL statement in script mode. For example, add a timestamp.

    • Required: No

    • Default value: None

  • batchSize

    • Description: The quantity of records submitted in one operation. Setting this parameter can greatly reduce the interactions between CDP and Oracle over the network, and increase the overall throughput. However, an excessively large value may cause the running process of CDP to become out of memory (OOM).

    • Required: No

    • Default value: 1024

Development in wizard mode

TargetOracle

  • Data source: datasource in the preceding parameter description. Select the Oracle data source.

  • Table: table in the preceding parameter description. Select the table to be synchronized.

  • Prepared statement before import: preSql in the preceding parameter description, namely, the SQL statement that is run before the data synchronization task is run.

  • Prepared statement after import: postSql in the preceding parameter description, namely, the SQL statement that is run after the data synchronization task is run.

  • Primary key conflict: writeMode in the preceding parameter description. You can select the expected import mode.

  • Field mapping: column in the preceding parameter description.

Development in script mode

Configure a job to write data into Oracle:

  1. {
  2. "type": "job",
  3. "version": "1.0",
  4. "configuration": {
  5. "reader": {},
  6. "writer": {
  7. "plugin": "Oracle",
  8. "parameter": {
  9. "datasource": "datasourceName",
  10. "table": "table",
  11. "column": [
  12. "id", "name", "age"
  13. ],
  14. "preSql": [
  15. "delete from XXX;"
  16. ]
  17. }
  18. }
  19. }
  20. }
Thank you! We've received your feedback.