SQL LOADER [message #57135] |
Thu, 22 May 2003 05:56 |
sidd
Messages: 130 Registered: May 2003
|
Senior Member |
|
|
I have a problem in writing a sql loader control file script, flat has 3 diff fields for date, i need combine these 3 fileds and put them in one single cloumn,
eg flat file:
ID YYYY MM DD
1 1999 05 05
2 2000 08 12
table in Oracle is Table1(ID, Pay_DATE)
any help is appreciated. pelase
thanks
|
|
|
Re: SQL LOADER [message #57144 is a reply to message #57135] |
Thu, 22 May 2003 07:50 |
|
Mahesh Rajendran
Messages: 10708 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
the easiest way i do is to
load the datafile AS IT IS (ie, with 4 columns) into a 4 colum table dummy_TABLE.
then issue,
insert into TABLE1 (Select id,yyyy||mm||dd from dummy_table);
drop table dummy_table;
or
use simple utilities like this
here i have joined the first 3 fields and 6th field into a single field. now use this newfile to load into ur database using sql*loader
C:>ls -lt emp.dat
-rwxrwxrwa 1 Administrators None 1118 May 22 09:11 emp.dat
C:>cat emp.dat
7369 SMITH CLERK 7902 12/17/80 800 20
7499 ALLEN SALESMAN 7698 02/20/81 1600 300 30
7521 WARD SALESMAN 7698 02/22/81 1250 500 30
7566 JONES MANAGER 7839 04/02/81 2975 20
7654 MARTIN SALESMAN 7698 09/28/81 1250 1400 30
7698 BLAKE MANAGER 7839 05/01/81 2850 30
7782 CLARK MANAGER 7839 06/09/81 2450 10
7788 SCOTT ANALYST 7566 07/13/87 3000 20
7839 KING PRESIDENT 11/17/81 5000 10
7844 TURNER SALESMAN 7698 09/08/81 1500 0 30
7876 ADAMS CLERK 7788 07/13/87 1100 20
7900 JAMES CLERK 7698 12/03/81 950 30
7902 FORD ANALYST 7566 12/03/81 3000 20
7934 MILLER CLERK 7782 01/23/82 1300 10
C:>awk '{print $1$2$3$6}' emp.dat >newemp.dat
C:>cat newemp.dat
7369SMITHCLERK800
7499ALLENSALESMAN1600
7521WARDSALESMAN1250
7566JONESMANAGER2975
7654MARTINSALESMAN1250
7698BLAKEMANAGER2850
7782CLARKMANAGER2450
7788SCOTTANALYST3000
7839KINGPRESIDENT10
7844TURNERSALESMAN1500
7876ADAMSCLERK1100
7900JAMESCLERK950
7902FORDANALYST3000
7934MILLERCLERK1300
|
|
|