SQL*XL - Preview
Please note: Screen prints in this section are made using an older version of SQL*XL:
SQL*XL creates a new menu
item in Excel. To just try SQL*XL you can open it in Excel the same way as you would open a spreadsheet (drag
and drop also works from the file manager). Alternatively install SQL*XL using the Tools-Addins... menu item. SQL*XL
does not hack around in your Excel to create the new menu item. Unloading SQL*XL will discard the new menu item
Everything is SQL*XL is done through the new Oracle menu item in Excel as can be seen in the screen prints below.
Apart from the screen prints I have prepared a few animated images to show you how certain advanced features
In the remainder of this page I will show some of the dialog screens that can be invoked from the new Oracle
menu in Excel.
The Oracle login screen is started by choosing the menu item Oracle-Connect. After the connection to the database
has been established the other menu items will be enabled. The title of the main Excel window will be updated to
show which database you are connected to.
The main feature is of course the query feature. Type the SQL in the query dialog as shown below and SQL*XL
will fetch the data straight into your spreadsheet. The dialog takes SQL up to 255 characters. The commercial full
version of SQL*XL enables you to assemble the SQL in the spreadsheet. You can use (and re-use) very large
SQL statements this way. Simply select all cells containing the SQL and away you go !
Options on the dialog let you:
-Use SQL from selected cells in your spreadsheet
-Print column headings with the data.
-Transpose Oracle rows to Excel columns. Ideal to nicely view a table with say more than 10 columns.
-AutoFit the column width according to the the data pasted into your sheet.
-Sort the fetched results according to the data in the first column
If you cannot remember the exact spelling of the tables, don't worry. The Schema.Table selection dialog lets you
type the tablename if you know it by heart. Otherwise you can retrieve a the list of tables or views owned by the
schema in the listbox in the upper half of the screen. In fact, this dialog is used everywhere where you need to
select a table/view.
SQL*XL even makes inserts and updates easier. It prepares forms in which you can type the details. Below is
an example of the INSERT screen. The UPDATE is done in a very similar way. This handy feature I actually don't
give away in the free lite version. Please order a full copy in order to use these features. The lite version includes
these features as demos.
The Describe Table/View dialog is displayed when the menu item Oracle-Describe is choosen. Here you can look
at the column definitions of the selected table. With the query button the whole table is fetched into your excel
To execute a PLSQL block or to execute a non select SQL statement you can use the Execute PL/SQL dialog. It
is started using the Oracle-PLSQL item. Here you can execute the commands not catered for in the other dialogs.
Again the PL/SQL is limited to 255 characters in this dialog. In the full version you can use the option "Use
Selected cells as SQL statement". This allows you to assemble (large) PL/SQL statements in your spreadsheet,
just like the way it works with SQL statements in the query dialog.