Solved

SQL Loader Control file to load data into multiple tables?

Posted on 2004-08-10
8
1,566 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
error in my cursor 5 64
format dd/mm/yyyy parameter 16 59
plsql job on oracle 18 109
Oracle SQL Developer - SubString 2 52
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

738 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