Here you can find the detailed instruction on how to export data from any database to Oracle database in Exportizer software.

Below, it is shown how to export data from GUI or command line.

Export Conditions

In most cases, to export data to Oracle, it is important to fulfill the following conditions:

  • We recommend to use Exportizer Enterprise and FD interface when working with Oracle databases. FD engine does not require ODBC drivers and exports data much faster. But if you prefer to use ODBC for both (source and target) databases, Exportizer Pro can be used.
  • Oracle client must be installed. Make sure the Exportizer and Oracle client have the same architecture, i.e. either all are 32-bit or all are 64-bit.
  • All other involved components, i.e. Exportizer, ODBC drivers (if you use ODBC on source or target side), OLE DB providers etc., should have the same architecture: 32-bit or 64-bit.

Note: If your operating system is 64-bit, you can install both 32-bit and 64-bit versions of the Exportizer software and use them independently.

Export to Oracle Preparations

If you are exporting data via GUI:

  1. Launch Exportizer Pro or Exportizer Enterprise.
  2. Register your source database.
  3. Register your target Oracle database. You can register it by several ways. Note: You can register the target database from the Export dialog during the exporting.

Export Steps (GUI)

  1. Open the registered source database.
  2. Choose a table or tables to export or write and execute your SQL query or queries. Please note that exporting multiple datasets (tables and/or queries) at a time is available in Exportizer Enterprise only.
  3. Click Export button or choose a needed item from Export menu.
  4. Switch to the Database tab and select your registered Oracle database as a target database. If you did not register it yet, click '...' button to the right to register it.
  5. Specify export parameters. Some notes:
    • Turn on the Memory saving mode option and its related options. In case of exporting problems, if you suspect that they can be caused by these options, try different combinations of them. If exported data or column names on any side can contain Unicode characters, set the corresponding stream Encoding, e.g. UTF-8.
    • Specify a Table name, i.e. name of the target table. For multi-table exporting, you can leave it empty, otherwise, the data will be exported to one destination table.
    • Optionally, specify the target table description (comment). For multi-table exporting, leave it empty; you will able to specify the target descriptions later on the Table Mappings step.
    • Choose the Export mode. Some modes allow you to update data in existing table(s), some modes can create destination tables, etc. To learn about all possible modes, read detailed description for Database export format. For multi-table exporting, this mode will be applied to the most of the tables, and you can override it for specific tables at the next step. For example, it can be Replace+Insert for the most of tables, and Update or Append+Update for others etc.
    • The bigger Commit interval value, the faster your exporting process. But too big value may cause memory and other issues. Therefore, try to play with it to find the optimal value before porting the solution to your production environment.
    Exporting Data to Oracle Database
  6. Click Next.

    For multi-table exporting, specify the source-to-target table mappings, otherwise check the source-to-target field mappings.

    In field or table mappings, you create the correspondence between the source objects (tables and/or fields) and target objects.

    In field mappings, in addition to source table fields, you can also use calculated fields specified by formulas.

    It is possible to define full specifications for target columns here, i.e. rename target columns, choose their types, specify NOT NULL constraints, default values, primary key flags, descriptions (comments) etc. These specifications will be applied when the target table needs to be created or overwritten.

    You can skip this step by clicking Export instead of clicking Next. In this case table and/or field mappings will be built automatically, which may be not desirable.

    Customizing Target Oracle Table
  7. Click Next to proceed to export logs and error handling options or click Export to start the export process immediately.

In case of performance problems: try different combinations of Memory saving mode options and Commit interval option; you might want to read all the data exporting recommendations.

Exporting Data to Oracle from Command Line

When you export data from the command line, you do not need to register the source and target databases (but you still can). Please note that using registered databases allows you to shorten the command line and to hide the database passwords; another option to hide the passwords is to use OS authentication (see example below).

In examples below, we do not use registered databases. Parameter /TrgDB is the name of your destination Oracle database from the tnsnames.ora file.

Command Line Examples

Exporting a DBF table to Oracle using OS authentication:

"C:\Program Files (x86)\Vitalii Levchenko\Exportizer Enterprise 9\exptizer.exe" /export /ExportType=DATABASE /ExportMode=REPLACE+INSERT /FieldMappings=ID=ID;CUSTID=CUSTOMER_ID;SALEDATE=SALE_DATE;TOTALSUM=TOTAL_SUM;TAXRATE=TAX_RATE;INVOICE=INVOICE /TrgTableName=ORDERS /TrgDBInterface=fd /TrgDBKind=DSN /TrgDBDriver=Oracle /CommitInterval=1000 /MemorySaving /UseSQLParameters /UseBatchMode /TrgOSAuthentication=Yes /TrgAuthenticationMode=Normal /TrgVendorHomepath=C:\app\John\product\12.1.0\client_1 /TrimTrailingSpaces /SrcDBInterface=ado /SrcDBKind=DSN /SrcDBDriver=DBASE /SrcDB=rptizer /SrcTableName=orders.dbf /TrgDB=cloud_prod

Exporting multiple PostgreSQL tables to Oracle in silent mode:

exptizer.exe /silent /export /ExportType=DATABASE /ExportMode=REPLACE+INSERT /CommitInterval=400 /SrcDBInterface=FD /SrcDBKind=DSN /SrcDBDriver=POSTGRESQL "/SrcServer=222.333.2.14" /SrcPort=5432 /SrcDB=dwh /SrcDBUserName=dwh_master /SrcDBPassword=dwh_master_password /TrgDBInterface=FD /TrgDBKind=DSN /TrgDBDriver=ORACLE /TrgOSAuthentication=Yes /TrgAuthenticationMode=Normal /TrgVendorHomepath=C:\app\John\product\12.1.0\client_1 /TrgDB=cloud_prod "/TableMappingsFile=c:\DWH\Export\PostgreSQL-To-Oracle-Table-Mappings.xml" /LogFile=C:\DWH\Export\Log\export.log /AppendLog

Note: You can add other parameters to the command lines above in accordance with the documentation.

Action File Example

The command line above can be transformed to an action file, which is much more comfortable to work with. You can run action files either form the command line or from Exportizer GUI. The action file contains all the command line parameters (except /silent switch), one parameter per line, and allows comments:

/export 
/ExportType=DATABASE 
/ExportMode=REPLACE+INSERT ;Target tables recreated when exist
;/ExportMode=APPEND ;Incoming records are appended to target tables
/CommitInterval=400 ;COMMIT after exporting every 400 records
/SrcDBInterface=FD 
/SrcDBKind=DSN 
/SrcDBDriver=POSTGRESQL 
/SrcServer=222.333.2.14 ;IP address or server URL
/SrcPort=5432 
/SrcDB=dwh 
/SrcDBUserName=dwh_master 
/SrcDBPassword=dwh_master_password 
/TrgDBInterface=FD 
/TrgDBKind=DSN 
/TrgDBDriver=ORACLE 
/TrgOSAuthentication=Yes 
/TrgAuthenticationMode=Normal 
/TrgVendorHomepath=C:\app\John\product\12.1.0\client_1 
/TrgDB=cloud_prod ;a name from tnsnames.ora file 
/TableMappingsFile=c:\DWH\Export\PostgreSQL-To-Oracle-Table-Mappings.xml ;a file with table source-to-target table mappings 
/LogFile=C:\DWH\Export\Log\export.log ;a file for writing logs to 
/AppendLog ;keep previous logs and add new logs to the end of the log file

See also

Ways of Opening Different Data Source Types

Opening Oracle Databases

Command Line Usage

Table and Field Mappings

Export Logs and Error Handling

Practical Cases

  • Exporting Data to Oracle