Loading Oracle data to unix in flat file

raghav288 · January 20, 2009, 3:32am

Hi
I would like to know how to load oracle data to unix flat file using a shell script.

Can we use sqlldr to import data from oracle, if so can anyone help me with it.

jgrogan · January 20, 2009, 7:40am

Use sqlplus to select your data and output it to a file:

LOGON={yourlogonstring}
OUTPUT={youroutpufilename}
SCHEMA={yourschema name}
SRCTBL={yoursourcetablename}

sqlplus -s $LOGON <<EOF >$OUTPUT
 column {columname1}       format {formatspec}  heading .
 column {columname2}       format {formatspec}  heading .
 ...other columns...
 set linesize {line size needed}
 select {columname1}
       ,{columname2}
       ,...other columns...
 from $SCHEMA.$SRCTBL;
EOF

Hope this gets you started

JG

raghav288 · January 23, 2009, 1:01am

jgrogan:

Use sqlplus to select your data and output it to a file:

LOGON={yourlogonstring}
OUTPUT={youroutpufilename}
SCHEMA={yourschema name}
SRCTBL={yoursourcetablename}

sqlplus -s $LOGON <<EOF >$OUTPUT
 column {columname1}       format {formatspec}  heading .
 column {columname2}       format {formatspec}  heading .
 ...other columns...
 set linesize {line size needed}
 select {columname1}
   ,{columname2}
   ,...other columns...
 from $SCHEMA.$SRCTBL;
EOF

Hope this gets you started

JG

Thank you very much