HELP!

[nedpan]

Is there a way to generate a data file in the format of

"171092403",26-JAN-83,"F","05","05","1","MSB","1","BRESSETTE","CASSANDRA DONNA","1","1","1","1710185201","1","1710185201","171","01852","6"

trough sql*plus or something else. There are 700.000 records and it is very important to be able to some how genarate a file in the above format. I would like my control file to be in the form of:

LOAD DATA
INFILE 'data.txt'
APPEND
INTO TABLE NED
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'

Please help time is tight.

Nedpan.

[sree_sri]

From which table you are pulling the data. Give me the table desc. Once we got that information then write dynamic sql statement and pull the information from that table.

For ex:
table EMP

EMPNO NUMBER(10),
ENAME VARCHAR2(12),
JOB VARCHAR2(10),
DEPT NUMBER(10)

If you want to pull the data from emp and you want to use the ','(comma) as delimiter then write this program

set heading off
Spool temp_data.dat
select empno||','|ename||','||job||','||dept from emp;

The data from spooled from emp to temp.data.dat in this format.

empno,ename,job,sal

Now write your control file in this manner

LOAD DATA
INFILE 'temp_data.dat'
APPEND
INTO TABLE NED
FIELDS TERMINATED BY ","
(empno integer,
ename char,
job char,
dept integer
)

If you give me the table details(Both tables) then i can mail you the complete script. Good luck

[nedpan]

Thank you! What you have given me will only seperate the diferent values with a come. Is there a way to also enclose those fields by "?

[el472]

if you have a table emp
empno,empname

then
select '"'||empno||'"'||','||'"'||empname||'"' from emp;