Ad Hoc Reporting

I had written a script to restore some data that had been lost. A team convened to run my script. The thing completed very quickly. After some spot checks, we determined my script was a success. The DBAs left the call shortly after that. There was one last task left. My boss wanted me to write some SQL to produce a comma separated file with the data.

That sounded simple enough. SPOOL the data out to a file. The main command was a big SELECT statement which included all the column values separated by commas. I gave it a try in development and found the lines were wrapping. So I SET LINESIZE to 200. We run the job in the production environment.

Ooops. The headers are showing up on every page. Should have SET HEADING OFF. Then we import the data into Microsoft Excel. Some rows are not lining up correctly. The LINESIZE needed to be bigger. Maybe set it to 2000? Is that too high to work? Also some of the data had commas within the values. Oh man this is getting tricky.

In the end I resorted to a tool like PL/SQL Developer to do the trick. It can export data with ease.