topic Re: Export table data to flat file in Operating System - Linux

Export table data to flat file

Gyankr — Thu, 13 Dec 2007 14:57:18 GMT

Hi,
I have an oracle db installed in a unix system. I want the table data to be exported to a flat file.The fields in the flat file need to be "PIPE separated".
Please let me know how to go about using bash shells. I have tried the reverse procedure (sql loaders).

Re: Export table data to flat file

James R. Ferguson — Thu, 13 Dec 2007 15:03:49 GMT

Hi:

Well, if you already have whitespace (spaces and tab) delimited data, simply do:

# perl -nale 'print join "|",@F' file

Regards!

...JRF...

Re: Export table data to flat file

Gyankr — Thu, 13 Dec 2007 15:06:59 GMT

Hi,
Thanks for your reply,that was very fast. I am not so well versed in perl,can you explain this using bash scripting

Re: Export table data to flat file

OldSchool — Thu, 13 Dec 2007 15:08:10 GMT

google turns up many suggestions on how to perform the unload. a couple are here:

http://asktom.oracle.com/tkyte/flat/index.html

http://www.withdata.com/oracmd_unload.html

Re: Export table data to flat file

James R. Ferguson — Thu, 13 Dec 2007 15:11:39 GMT

Hi (again):

Well, in Perl, this reads your input file ('-n') and automatically splits each record into whitespace-delimited fields ('-a') into an array called '@F'. The fields of @F are then printed using the 'join' operator and a pipe delimiter. The '-l' switch adds a newline to each record as it is printed.

Regards!

...JRF...

Re: Export table data to flat file

Volker Borowski — Thu, 13 Dec 2007 15:22:29 GMT

Well,

this might help:
http://www.orafaq.com/faqloadr.htm#UNLOAD

but you may have to substitute

select col1 || ',' || col2 || ',' || col3

with

select col1 || '|' || col2 || '|' || col3

to get a pipe-delimited result.

Regards
Volker

Re: Export table data to flat file

Murat SULUHAN — Thu, 13 Dec 2007 19:46:03 GMT

Hi Raju

Check following url
http://www.oracle-bse.com/articles/9i/GeneratingCSVFiles.php

utl_file is excellent for this issue.

Best Regards
Murat

Re: Export table data to flat file

Gyankr — Mon, 31 Dec 2007 11:53:58 GMT

Hello everybody,
Thanks my is Problem solved -used the utl package in plsql

Thanks,
Gyan

Re: Export table data to flat file

Gyankr — Tue, 01 Jan 2008 06:31:12 GMT

Problem solved