SQL*XL - Customer Case Study


Lawrence Livermore National Laboratory (LLNL)


A U.S. Department of Energy national laboratory operated by the University of California.




Requirements to meet

We use SQL*XL to extract information from the database into spreadsheet form for easy viewing of data. We also use it to upload new or modified data

Issues to tackle

1. When modifying existing data, we are required to perform a query first. This sometimes is a hindrance primarily because of the differences in Oracle and Excel sorting. For instance: I run a query with an order by statement to produce a spreadsheet sorted alphabetically. I then proceed to make updates to it and/or resort it. If I finally resort it (in Excel) using the same fields as my original order by clause, the results are sorted differently that what the original query produced.

2. Sometimes we want to insert Excel functions into the spreadsheet that contains the results of a query. Most often, the functions will not work properly. We must copy the cells produced from the query onto a new worksheet and then insert the functions.

3. When you are connected to one database, you can not query a second one. For example select * from table_name@remote_database produces a client database did not begin a transaction error.

Approach taken

1. Run the query first and directly edit the resulting spreadsheet. When the update source is contained in a separate spreadsheet, I manually confirm the spreadsheets are sorted identically and then copy/paste the update source onto the resulting query spreadsheet.

2. see issues

3. Log out of current database and onto remote_database to perform the query


Generally get the information the way we want it much quicker than spooling queries and creating tab delimited files