Unload Oracle data to flat file (CSV, TSV, TXT) in command line (Windows / Linux / MacOS)

parameters:

delimiter: delimiter char ("|",",","#","TAB","WHITESPACE")

quote: quote char (quote=' => single quotes, quote=" => double quotes)

recorddelimiter: record delimiter (CRLF|LFCR|LF), default is CRLF

header: y|n, print field name at first line

table: table name ("all" for unload all table)

query: sql query string (can not use multi-line sql)

sqlfile: sql script file contains the query you want to unload

schema: schema name, use this prameter to unload specific schema's data, if not set, use logon user's default schema

datafile: data file name

folder: data files folder (for unload all table)

filetype: txt|tsv|csv

dateformat: date format (e.g. "yyyy-mm-dd hh24:mi:ss")

timestampformat: timestamp format (e.g. "yyyy-mm-dd-hh24.mi.ss.ff6")

quit = y|n (quit ora-cmd after run in batch mode)


Unload table example

ora-cmd> unload table=jobs datafile=e:\temp\tables\jobs.txt delimiter="," header=y;


Unload query example

ora-cmd> unload query="select job_id,job_title from jobs"
     >  datafile=e:\temp\tables\jobs_part.txt
     >  filetype=txt
     >  delimiter=","
     >  header=y;


Unload as sqlfile example

sqlfile content: unload_jobs.sql

	select job_id,job_title from jobs

ora-cmd> unload sqlfile=e:\temp\tables\unload_jobs.sql
     >  datafile=e:\temp\tables\jobs_part2.txt
     >  filetype=txt
     >  delimiter="," quote="
     >  header=y;


Unload schema example

ora-cmd> unload table=all folder=e:\temp\tables filetype=csv delimiter="," header=y;


In batch mode

ora-cmd dsn="pan:withdata@192.168.0.102:1521/test" task=unload table=all folder=e:\temp\tables filetype=csv delimiter="," header=y quit=y