The xls4OracleSQL tool

The xls4Oracle SQL function will generate an excel worksheet, formatted and ready for you to work with. Data is fetched very fast, using buffer technology.

sql - 10During the fetch, you wil see the progressbar




sql - 13Retrieving all columns combined with the standard Excels features (like filter and sort). This enables you to write “select * from” any table, even tables with many columns, like the mtl_system_items or big views with customer informations.

The “Hide Empty” feature will hide all columns without any data, and only show columns with data. Many tables has unused columns without any data in them. The Hide Empty is default set to Yes


sql - 14After an extract, you can use the xls4Oracle sql format function to hide/unhide columns. The format function shows a list of all columns with column name, column type, number of rows data (emptyness!) and sequence number. The format function has a function to show/hide column types (ID,WHO, DFF, KFF, DATE…). You can sort the list by name, sequence, column type and empty. The empty column show empty or how many rows with data.

The xls4Oracle / SQL email function, enables you to email just the worksheet with extracted data.

sql - 5The worksheet with extracted data has a comment in the A1 cell. The comment shows all information about the extract and enables you to reexecute the exact same extract.

Execute multiple SQL statements: enter the SQL statemenst to be executed and enter “1” in the execute columns and press the xls4Oracle SQL button to execute all the sql statemants.

Use paramteres: enter an extra line above the sql line, give it a name, enter a value and in the sql statement use formula like: <=”and name like ‘”&B9&”‘”>

To do an apps initialize: enter “1” in the logon field. When sql is executed, you will be prompted for user name and password – then you can select a responsibility. The logon field will be updated with the apps initialize code.

Extract versions: prefix the sheet name with a dollar sign, and new sheets will be generated with a version extension.

3 ways to execute the sql function:

  1. From the sql sheet – mark sql(s) to be executed and press the sql button
  2. From an extract sheet – using the A1 comment info – press the sql button
  3. From any cell with a valid sql comment – press the sql button