Configure Oracle Writer - Data Integration| Alibaba Cloud Documentation Center

This topic describes the data types and parameters that Oracle Writer supports and how to configure it by using the codeless user interface (UI) and code editor.

Oracle Writer allows you to write data to tables stored in primary Oracle databases. Specifically, Oracle Writer connects to a remote Oracle database by using Java Database Connectivity (JDBC), and executes an INSERT INTO statement to write data to the Oracle database.

Note

You must configure a connection before you configure Oracle Writer.
Oracle Writer uses the ojdbc6-12.1.1.jar driver. For more information about the supported versions of Oracle JDBC drivers, see Oracle JDBC FAQ.

Oracle Writer is designed for extract, transform, load (ETL) developers to import data from data warehouses to Oracle databases. Users such as database administrators can use Oracle Writer as a data migration tool.

Oracle Writer obtains data from a Data Integration reader, connects to a remote Oracle database by using JDBC, and then executes an SQL statement to write data to the Oracle database.

Data types

Oracle Writer supports most Oracle data types. Make sure that your data types are supported.

The following table describes the data types that Oracle Writer supports.


Category	Oracle data type
Integer	NUMBER, ROWID, INTEGER, INT, and SMALLINT
Floating point	NUMERIC, DECIMAL, FLOAT, DOUBLE PRECISION, and 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, and NCHAR VARYING
Date and time	TIMESTAMP and DATE
Boolean	BIT and BOOLEAN
Binary	BLOB, BFILE, RAW, and LONG RAW

Parameters


Parameter	Description	Required	Default value
datasource	The connection name. It must be the same as the name of the added connection. You can add connections in the code editor.	Yes	N/A
table	The name of the destination table.	Yes	N/A
writeMode	The write mode. Valid value: insert into. If a primary key conflict or unique index conflict occurs, data cannot be written to the conflicting rows and is regarded as dirty data.	No	insert into
column	The columns in the destination table to which data is written. Separate the columns with commas (,), for example, `"column":["id","name","age"]`. To write data to all the columns in the destination table, set the value to an asterisk (), for example, `"column":[""]`.	Yes	N/A
preSql	The SQL statement to execute before the sync node is run. For example, you can clear outdated data before data synchronization. You can execute only one SQL statement on the codeless UI and multiple SQL statements in the code editor.	No	N/A
postSql	The SQL statement to execute after the sync node is run. For example, you can add a timestamp after data synchronization. You can execute only one SQL statement on the codeless UI and multiple SQL statements in the code editor.	No	N/A
batchSize	The number of data records to write at a time. Set this parameter to a proper value based on your actual needs. This can greatly reduce the interactions between Data Integration and the Oracle database on the network, and increase the throughput. However, an excessively large value may lead to the out of memory (OOM) error during the data synchronization process.	No	1,024

Configure Oracle Writer by using the codeless UI

Configure the connections.

Configure the connections to the source and destination data stores for the sync node. Select data source section


Parameter	Description
Data source	The datasource parameter in the preceding parameter description. Select a connection type and select the name of a connection that you have configured in DataWorks.
Table	The table parameter in the preceding parameter description.
Prepare statement before import	The preSql parameter in the preceding parameter description. Enter an SQL statement to execute before the sync node is run.
Complete statement after import	The postSql parameter in the preceding parameter description. Enter an SQL statement to execute after the sync node is run.
Primary key conflict	The writeMode parameter in the preceding parameter description. Select the required write mode.

Configure field mapping. It is equivalent to setting the column parameter in the preceding parameter description. Fields in the source table on the left have a one-to-one mapping with fields in the destination table on the right. Field Mapping section


GUI element	Description
The same name mapping	Click The same name mapping to establish a mapping between fields with the same name. Note that the data types of the fields must match.
Peer mapping	Click Peer mapping to establish a mapping between fields in the same row. Note that the data types of the fields must match.
Unmap	Click Unmap to remove mappings that have been established.
Automatic typesetting	Click Automatic typesetting to sort the fields based on specified rules.

Configure channel control policies. Channel control section


Parameter	Description
Maximum number of concurrent tasks expected	The maximum number of concurrent threads that the sync node uses to read data from or write data to data stores. You can configure the concurrency for the node on the codeless UI.
Synchronization rate	Specifies whether to enable bandwidth throttling. You can enable bandwidth throttling and set a maximum transmission rate to avoid heavy read workload of the source. We recommend that you enable bandwidth throttling and set the maximum transmission rate to a proper value.
The number of error records exceeds	The maximum number of dirty data records allowed.

Configure Oracle Writer by using the code editor

The following example shows how to configure a sync node to write data to an Oracle database. For more information, see Create a sync node by using the code editor.

Notice Delete the comments from the following code before you run the code.

{
    "type":"job",
    "version":"2.0",// The version number.
    "steps":[
        { 
            "stepType":"stream",
            "parameter":{},
            "name":"Reader",
            "category":"reader"
        },
        {
            "stepType":"oracle",// The writer type.
            "parameter":{
                "postSql":[],// The SQL statement to execute after the sync node is run.
                "datasource":"",
                "session": [],// The settings of the session to the database.
                "column":[// The columns to which data is written.
                    "id",
                    "name"
                ],
                "encoding":"UTF-8",// The encoding format.
                "batchSize":1024,// The number of data records to write at a time.
                "table":"", // The name of the destination table.
                "preSql":[]// The SQL statement to execute before the sync node is run.
            },
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{
            "record":"0"// The maximum number of dirty data records allowed.
        },
        "speed":{
            "throttle":false,// Specifies whether to enable bandwidth throttling. A value of false indicates that the bandwidth is not throttled. A value of true indicates that the bandwidth is throttled. The maximum transmission rate takes effect only if you set this parameter to true.
            "concurrent":1,// The maximum number of concurrent threads.
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}