Oracle Database Bulk Data Export tool development

Oracle Database Bulk Data export tool development

Demand

A requirement has been encountered in recent work, and users frequently require large volumes of data to be exported from the background. The current way to export is to use the PL/SQL developer tool to connect to an Oracle database, write a statement, and export it using the features of the PL/SQL Developer tool to export Excel files.

1. Writing SQL query results

2. Select Export Excel File

When you choose to export an Excel file, you can choose to export the XLS, xlsx two format files.

Problems encountered:
The export fails when the user requires that the number of exported data bars is too large to exceed the maximum number of rows that XLS or xlsx two files can store. To address this issue, a specific development of the Oracle Data Export tool is used to support the export of large volumes of data.

The maximum number of XLS file lines is: 65536
The maximum number of rows for an xlsx file is: 1048576

Basic ideas

Use any programming language to connect to the database and execute SQL to write the query results to an Excel file, respectively.

There are two implementation scenarios:

1. Use the Java language to connect to a database, write an Excel file using the POI Library
2. Develop desktop programs using the C + + language and write Excel files using other libraries

Selection process:
Consider that the tool will provide other colleagues in the company to use, in addition to the Java interface is not very understanding, so abandon this scenario 1, using Scenario 2. After you have selected Use Scenario 2, proceed to select the libraries and related technologies that you can use.

About choosing a file to store
Directly write Excel file: When writing Excel file, use LIBXL Library, when the amount of data is large, there will be a case of disk failure, in this case can only reduce the number of lines written to Excel file. If there are many fields in the query result, and the length is very long, this scheme can save fewer data bars.

Write TXT file:
To write a CSV file:
Writing a TXT file is similar to writing a CSV file. The difference is that when you write a TXT file, you use "\ t" to separate different fields, and when you write the CSV format, you use "," (comma) to separate them.
In addition, when writing files in CSV and TXT format, because the user can open through Excel, if it is a number, and starts with 0, you need to add ' number to identify this field as text.

Other content
Select Duilib UI Library, MFC not selected
Select Ocilib to access and manipulate the database (you may want to write a note about how the library is used)
Write an Excel file using the LIBXL library

Operation Flow

Oracle Database Bulk Data Export tool development