karthikd22
asked on
SQL Loader Control file to load data into multiple tables?
Hello All,
I have a data file generated with the help of an SQL query.I am trying to use SQL Loader to load the
data into a couple of other database tables.
The data file is generated as a csv file using TOra (Toolkit for Oracle)
from the following query:
select unique_name,odg_users.nume
utl_raw.cast_to_varchar2(f
geo_name,address,occupatio
from tst_users,tst_geo,tst_user
tst_users.numeric_id=tst_u
tst_users.geo=tst_geo.geo_
tst_users.occupation=tst_o
The datafile looks like as below(has been word-wrapped):
"#";"UNIQUE_NAME";"NUMERIC
"1";"user01";"12001";"usr1
"2";"user02";"12002";"usr2
"3";"user03";"12003";"usr3
"4";"user04";"12004";"usr4
"5";"user05";"12005";"usr5
"6";"user06";"12006";"usr6
"7";"user07";"12007";"usr7
"8";"user08";"12008";"usr8
"9";"user09";"12009";"usr9
"10";"user10";"12010";"usr
"11";"user11";"12011";"usr
"12";"user12";"12012";"usr
"13";"user13";"12013";"usr
"14";"user14";"12014";"usr
"15";"user15";"12015";"usr
I was reading about various Case Studies for SQL Loader from the Oracle Application Developer's guide.
The document talks about using "fields terminated by "," " or use position values like 1:6 is id etc.
I am bit confused what kind of control file I need to write to store the above data into the
tables.
For example, let me explain how the data has to be stored in the destination tables:
The destination tables are USR_REC and USR_PROFILE.
The schema of the tables are as follows:
usr_rec
(
user_id NUMBER (12) NOT NULL
, uid VARCHAR2 (256) NOT NULL
, auth_pwd VARCHAR2 (32)
, disabled VARCHAR2 (1) NOT NULL
, login_stamp DATE
, logout_stamp DATE
, logout_time NUMBER (12)
, auth_count NUMBER (8)
, auth_token VARCHAR2 (16)
, pwd_stamp DATE
)
usr_profile
(
user_id NUMBER
, show VARCHAR2 (1) NOT NULL
, field VARCHAR2 (64) NOT NULL
, value VARCHAR2 (256)
)
The records in USR_REC and USR_PROFILE are as follows.
USR_REC
========
The user_id column is a sequence value kind of and the UID column should be mapped from UNIQUE_NAME in the data file appended with host name e.g host.foo.com
USER_ID UID
------- ----
1 user01@host.foo.com
2 user02@host.foo.com
USR_PROFILE
===========
The user_id field is a foreign key to user_id of USR_REC table.The show column has "y" or "n" values.
The field column can be known say for example e-mail,bday,nickanme etc.The value column should be mapped to NICKNAME, FIRST_NAME, LAST_NAME accordingly.
For example from the record 1, the table should contain the record as:
USER_ID SHOW FIELD VALUE
------- ----- ------ ------
1 Y <nickname> usr1
1 Y <given> f1
1 Y <family> l1
1 Y <country> India
1 Y <home> user01@foo.com
1 Y <role> Engineer
1 Y <bday> 18-MAY-69
1 Y <Photo> http://photos.yahoo.com
1 Y <URL> http://myhome.foo.com
Like that for user2,user3 etc.
My questions are :
1. Is the format for data file correct?I don't know why TOra generates a CSV file with ";" as the
delimiter!
2. In a control file do I need to specify some columns as FILLER?
3. Can I use FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' option or do I need to specify position of data fields to map data into specific table columns?
If somebody could give some pointers/suggestions that would be really helpful.I don't have prior experience on this.But I need to do this.So I am learning something from documents, newsgroups and trying out.
Thanks for your time.
Regards,
Karthik
I have a data file generated with the help of an SQL query.I am trying to use SQL Loader to load the
data into a couple of other database tables.
The data file is generated as a csv file using TOra (Toolkit for Oracle)
from the following query:
select unique_name,odg_users.nume
utl_raw.cast_to_varchar2(f
geo_name,address,occupatio
from tst_users,tst_geo,tst_user
tst_users.numeric_id=tst_u
tst_users.geo=tst_geo.geo_
tst_users.occupation=tst_o
The datafile looks like as below(has been word-wrapped):
"#";"UNIQUE_NAME";"NUMERIC
"1";"user01";"12001";"usr1
"2";"user02";"12002";"usr2
"3";"user03";"12003";"usr3
"4";"user04";"12004";"usr4
"5";"user05";"12005";"usr5
"6";"user06";"12006";"usr6
"7";"user07";"12007";"usr7
"8";"user08";"12008";"usr8
"9";"user09";"12009";"usr9
"10";"user10";"12010";"usr
"11";"user11";"12011";"usr
"12";"user12";"12012";"usr
"13";"user13";"12013";"usr
"14";"user14";"12014";"usr
"15";"user15";"12015";"usr
I was reading about various Case Studies for SQL Loader from the Oracle Application Developer's guide.
The document talks about using "fields terminated by "," " or use position values like 1:6 is id etc.
I am bit confused what kind of control file I need to write to store the above data into the
tables.
For example, let me explain how the data has to be stored in the destination tables:
The destination tables are USR_REC and USR_PROFILE.
The schema of the tables are as follows:
usr_rec
(
user_id NUMBER (12) NOT NULL
, uid VARCHAR2 (256) NOT NULL
, auth_pwd VARCHAR2 (32)
, disabled VARCHAR2 (1) NOT NULL
, login_stamp DATE
, logout_stamp DATE
, logout_time NUMBER (12)
, auth_count NUMBER (8)
, auth_token VARCHAR2 (16)
, pwd_stamp DATE
)
usr_profile
(
user_id NUMBER
, show VARCHAR2 (1) NOT NULL
, field VARCHAR2 (64) NOT NULL
, value VARCHAR2 (256)
)
The records in USR_REC and USR_PROFILE are as follows.
USR_REC
========
The user_id column is a sequence value kind of and the UID column should be mapped from UNIQUE_NAME in the data file appended with host name e.g host.foo.com
USER_ID UID
------- ----
1 user01@host.foo.com
2 user02@host.foo.com
USR_PROFILE
===========
The user_id field is a foreign key to user_id of USR_REC table.The show column has "y" or "n" values.
The field column can be known say for example e-mail,bday,nickanme etc.The value column should be mapped to NICKNAME, FIRST_NAME, LAST_NAME accordingly.
For example from the record 1, the table should contain the record as:
USER_ID SHOW FIELD VALUE
------- ----- ------ ------
1 Y <nickname> usr1
1 Y <given> f1
1 Y <family> l1
1 Y <country> India
1 Y <home> user01@foo.com
1 Y <role> Engineer
1 Y <bday> 18-MAY-69
1 Y <Photo> http://photos.yahoo.com
1 Y <URL> http://myhome.foo.com
Like that for user2,user3 etc.
My questions are :
1. Is the format for data file correct?I don't know why TOra generates a CSV file with ";" as the
delimiter!
2. In a control file do I need to specify some columns as FILLER?
3. Can I use FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' option or do I need to specify position of data fields to map data into specific table columns?
If somebody could give some pointers/suggestions that would be really helpful.I don't have prior experience on this.But I need to do this.So I am learning something from documents, newsgroups and trying out.
Thanks for your time.
Regards,
Karthik
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
MikeOM_DBA
PS: UID cannot (should not) be used as column name.
>> My questions are :
>> 1. Is the format for data file correct?I don't know why TOra generates a CSV file with ";" as the delimiter!
Check to see if the data is actually on multiple lines or if it just looks like it. If you are on linux (or any other *nix) you can use 'wc file_name.csv' to find out how many lines there are. If your query returned 10 rows, then wc should return 10 rows. If not, your linesize setting is probably too short. If you are on Win, the best advice I have is to make NotePad (or whatever you are using) wider on your screen and see if the lines move up to the previous line. If you have a bunch of white-space to the right of the data, then TORA has set the linesize too short.
>> 2. In a control file do I need to specify some columns as FILLER?
Certainly, if you don't have data for some of the fields, FILLER will let you skip them as demonstrated by MikeOM_DBA above.
>> 3. Can I use FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' option or do I need to specify position of data fields to map data into >> specific table columns?
No need to use position specification and unless your data is of identical length, there is no easy way to do it in a CSV. Use the pattern Mike showed above and try it out.
Good luck and happy load'ing :)
Stephen
>> 1. Is the format for data file correct?I don't know why TOra generates a CSV file with ";" as the delimiter!
Check to see if the data is actually on multiple lines or if it just looks like it. If you are on linux (or any other *nix) you can use 'wc file_name.csv' to find out how many lines there are. If your query returned 10 rows, then wc should return 10 rows. If not, your linesize setting is probably too short. If you are on Win, the best advice I have is to make NotePad (or whatever you are using) wider on your screen and see if the lines move up to the previous line. If you have a bunch of white-space to the right of the data, then TORA has set the linesize too short.
>> 2. In a control file do I need to specify some columns as FILLER?
Certainly, if you don't have data for some of the fields, FILLER will let you skip them as demonstrated by MikeOM_DBA above.
>> 3. Can I use FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' option or do I need to specify position of data fields to map data into >> specific table columns?
No need to use position specification and unless your data is of identical length, there is no easy way to do it in a CSV. Use the pattern Mike showed above and try it out.
Good luck and happy load'ing :)
Stephen
NOTEs to Stephen:
Requirement is to generate one row in usr_rec table and multiple rows in usr_profile
table for each field of one input record.
position(1) is required to position the pointer at beginning of input record for each field
that is to be loaded (due to csv format).
Requirement is to generate one row in usr_rec table and multiple rows in usr_profile
table for each field of one input record.
position(1) is required to position the pointer at beginning of input record for each field
that is to be loaded (due to csv format).
NOTE to Mike:
I see that and I was not referring to your use of position(1), but rather to the original question of the use of position(n,m) in the sample material. If the source data is fixed-width, then you could use position (40:45) to identify a data for a particular field.
I think we are both on the same page, I was just trying to provide some answers to help in the requestors understanding of the concepts.
HAND
I see that and I was not referring to your use of position(1), but rather to the original question of the use of position(n,m) in the sample material. If the source data is fixed-width, then you could use position (40:45) to identify a data for a particular field.
I think we are both on the same page, I was just trying to provide some answers to help in the requestors understanding of the concepts.
HAND