Re: How do I export Oracle table data to a csv file

reji.mathews_at_capita.co.uk wrote:
> I am trying to export Oracle Data to csv file but it fails to export
> some of the data.
> Following is the syntax I am using:
>
> SET ECHO OFF HEADING OFF NEWPAGE 0 FEEDBACK OFF LINESIZE 10000
> PAGESIZE 0 WRA OFF RECSEP OFF CON OFF TRIM ON TRIMS ON VERIFY off
> doc off
>
> --FAQ.bat--
> sqlplus -s userid/password_at_hoststring @faq.sql > faq.csv
>
> --FAQ.sql--
> select
> '"'||FAQ_NUMBER ||'","'||FAQ_CATEGORY||'","'||QUESTION||'","'||ANSWER||'","'||RELATED_LINK||'","'||AUDIENCE||'","'||FAQ_VALID
> from FAQ_INFOBASE
> order by FAQ_NUMBER;
>
> exit
>
>
> The column ANSWER has most of the special characters. Please Please
> help me out
>
>
> --

Ok, before anything, be careful when using sqlplus -s userid/password_at_hoststring
If you're running this on a Unix machine, anyone will be able to see your password using "ps". When I need to use sqlplus in a script I always call it with the /NOLOG option and put the connect data inside the script. Now on to your question:

If this is a regular job that you'll have to run, I recommend using Perl. It's fast, free, multi-platform and there are several examples online (search google for "Perl DBI"). If you don't like this idea, check Microsoft's Windows Scripting Host. It allows you to create Basic scripts for Windows connecting to Oracle via ODBC. You can even load data directly into Excel.

If this is a one-time thing however, Toad (http://www.toadsoft.com) is also good enough. There's a freeware version available in their website (time-limited, I guess).

Hope this helps.

Regards.

Wanderley Received on Sat Jan 11 2003 - 13:09:09 CST