Here you can find the detailed instruction on how to export/convert data from SQLite 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 from SQLite to Oracle, the following conditions are required:
- SQLite3.dll must be accessible.
- Exportizer Pro or Exportizer Enterprise must be used.
- When using Exportizer Pro, the source and target databases must be connected via ODBC (using ADO interface), so make sure both SQLite ODBC driver and Oracle ODBC driver were installed and the corresponding ODBC data sources were created. The drivers must be both either 32-bit or 64-bit.
- When using Exportizer Enterprise, both databases can be connected using FD interface, and there are no ODBC drivers required. Of course, you can use other connection methods available for these types of databases, but note that using FD on the target side can significantly speed up the data exporting.
- All involved components, i.e. Exportizer, ODBC drivers (if you use ODBC on source or target side), OLE DB providers, should have the same architecture: 32-bit or 64-bit.
Notes
- 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.
- You can invoke the ODBC Data Source Administrator directly from Exportizer (in Open Data Source dialog) when it was launched in administrator mode.
SQLite to Oracle Conversion Preparation
If you are exporting data via GUI:
- Launch Exportizer Pro or Exportizer Enterprise.
- Register your source SQLite database:
- In Exportizer Pro, use ADO interface. The database must be an ODBC DSN, pointed to your SQLite database file.
- In Exportizer Enterprise, use FD interface. In most cases, specifying a pointer to the SQLite file is enough. Sometimes, specifying the Vendor library parameter may be needed: this must be a main DLL from the folder where SQLite installed, e.g. sqlite3.dll.
- 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)
- Open the registered SQLite database or drug and drop the source SQLite file onto the Exportizer table list.
- Choose a dataset or datasets to export:
- select a table from the table list;
or - open an SQL window, then write and execute an SQL query;
or - select multiple tables in the table list; before doing that, click Select Tables button
;
or - prepare a mix of multiple tables and/or SQL queries: open multiple tables one by one and multiple SQL windows; in SQL windows, write your database queries and execute them to get the result sets.
Please note that exporting multiple datasets at a time is available in Exportizer Enterprise only.
- select a table from the table list;
- Click Export button
or choose a needed item from Export menu.
- Switch to the Database tab. If this tab is not visible, click Favorite Export Formats button in the top-right corner of the window and make sure the corresponding format is selected.
- Select your registered Oracle database as a target database. If you did not register it yet, click '...' button to the right to register it.
- Specify export parameters. Some notes:
- Turn on the Memory saving mode option and its related options. Turning on all three options gives you the maximum speed. But in case of exporting problems, if you suspect that they can be caused by these options, try different combinations of them. If your source data or column names on any side can contain Unicode characters, set the stream Encoding to UTF-8.
- Specify the 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; anyways, the source-to-target table mappings are available at the next step.
- Oracle supports optional table descriptions (comments), so you can type a description for the target table; if you are exporting multiple datasets, leave this field empty and fill the descriptions for target tables later on the Table Mappings step. Note that descriptions are applied during the table creation only; they are ignored when the export mode (see below) is appending, updating, or deleting records in existing target table.
- Choose the needed Export mode. 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 (starting from a moderate value) to find the optimal value before porting the solution to your production environment.
- Click Next.
For multi-table exporting, specify the source-to-target table mappings, otherwise check the source-to-target field mappings.
In field mappings, you create the correspondence between the source and target columns.
In the source part of the field mappings, instead of the source table fields, you can also use calculated fields specified by formulas. You can also leave the source part empty if the target column is an identity or computed column.
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. Destination identity and computed columns can also be defined if the target Oracle version supports that. These specifications will be applied when the target table needs to be created or overwritten, i.e. when the corresponding export mode is used.
In table mappings (for multi-dataset exporting), you create the correspondence between the source datasets (tables and/or queries) and target tables. Here, you can specify target table names, their descriptions (comments), and nested field mappings for each table pair.
- Click Next to proceed to export logs and error handling options or click Export to start the export process immediately.
Resolving Export Performance Issues
In the case of the slow performance, there are some actions you can try:
- First of all, make sure the target database is connected via FD interface. Using FD on the target side lets you utilize batch SQL commands execution which can speed up the exporting process drastically.
- Try different combinations of Memory saving mode options and Commit interval setting. It is recommended to turn on the Memory saving mode, Use SQL parameters and Batch mode options and set the value of Commit interval from several hundreds to several thousands depending on you target server resources.
- Finally, you might want to read all the data exporting recommendations.
Exporting SQLite to Oracle from Command Line
Command Line Example
Here is how to convert data from several SQLite tables to Oracle in silent mode:
exptizer.exe /silent /export /ExportType=DATABASE /ExportMode=REPLACE+INSERT /IncludeMemo /CommitInterval=500 /MemorySaving /UseSQLParameters /UseBatchMode /Encoding=UTF-8 /SrcDBInterface=FD /SrcDBKind=FILE /SrcDBDriver=SQLite /SrcDB=C:\TEST\employees.db /TrgDBInterface=FD /TrgDBKind=DSN /TrgDBDriver=Oracle /TrgOSAuthentication=Yes /TrgAuthenticationMode=Normal /TrgCharset=UTF8 /TrgVendorHomepath=C:\app\John\product\12.1.0\client_1 /TrgDB=cloud_prod "/TableMappingsFile=c:\DWH\Export\SQLite-To-Oracle-Table-Mappings.xml" /LogFile=C:\DWH\Export\Log\export.log /AppendLog
The table mappings file (/TableMappingsFile parameter) must be prepared before the exporting. It must contain the source-to-target table mappings. It can be prepared in GUI Export dialog or manually using the documentation.
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
/MemorySaving
/UseSQLParameters
/UseBatchMode
/Encoding=UTF-8
/CommitInterval=500 ;COMMIT after exporting every 500 records
/SrcDBInterface=FD
/SrcDBKind=FILE
/SrcDBDriver=SQLITE
/SrcDB=C:\TEST\employees.db
/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\SQLite-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