07 April 2008

Tab delimited ouptut from Oracle/SQLPlus

OK - It's been a looong tim since I made a posting. Sorry to anyone (is there anyone who reads this?) who might checks this BLOG for updates. I promise posts will get more frequent.

Now, to the heart of this topic. I had a situation where I NEEDED to get a copy of a table from an Oracle9 system into my SQL Server 2005 datamart. The extract in my SSIS package was working fine until our IT infrastructure team decided to monkey with the firewalls between offices. They had all the best intentions, but their 'monkeying' wasn't planned for completion until 6 months from now. Not a good situation considering I needed a copy of the Oracle table EVERY NIGHT!

Without the correct firewall access for the OLE DB Extract directly into a SQL Server table I resorted to the old school method of executing a SQL query in the command tool SQLPlus. It should have been simple, but I wanted a TAB DELIMITED output and the SET commands for SQLPlus didn't seem to have any setting for tab delimitation.

I did find an obscure posting from 2002 (wow, that's old) which showed hardcoding characters inbetween the column specifications of the SQL query using commas. I thought, "hey maybe it'll work the same if I substitute the TAB character instead of the comma?" Well, sure enough it worked. Here's a snippet of the SQLPlus code. I cut out a lot of the column names because there were 100+ column references. Note the output is spooled to a text file which I then FTP to my SQL Server Box and import the text file into SQl server. The character inbetween the single quotes in the query ('') is a TAB character. Crude - BUT IT WORKS!

SET LINESIZE 8000
set trimspool on
SET FEEDBACK OFF
SET TERMOUT OFF
SET HEAD OFF
SET SPACE 1
SET PAGES 0

SPOOL TabdelimitOuput.txt

SELECT
JOBNUMBER
' ' ENTRYNUMBER
' ' THEJOURNALNUMBER
' ' JOURNALLINENUMBER
' ' POSTINGDATE
' 'VOUCHERNUMBER
' 'DATEOFENTRY
' 'ACTIVITYNUMBER
' 'ACTIVITYTYPE
' 'TEXT
' 'EMPLOYEENUMBER
' 'NUMBERHOURSREGISTERE
' 'NUMBEROFHOURSINVOICE
' 'CURRENCY
' 'COSTPRICE
' 'COSTPRICETOTAL
FROM WIRE.JOBENTRY;

SPOOL OFF
run
exit