Re: SQL to Dump rows to ASCII comma delimited File

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 28 Jul 1999 14:35:18 GMT
Message-ID: <37a514e3.12916002@newshost.us.oracle.com>

A copy of this was sent to "michael laffety" <mlaff_at_paragon.ie> (if that email address didn't require changing) On Wed, 28 Jul 1999 13:52:02 +0100, you wrote:

>Hi all,
>
>Does anyone have an SQL script that will dump select rows from a table to a
>ASCII comma delimited File for subsequent processing by SQL loader. Thanks
>in advance.
>
>Michael
>

I use this sh script:

$ 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

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) 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
set     verify off

set trimspool on
set linesize 5000
start /tmp/flat$$.sql
exit
EOF
rm /tmp/flat$$.sql
done

==================== eof ============================

to do it. It works like this:

$ sqlldr_exp scott/tiger emp
LOAD DATA
INFILE *
INTO TABLE emp
REPLACE
FIELDS TERMINATED BY '|'
(
empno

,ename
,job
,mgr
,hiredate
,sal
,comm
,deptno

)
BEGINDATA

7369|SMITH|CLERK|7902|17-DEC-80|800||20
7499|ALLEN|SALESMAN|7698|20-FEB-81|1600|900|30
7521|WARD|SALESMAN|7698|22-FEB-81|1250|500|30
7566|JONES|ANALYST|7839|02-APR-81|2975||20
7654|MARTIN|SALESMAN|7698|28-SEP-81|1250|1400|30
7698|BLAKE|MANAGER|7839|01-MAY-81|2850||30
7782|CLARK|MANAGER|7839|09-JUN-81|2451||10
7788|SCOTT|ANALYST|7566|09-DEC-82|3000||20
7839|KING|PRESIDENT||17-NOV-81|5000||10
7844|TURNER|SALESMAN|7698|08-SEP-81|1500|0|30
7876|ADAMS|CLERK|7788|12-JAN-83|1100||20
7900|JAMES|CLERK|7698|03-DEC-81|950||30
7902|FORD|ANALYST|7566|03-DEC-81|3000||20
7934|MILLER|CLERK|7782|23-JAN-82|1300||10

You would redirect the output to a .ctl file and then sqlldr can load it back in. BEWARE data containing | and newlines!!!

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Jul 28 1999 - 09:35:18 CDT