Here is a revised SQLLDR_EXP script that does this:

$ cat sqlldr_exp
#!/bin/sh

if [ "$1" = "" ]
then
cat << EOF
usage: flat un/pw [tables|views]

example: flat scott/tiger emp dept

description: Select over standard out all rows of table or view with
columns delimited by tabs.
EOF
exit
fi


PW=$1
shift

for X in $*
do
sqlplus -s $PW << EOF > /tmp/flat$$.sql
set wrap off
set feedback off
set pagesize 0
set verify off


prompt prompt LOAD DATA
prompt prompt INFILE *
prompt prompt INTO TABLE $X
prompt prompt REPLACE
prompt prompt FIELDS TERMINATED BY '|'
prompt prompt (
select 'prompt ' || decode(column_id,1,'',',') ||
lower(column_name ) ||
decode(data_type, 'DATE',
' date ''DD-MON-YYYY HH24:MI:SS''', null )
from user_tab_columns
where table_name = upper('$X')
/
prompt prompt )
prompt prompt BEGINDATA


prompt select
select lower(column_name)||'||chr(124)||'
from user_tab_columns
where table_name = upper('$X') and
column_id != (select max(column_id)
from user_tab_columns where
table_name = upper('$X'))
/
select lower(column_name)
from user_tab_columns
where table_name = upper('$X') and
column_id =
(select max(column_id) from user_tab_columns where
table_name = upper('$X'))
/
prompt from $X
prompt /
prompt exit
exit
EOF
sqlplus -s $PW << EOF
set wrap off
set feedback off
set pagesize 0
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
set verify off
set trimspool on
set linesize 5000
start /tmp/flat$$.sql
exit
EOF
rm /tmp/flat$$.sql
done


When you run this now, it'll generate:



$ sqlldr_exp scott/tiger emp
LOAD DATA
INFILE *
INTO TABLE emp
REPLACE
FIELDS TERMINATED BY '|'
(
empno
,ename
,job
,mgr
,hiredate date 'DD-MON-YYYY HH24:MI:SS'
,sal
,comm
,deptno
)
BEGINDATA
7370|SMITH|CLERK|7902|17-DEC-1980 00:00:00|800||20
7499|ALLEN|SALESMAN|7698|20-FEB-1981 00:00:00|1600|300|30
7521|WARD|SALESMAN|7698|22-FEB-1981 00:00:00|1250|500|30
7566|JONES|MANAGER|7839|02-APR-1981 00:00:00|2975||20
7654|MARTIN|SALESMAN|7698|28-SEP-1981 00:00:00|1250|1400|30
7698|BLAKE|MANAGER|7839|01-MAY-1981 00:00:00|2850||30
7782|CLARK|MANAGER|7839|09-JUN-1981 00:00:00|2450||10
7788|SCOTT|ANALYST|7566|09-DEC-1982 00:00:00|3000||20
7839|KING|PRESIDENT||17-NOV-1981 00:00:00|5000||10
7844|TURNER|SALESMAN|7698|08-SEP-1981 00:00:00|1500|0|30
7876|ADAMS|CLERK|7788|12-JAN-1983 00:00:00|1100||20
7900|JAMES|CLERK|7698|03-DEC-1981 00:00:00|950||30
7902|FORD|ANALYST|7566|03-DEC-1981 00:00:00|3000||20
7934|MILLER|CLERK|7782|23-JAN-1982 00:00:00|1300||10