ETL PostgreSQL in Oracle ODI 12c

This article describes how to synchronize data to Oracle from PostgreSQL via ODI.

1. Define the physical architecture

1.1 Creating a new PostgreSQL data server
Topology->physical Architecture->postgresql, right-click to select New Data Server and enter the relevant information in the Definition dialog box, such as:

In the JDBC dialog box, enter the following information, such as:

Click Save. Click "Test Connection" to verify.
1.2 Creating a physical scheme for PostgreSQL
Topology->physical architecture->postgresql-> "Postgresql_rhndb", enter the name and database in the Definition dialog box, click Save.

Note here that the database chooses Rhndb,schema must choose public. Finally click "Save".

2. Define Logical architecture

Topology->logical Architecture->postgresql, right click to select "New Logical Schema", select the corresponding physical scheme in the Definition dialog box, click Save.

3. Define the PostgreSQL model

Designer->models->new model, as shown in:

Click "Save". Then click on "Reverse Engineer" to synchronize the table structure of PostgreSQL.
A copy of the Oracle database table structure has been previously synchronized and is used directly here. If not, you'll need to follow the steps above to create it.

4. Create a project

Designer->projects->new Project, enter a name and click Save.

5. Create a map

Designer->projects->postgresql_to_oracle->mapping, right-click to select "New Mapping", then drag the source and target tables to link, such as:

The knowledge module here selects "LKM SQL to SQL (built-in). GLOBAL "as shown in:

Attention:
A, if the field type encountered with PostgreSQL does not match the field type of Oracle, edit the field type of the source and target tables in model.
B, in the logic options in the map, set the target table's integration type to incremental update (append by default), such as:

C, don't forget to click "Save".

6. Verify the mapping

Click on the icon shown in the diagram to verify the error, if the errors are modified according to the actual situation.

ETL PostgreSQL in Oracle ODI 12c