Shulou(Shulou.com)06/03 Report--

Development of batch data Export tool for Oracle Database

Demand

Recently, I encountered a demand in my work, and users frequently asked for bulk export of a large amount of data from the background. At present, the export method is to use the PL/SQL Developer tool to connect to the Oracle database, write SQL statements, and use the function of exporting Excel files included in the PL/SQL Developer tool for export.

Write SQL query results

two。 Select Export Excel File

When you choose to export Excel files, you can choose to export XLS and XLSX files.

Problems encountered:

The export fails when the user requires that the number of pieces of data exported is too large, exceeding the maximum number of rows that can be stored in XLS or XLSX files. In order to solve this problem, an Oracle data export tool is specially developed to support the export of large quantities of data.

The maximum number of lines in a XLS file is: 65536

The maximum number of lines for a XLSX file is: 1048576

Basic conception

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

There are two implementation scenarios:

Use the POI language to connect to the database, use the POI library to write Excel files, develop desktop programs using the Cpicard + language, and use other libraries to write Excel files.

Selection process:

Considering that the tool will be used by other colleagues within the company, and that you don't know much about the interface of Java, we abandon this option 1 and adopt option 2. After selecting option 2, proceed to select the libraries and related technologies that can be used.

About the choice of storing files

Write Excel files directly: when writing Excel files, use the LibXl library, when the amount of data is relatively large, there will be a failure to save the disk. In this case, you can only reduce the number of lines written to Excel files. If the query result has many fields and the length is long, this scheme can save fewer pieces of data.

Write the Txt file:

Write the Csv file:

Writing Txt files is similar to writing Csv files. The difference is that "\ t" is used to separate different fields when writing Txt files, while "," (comma) is used to separate fields when writing Csv format.

In addition, when writing files in Csv and Txt formats, because the user can open it through Excel, if it is a number and starts with 0, you need to add a 'sign to identify this field as text.

Other content

Select Duilib UI library, not MFC

Select OCILib to access and manipulate the database (you want to be able to write notes on how to use the library)

Write Excel files using the LibXl library

Operation flow

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.