Multiple Oracle databases create an Excel report

Microsoft Excel spreadsheets are widely used in today's business environment. Although Excel can read and display delimited files, the. xls proprietary format provides some other expressive features, including font formatting and multiple worksheet spreadsheets.

Many commercial and open source tools provide the ability to generate reports that can be read by Excel. For example, you can use Oracle Reports to generate a spreadsheet, or use Sql*plus to output a comma-delimited file. Use some of the existing tools to access multiple data sources. However, the format of the report is closely related to the results of a particular query, and different data sources sometimes produce different results. (for example, a SELECT * from EMP query for tables in two different databases, the number and type of columns returned will necessarily vary.) For a variety of reasons, you might want to access several different data sources and then organize the results returned in a spreadsheet:

The database administrator may want to compare the configuration of different databases.

Application developers may need to check the versions of Pl/sql objects in different databases.

When migrating data between databases, the database administrator may need to estimate the progress of the job.

When a programmer uses a test database to debug an application, it may be necessary to compare the current data with the data in the production environment.

Depending on the structure and purpose of the database, there may be other reasons why you want to generate such spreadsheets, but all of the reasons depend on the functional requirements and definitions of the database.

This article explains how to use the Apache Jakarta POI Open source project to generate a spreadsheet from several different data sources. Each worksheet in the workbook displays the results returned from a given Oracle database. The requirements you want to meet in the current project are:

Run the software to create a spreadsheet that contains data obtained from one or more Oracle databases.

The user will define a random SQL query, and then the query is processed by each configured database.

The user will define one or more database connections.

Generates a configuration file that includes the data connections and SQL queries that you define.

The configuration file is in XML format.

A worksheet is generated to show the results returned by each database.

Each worksheet is named based on the server name and the Oracle system identifier (also known as the Oracle SID).

The spreadsheet displays column headings (based on database columns) in bold format.

The software will be written in Java.

The database will be accessed using JDBC.

The spreadsheet will be generated using Jakarta POI.

You can add a lot of requirements to your production system. Clearly, the password encryption aspect is omitted from the list above. Although it is now possible to handle SQL data types, a more robust solution should also be able to handle a number of special data types, such as BLOBs (binary Large Objects), CLOB (character large objects), and LONG. Currently, only one XML configuration file and one script can be invoked to set up the environment and execute Java classes, without providing an easy-to-use graphical interface. Because only one query is executed, you must ensure that the SQL query has no syntax errors and returns a result set based on the objects for each database. And the content of the record is very small. However, if the program meets the requirements above, the program is relatively easy to use, and it can quickly generate documents that contain many database data. If you want, you can also use Excel to edit the format of the document. Because the data from multiple data sources is included in a spreadsheet, we can easily compare the data extracted from each database. In addition, the primary mechanism that you use is the POI application programming Interface (API) calls that are clearly distinguishable and not overwhelmed by the other code and resources needed to build a full-featured solution.