Home » RDBMS Server » Server Utilities » SQL*Loader : How to load based on the header-record
SQL*Loader : How to load based on the header-record [message #73944] Sat, 07 August 2004 15:33 Go to next message
shankar
Messages: 29
Registered: January 2002
Junior Member
Hi,

Appreciate if someone can give me some directions on this.

I get data-files which has a particular identified on the 1st record( header ), based on which I've to load a constant for the 1st column of every row. How do I set in the control file.

Example :-

data_file_1
Oへ-aug-04�� -- format( company_id&#124date&#124row_count )
john saylor𞓄ܗ� --format( name&#124emp_id&#124salary )
larry ellissonә쐤� --format( name&#124emp_id&#124salary )
...
...
... -- 1000 such records

data_file_2
Sへ-aug-04ぺ
scott mcnealyә嗊�
john schwartzӚ嗊�
...
...
--10 such records

The table I want to load these 2, have these columns

Table A
Company_name
Emp_name
Emp_id
Run_date ( default to sysdate )

Now, all data-files will have only one identifier-record and in the 1st row. So in these examples, I've to set a constant as Oracle or Sun Microsystems based on the 1st record( header ) for every row inserted from that data-file. I need to embed all these in a single control-file to load data-files for different companies.

Thanks.
- Shankar.

Report message to a moderator

Re: SQL*Loader : How to load based on the header-record [message #73951 is a reply to message #73944] Sun, 08 August 2004 18:37 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
Senior Member
Some of your data is illegible on this forum, apparently due to some strange handling by this forum of the delimiter that you used. So, I created some similar data for a demonstration and used a comma as a delimiter. I don't think you can do this with only one control file. However, you can use two control files and you wll also need an extra table. Running SQL*Loader with the first control file will replace the header record into the extra table. Then running SQL*Loader with the second control file will skip the header and append the other rows into table a, selecting the company name from the extra table. You can just copy and run the entire script below to see what I mean. Use a separate schema to test on, so you don't drop your table a. I used the scott schema.

-- start of script
store set saved_settings replace

spool data_file_1.dat
prompt Oracle,aug-04,2
prompt john saylor,10,100
prompt larry ellison,20,200
spool off

spool data_file_2.dat
prompt Sun Microsystems,aug-04,2
prompt scott mcnealy,30,300
prompt john schwartz,40,400
spool off

create table a
(company_name varchar2(16),
emp_name varchar2(16),
emp_id number,
run_date date)
/

create table b
(company_name varchar2(16))
/

spool your_controla.ctl
prompt options (load=1)
prompt LOAD DATA
prompt replace
prompt INTO TABLE b
prompt FIELDS TERMINATED BY ','
prompt TRAILING NULLCOLS
prompt (company_name)
spool off

spool your_controlb.ctl
prompt options (skip=1)
prompt LOAD DATA
prompt append
prompt INTO TABLE a
prompt FIELDS TERMINATED BY ','
prompt TRAILING NULLCOLS
prompt (company_name "(select company_name from b)",
prompt emp_name,
prompt emp_id,
prompt run_date "(select sysdate from dual)")
spool off

start saved_settings
host sqlldr scott/tiger control=your_controla.ctl log=your_controla.log data=data_file_1.dat
host sqlldr scott/tiger control=your_controlb.ctl log=your_controlb.log data=data_file_1.dat
host sqlldr scott/tiger control=your_controla.ctl log=your_controla.log data=data_file_2.dat
host sqlldr scott/tiger control=your_controlb.ctl log=your_controlb.log data=data_file_2.dat

select * from b
/
select * from a
/

drop table a
/
drop table b
/
-- end of script

-- results of my run:
Wrote file saved_settings
Oracle,aug-04,2
john saylor,10,100
larry ellison,20,200
Sun Microsystems,aug-04,2
scott mcnealy,30,300
john schwartz,40,400

Table created.

Table created.

options (load=1)
LOAD DATA
replace
INTO TABLE b
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(company_name)
options (skip=1)
LOAD DATA
append
INTO TABLE a
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(company_name "(select company_name from b)",
emp_name,
emp_id,
run_date "(select sysdate from dual)")

COMPANY_NAME
----------------
Sun Microsystems

COMPANY_NAME     EMP_NAME             EMP_ID RUN_DATE
---------------- ---------------- ---------- ---------
Oracle           10                      100 08-AUG-04
Oracle           20                      200 08-AUG-04
Sun Microsystems 30                      300 08-AUG-04
Sun Microsystems 40                      400 08-AUG-04

Table dropped.

Table dropped.

Report message to a moderator

Re: SQL*Loader : How to load based on the header-record [message #73953 is a reply to message #73951] Sun, 08 August 2004 18:46 Go to previous message
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
Senior Member
I just realized that I had an extra column in my sample data and had the wrong data in the wrong columns. Here is a corrected example.

-- start of script
store set saved_settings replace

spool data_file_1.dat
prompt Oracle,aug-04,2
prompt john saylor,10
prompt larry ellison,20
spool off

spool data_file_2.dat
prompt Sun Microsystems,aug-04,2
prompt scott mcnealy,30
prompt john schwartz,40
spool off

create table a
(company_name varchar2(16),
emp_name varchar2(16),
emp_id number,
run_date date)
/

create table b
(company_name varchar2(16))
/

spool your_controla.ctl
prompt options (load=1)
prompt LOAD DATA
prompt replace
prompt INTO TABLE b
prompt FIELDS TERMINATED BY ','
prompt TRAILING NULLCOLS
prompt (company_name)
spool off

spool your_controlb.ctl
prompt options (skip=1)
prompt LOAD DATA
prompt append
prompt INTO TABLE a
prompt FIELDS TERMINATED BY ','
prompt TRAILING NULLCOLS
prompt (emp_name,
prompt emp_id,
prompt company_name "(select company_name from b)",
prompt run_date "(select sysdate from dual)")
spool off

start saved_settings
host sqlldr scott/tiger control=your_controla.ctl log=your_controla.log data=data_file_1.dat
host sqlldr scott/tiger control=your_controlb.ctl log=your_controlb.log data=data_file_1.dat
host sqlldr scott/tiger control=your_controla.ctl log=your_controla.log data=data_file_2.dat
host sqlldr scott/tiger control=your_controlb.ctl log=your_controlb.log data=data_file_2.dat

select * from b
/
select * from a
/

drop table a
/
drop table b
/
-- end of script

-- results of my run:
Wrote file saved_settings
Oracle,aug-04,2
john saylor,10
larry ellison,20
Sun Microsystems,aug-04,2
scott mcnealy,30
john schwartz,40

Table created.

Table created.

options (load=1)
LOAD DATA
replace
INTO TABLE b
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(company_name)
options (skip=1)
LOAD DATA
append
INTO TABLE a
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(emp_name,
emp_id,
company_name "(select company_name from b)",
run_date "(select sysdate from dual)")

COMPANY_NAME
----------------
Sun Microsystems

COMPANY_NAME     EMP_NAME             EMP_ID RUN_DATE
---------------- ---------------- ---------- ---------
Oracle           john saylor              10 08-AUG-04
Oracle           larry ellison            20 08-AUG-04
Sun Microsystems scott mcnealy            30 08-AUG-04
Sun Microsystems john schwartz            40 08-AUG-04

Table dropped.

Table dropped.

Report message to a moderator

Previous Topic: SQL*Loader : How to load based on the header-record
Next Topic: Import/Export commands with different oracle versions
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Jul 03 10:34:20 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.