Home » RDBMS Server » Server Administration » SQL LOADER
SQL LOADER [message #57135] Thu, 22 May 2003 05:56 Go to next message
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 Go to previous message
Mahesh Rajendran
Messages: 10707
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

Previous Topic: Performance Related Question
Next Topic: Buffer Size.
Goto Forum:
  


Current Time: Fri Sep 20 11:34:03 CDT 2024