How do I transfer all records to comma separted text file in UNIX

[babalkp]

Hi,

Iam new to ORACLE. I have worked on SYBASE before. I would like to know the equivalent command of bcp in SYBASE in ORACLE. Well, bcp is bulk copy program. It transfers all records of table to comma separated text file. This will help me to store all my records in UNIX and I can transfer back to my table whenever I wanted.

Please let know what is the command line for oracle for the above?

Regards
Krishna.

[pwoneill]

there is no one command to do it, but it's not hard to do in sqlplus.

just make your select something like

select col1 || ',' || col2 || ',' col3 from table.

and it will spit results like

col1,col2,col3

spool that to a file.

you might also be able to set the sqlplus variable colsep to a comma.

[arshiah]

You can also use EXPORT and IMPORT utilities if you just need to save the data to a file and load it back later on.
These tools are very simple to use too.

AH.

[marist89]

Or, look at [url]http://osi.oracle.com/wa/ask/owa/ask_tom_pkg.display?p_dispid=228213957918[/url] for a neat package/script that does it for you...

[sambavan]

You could write a PLSQL procedure that would use the UTL_FILE package. This would give you more flexibility on how to manuver the data. You could open multiple cursors and then format then to your requirement and flush them on to a flat file.

import/export would only work if you are planning to move the data from oracle to oracle.

The select x ||','||'y'||','
FROM xyz;
would work if you are trying to flush the data from one/multiple tables, but it is possible to get the multiple entries on the same records, if there are some kind of join statments.

On the other hand if you use the UTL_FILE package, and write a procedure, then you could have single record containing all the related informations in one line.

Good luck,
Sam