Solved

SQL Loader Control file to load data into multiple tables?

Posted on 2004-08-10
8
1,397 Views
Last Modified: 2008-01-09
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.numeric_id,utl_raw.cast_to_varchar2(nickname),
utl_raw.cast_to_varchar2(first_name),utl_raw.cast_to_varchar2(last_name),age,
geo_name,address,occupation_name,to_char(birthday,'DD-MON-YY'),photo_album_url,homepage_url
from tst_users,tst_geo,tst_user_comm, tst_occupation where
tst_users.numeric_id=tst_user_comm.numeric_id and
tst_users.geo=tst_geo.geo_code and
tst_users.occupation=tst_occupation.occupation_code;

The datafile looks like as below(has been word-wrapped):

"#";"UNIQUE_NAME";"NUMERIC_ID";"UTL_RAW.CAST_TO_VARCHAR2(NICKNAME)";"UTL_RAW.CAST_TO_VARCHAR2(FIRST_NAME)";"UTL_RAW.CAST_TO_VARCHAR2(LAST_NAME)";"AGE";"GEO_NAME";"ADDRESS";"OCCUPATION_NAME";"TO_CHAR(BIRTHDAY,'DD-MON-YY')";"PHOTO_ALBUM_URL";"HOMEPAGE_URL"
"1";"user01";"12001";"usr1";"f1";"l1";"35";"India";"user01@foo.com";"Engineer";"16-MAY-69";"http://photos.yahoo.com";"http://myhome.foo.com"
"2";"user02";"12002";"usr2";"f2";"l2";"25";"India";"user02@foo.com";"Engineer";"22-JUN-79";"http://photos.yahoo.com";"http://myhome.foo.com"
"3";"user03";"12003";"usr3";"f3";"l3";"22";"India";"user03@foo.com";"Engineer";"24-SEP-82";"http://photos.yahoo.com";"http://myhome.foo.com"
"4";"user04";"12004";"usr4";"f4";"l4";"18";"India";"user04@foo.com";"Engineer";"17-JUN-86";"http://photos.yahoo.com";"http://myhome.foo.com"
"5";"user05";"12005";"usr5";"f5";"l5";"21";"India";"user05@foo.com";"Engineer";"27-JUL-83";"http://photos.yahoo.com";"http://myhome.foo.com"
"6";"user06";"12006";"usr6";"f6";"l6";"31";"India";"user06@foo.com";"Engineer";"14-AUG-72";"http://photos.yahoo.com";"http://myhome.foo.com"
"7";"user07";"12007";"usr7";"f7";"l7";"25";"India";"user07@foo.com";"Engineer";"18-SEP-78";"http://photos.yahoo.com";"http://myhome.foo.com"
"8";"user08";"12008";"usr8";"f8";"l8";"35";"India";"user08@foo.com";"Engineer";"12-JUN-69";"http://photos.yahoo.com";"http://myhome.foo.com"
"9";"user09";"12009";"usr9";"f9";"l9";"45";"India";"user09@foo.com";"Engineer";"11-APR-59";"http://photos.yahoo.com";"http://myhome.foo.com"
"10";"user10";"12010";"usr10";"f10";"l10";"40";"India";"user10@foo.com";"Engineer";"28-MAY-64";"http://photos.yahoo.com";"http://myhome.foo.com"
"11";"user11";"12011";"usr11";"f11";"l11";"27";"India";"user11@foo.com";"Engineer";"29-JUN-77";"http://photos.yahoo.com";"http://myhome.foo.com"
"12";"user12";"12012";"usr12";"f12";"l12";"23";"India";"user12@foo.com";"Engineer";"21-DEC-80";"http://photos.yahoo.com";"http://myhome.foo.com"
"13";"user13";"12013";"usr13";"f13";"l13";"16";"India";"user13@foo.com";"Engineer";"22-OCT-87";"http://photos.yahoo.com";"http://myhome.foo.com"
"14";"user14";"12014";"usr14";"f14";"l14";"17";"India";"user14@foo.com";"Engineer";"21-FEB-87";"http://photos.yahoo.com";"http://myhome.foo.com"
"15";"user15";"12015";"usr15";"f15";"l15";"19";"India";"user15@foo.com";"Engineer";"27-NOV-84";"http://photos.yahoo.com";"http://myhome.foo.com"


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
0
Comment
Question by:karthikd22
  • 3
  • 2
8 Comments
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 125 total points
ID: 11766918
Try something like this:

OPTIONS (SKIP=1)
LOAD DATA
INFILE 'MyFile1.csv'
INTO TABLE USR_REC FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
(USER_ID
,UNIQUE_NAME        FILLER
,NUMERIC_ID         FILLER
,NICKNAME           FILLER
,FIRST_NAME         FILLER
,LAST_NAME          FILLER
,AGE                FILLER
,GEO_NAME           FILLER
,UID_NAME --ADDRESS
,OCCUPATION_NAME    FILLER
,BIRTHDAY           FILLER
,PHOTO_ALBUM_URL    FILLER
,HOMEPAGE_URL       FILLER
,DISABLED CONSTANT 'N')
INTO TABLE USR_PROFILE FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
(USER_ID POSITION(1)
,VALUE --UNIQUE_NAME
,NUMERIC_ID         FILLER
,NICKNAME           FILLER
,FIRST_NAME         FILLER
,LAST_NAME          FILLER
,AGE                FILLER
,GEO_NAME           FILLER
,ADDRESS            FILLER
,OCCUPATION_NAME    FILLER
,BIRTHDAY           FILLER
,PHOTO_ALBUM_URL    FILLER
,HOMEPAGE_URL       FILLER
,SHOW CONSTANT 'Y',FIELD CONSTANT '<UNIQUE_NAME>')
...
And so on for all other fields.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 11766940
PS: UID cannot (should not) be used as column name.
0
 
LVL 4

Expert Comment

by:andertst
ID: 11768447
>> 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

0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 11768638
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).


0
 
LVL 4

Expert Comment

by:andertst
ID: 11773910
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
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now