Home » RDBMS Server » Server Utilities » SQL *Loader - Loading data into multiple tables (Oracle 10.1)
SQL *Loader - Loading data into multiple tables [message #395586] Wed, 01 April 2009 23:26 Go to next message
oracle123
Messages: 56
Registered: March 2009
Member
hi all,

I am a newbie trying to load data from flat file to two different tables on the database using SQL*Loader utility.

Two tables look as described below:

desc emp

Name         Type
----         ----------
eid          number(5)
ename        varchar2(25)
dept_id      number(5)


desc dept

Name         Type
----         ----------
dept_id      number(5)
dept_name    varchar2(25)


Sample records in flat file are csv formatted and look like this:

123, kios, scott, 4, sales, 5000
45, nims, john, 34, marketing, 23000


In the above records:

1st value refers to eid
3rd value refers to ename
4th value refers to dept_id
5th value refers to dept_name
2nd and 6th values should not be considered (need not be updated into the tables)

I did some research and found this link on oracle website but it asks me to use POSITION which I dont think is possible in my case as it is a variable.
http://www.sc.ehu.es/siwebso/KZCC/Oracle_10g_Documentacion/server.101/b10825/ldr_cases.htm#i1007217

Any help on how to implement the control file for this case would be greatly appreciated.

Thanks,
Scott.








Re: SQL *Loader - Loading data into multiple tables [message #395588 is a reply to message #395586] Wed, 01 April 2009 23:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I am a newbie trying to load data from flat file to two different tables on the database using SQL*Loader utility.
So do not use wrong tool.
Use EXTERNAL TABLE & load data vis PL/SQL as desired.
Re: SQL *Loader - Loading data into multiple tables [message #395736 is a reply to message #395586] Thu, 02 April 2009 08:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table t1 (
  2  eid          number(5),
  3  ename        varchar2(25),
  4  dept_id      number(5)
  5  )
  6  /

Table created.

SQL> create table t2 (
  2  dept_id      number(5),
  3  dept_name    varchar2(25)
  4  )
  5  /

Table created.

SQL> create table ext (
  2  eid          number(5),
  3  fill1        varchar2(25),
  4  ename        varchar2(25),
  5  dept_id      number(5),
  6  dept_name    varchar2(25),
  7  fill2        varchar2(10)
  8  )
  9  organization external (
 10    type oracle_loader
 11    default directory WORK_DIR
 12    access parameters (
 13      records delimited by X'0A'
 14      nobadfile
 15      nologfile
 16      nodiscardfile
 17      fields terminated by ','
 18      (eid, fill1, ename, dept_id, dept_name, fill2)
 19    )
 20    location ('t.txt')
 21  )
 22  reject limit unlimited
 23  /

Table created.

SQL> insert all 
  2    into t1 (eid, ename, dept_id) values (eid, trim(ename), dept_id)
  3    into t2 (dept_id, dept_name) values (dept_id, trim(dept_name))
  4  select * from ext
  5  /

4 rows created.

SQL> select * from t1;
       EID ENAME                        DEPT_ID
---------- ------------------------- ----------
       123 scott                              4
        45 john                              34

2 rows selected.

SQL> select * from t2;
   DEPT_ID DEPT_NAME
---------- -------------------------
         4 sales
        34 marketing

2 rows selected.

Regards
Michel

[Updated on: Thu, 02 April 2009 08:51]

Report message to a moderator

Re: SQL *Loader - Loading data into multiple tables [message #395738 is a reply to message #395586] Thu, 02 April 2009 08:52 Go to previous messageGo to next message
oracle123
Messages: 56
Registered: March 2009
Member
Blackswan,

Thanks for your prompt reply. My application requires to log everything that's happening during the load process which I can do using SQL Loader through bad, discarded and log files. For example oracle errors, bad records and all.

Where as we cannot achieve the same logging if we use external tables. Please suggest on this.

Thanks,
Scott.
Re: SQL *Loader - Loading data into multiple tables [message #395752 is a reply to message #395738] Thu, 02 April 2009 09:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can do the same thing with external table, just use badfile, logfile and discardfile parameters instead on "no..." one I used in the example.

Regards
Michel
Re: SQL *Loader - Loading data into multiple tables [message #395757 is a reply to message #395586] Thu, 02 April 2009 09:32 Go to previous messageGo to next message
oracle123
Messages: 56
Registered: March 2009
Member
Michel,

I could not expect a better solution than the one you provided.
Thanks a lot. Now I have a better understanding of external tables and logging process.

My application should be a cron job that runs every 15 minutes and collects the data from the flat file. I am wondering if the external table purges all the existing data before loading new data from the flat file everytime I invoke the following set of statements:

Quote:
SQL> insert all
2 into t1 (eid, ename, dept_id) values (eid, trim(ename), dept_id)
3 into t2 (dept_id, dept_name) values (dept_id, trim(dept_name))
4 select * from ext
5 /


Thanks,
Scott.
Re: SQL *Loader - Loading data into multiple tables [message #395798 is a reply to message #395757] Thu, 02 April 2009 10:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
External table is a way to view your file as a table it does not do anything you explicitly do not with SQL.

Regards
Michel
Re: SQL *Loader - Loading data into multiple tables [message #395819 is a reply to message #395586] Thu, 02 April 2009 12:41 Go to previous messageGo to next message
oracle123
Messages: 56
Registered: March 2009
Member
Michel,

Now I am facing more tricky problem while dealing with another application. I need to upload data into a single table from the flat file which is described as below:

desc students

Name           Type
----           ----------
student_id     number(5)
student_name   varchar2(25)
subject_id     varchar2(5)
marks          number(3)


Sample records in flat file are csv formatted and look like this:

123, king, T1, 89, T2, 97, T3, 67
4568, steven, T1, 97, T4, 83, T9, 54

These records should look in the students table as shown below:

STUDENT_ID    STUDENT_NAME   SUBJECT_ID  MARKS
----------    ------------   ----------  ------
123           king           T1           89
123           king           T2           97
123           king           T3           67
4568          steven         T1           97
4568          steven         T4           83
4568          steven         T9           54


It's like dividing single physical record into multiple logical records.

In my actual scenario, there will be 90 (subject_id,marks) values for each student_id in a record in the flat file opposed to 3 values which I showed to simplify the problem.

Please advice me on this issue. Thanks again.

- Scott.

Re: SQL *Loader - Loading data into multiple tables [message #395821 is a reply to message #395819] Thu, 02 April 2009 13:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use INSERT ALL in the same way but to insert several rows in the same table instead of 2 tables.

Regards
Michel

[Updated on: Thu, 02 April 2009 13:01]

Report message to a moderator

Re: SQL *Loader - Loading data into multiple tables [message #395827 is a reply to message #395586] Thu, 02 April 2009 13:36 Go to previous messageGo to next message
oracle123
Messages: 56
Registered: March 2009
Member
Michel,

In order to use external tables method, I had to create external table with more than 180 columns to handle this case as each physical record in the flat file has 90 values for "subject_id" and 90 values for "marks".

Is there a work around with SQL*Loader for this so that I dont need to create an external table with so many columns.

Thanks,
Scott.
Re: SQL *Loader - Loading data into multiple tables [message #395829 is a reply to message #395827] Thu, 02 April 2009 13:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the problem with the numbetr of columns? You do it only once and can write it in less time that this topic lasts.
Anyway, with SQL*Loader, you have to write a control file with the same number of columns.
How could Oracle know the fields if you don't explain it what are the fields?

Regards
Michel

[Updated on: Thu, 02 April 2009 13:52]

Report message to a moderator

Re: SQL *Loader - Loading data into multiple tables [message #395833 is a reply to message #395827] Thu, 02 April 2009 15:14 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You can load the subjects and marks into one column in a staging table, then parse them out, as demonstrated below.

-- flat_file.csv:
123, king, T1, 89, T2, 97, T3, 67
4568, steven, T1, 97, T4, 83, T9, 54


-- test.ctl:
load data
infile flat_file.csv
into table staging
fields
(student_id         terminated by ',',
 student_name       terminated by ',',
 subjects_and_marks terminated by x'0a')


SCOTT@orcl_11g> CREATE TABLE staging
  2    (student_id	    number(5),
  3  	student_name	    varchar2(25),
  4  	subjects_and_marks  CLOB)
  5  /

Table created.

SCOTT@orcl_11g> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log

SCOTT@orcl_11g> COLUMN subjects_and_marks FORMAT A25 WORD_WRAPPED
SCOTT@orcl_11g> SELECT * FROM staging
  2  /

STUDENT_ID STUDENT_NAME              SUBJECTS_AND_MARKS
---------- ------------------------- -------------------------
       123  king                     T1, 89, T2, 97, T3, 67
      4568  steven                   T1, 97, T4, 83, T9, 54

SCOTT@orcl_11g> CREATE TABLE students
  2    (student_id     number(5),
  3  	student_name   varchar2(25),
  4  	subject_id     varchar2(5),
  5  	marks	       number(3))
  6  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION list_element
  2  	(p_string    VARCHAR2,
  3  	 p_element   INTEGER,
  4  	 p_separator VARCHAR2 DEFAULT ',')
  5  	RETURN	     VARCHAR2
  6  AS
  7    v_string      VARCHAR2 (32767);
  8  BEGIN
  9    v_string := p_separator || p_string || p_separator;
 10    v_string := SUBSTR (v_string,
 11  			   INSTR (v_string, p_separator, 1, p_element)
 12  			   + LENGTH (p_separator));
 13    RETURN TRIM (SUBSTR (v_string, 1, INSTR (v_string, p_separator) - 1));
 14  END list_element;
 15  /

Function created.

SCOTT@orcl_11g> INSERT INTO students
  2  SELECT student_id, student_name,
  3  	    list_element (subjects_and_marks, rn1),
  4  	    list_element (subjects_and_marks, rn2)
  5  FROM   staging,
  6  	    (SELECT ((ROWNUM - 1) * 2) + 1 AS rn1,
  7  		    ((ROWNUM - 1) * 2) + 2 AS rn2
  8  	     FROM   DUAL
  9  	     CONNECT BY LEVEL <=
 10  		    (SELECT MAX (LENGTH (subjects_and_marks)
 11  				 - LENGTH (REPLACE (subjects_and_marks, ',', ''))) + 1
 12  		     FROM   staging))
 13  WHERE  list_element (subjects_and_marks, rn1) IS NOT NULL
 14  /

6 rows created.

SCOTT@orcl_11g> SELECT * FROM students ORDER BY student_id, subject_id
  2  /

STUDENT_ID STUDENT_NAME              SUBJE      MARKS
---------- ------------------------- ----- ----------
       123  king                     T1            89
       123  king                     T2            97
       123  king                     T3            67
      4568  steven                   T1            97
      4568  steven                   T4            83
      4568  steven                   T9            54

6 rows selected.

SCOTT@orcl_11g> 

Re: SQL *Loader - Loading data into multiple tables [message #395847 is a reply to message #395586] Thu, 02 April 2009 19:02 Go to previous messageGo to next message
oracle123
Messages: 56
Registered: March 2009
Member
Barbara Boehmer,

I am very much impressed with your solution. Could you please explain me what does FUNCTION "list_element" do?
And the other concern I have is the negative performance effect that may caused due to using so many functions and translations.

- Scott.
Re: SQL *Loader - Loading data into multiple tables [message #395850 is a reply to message #395586] Thu, 02 April 2009 21:01 Go to previous messageGo to next message
oracle123
Messages: 56
Registered: March 2009
Member
Michel Cadot,

To use external tables, do we need to have the flat file on the same server where database resides?

In my scenario, flat file resides on a different application server(not on the database server). So I am unable to create directory pointing to that server.

Thanks,
Scott.
Re: SQL *Loader - Loading data into multiple tables [message #395851 is a reply to message #395586] Thu, 02 April 2009 21:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>To use external tables, do we need to have the flat file on the same server where database resides?
Yes
Re: SQL *Loader - Loading data into multiple tables [message #395856 is a reply to message #395847] Thu, 02 April 2009 22:20 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Quote:

Could you please explain me what does FUNCTION "list_element" do?



It returns the nth element of a delimited string. So, if you pass it a string like "a,b,c" as your first parameter and pass "2" for the element for the second parameter and "," for the delimiter for the third parameter or accept that as default, then it returns "b" as that is the second element of that comma-delimited string.

Quote:

And the other concern I have is the negative performance effect that may caused due to using so many functions and translations.



All of the code that I used should run quickly. When in doubt, test and see. It eliminates the need for creating a SQL*Loader control file and staging table with one column for each subject and marks. And, your data file does not have to reside on your server. It can be on a client machine when using SQL*Loader. If you use external tables, then the data file must reside on the same server as your database. Your original problem in this thread could also be done using SQL*Loader, specifying delimiters instead of positions.


[Updated on: Thu, 02 April 2009 22:22]

Report message to a moderator

Re: SQL *Loader - Loading data into multiple tables [message #396001 is a reply to message #395586] Fri, 03 April 2009 08:34 Go to previous messageGo to next message
oracle123
Messages: 56
Registered: March 2009
Member
Barbara Boehmer,

Once again thanks for your detailed explanation.

So, Now I think external tables is out of question for me and need to use SQL*Loader.

Quote:
It eliminates the need for creating a SQL*Loader control file and staging table with one column for each subject and marks.


Actually the problem in creating staging table is: I need two cron jobs to run on daily basis to load the data into database. One for the SQL*Loader to load data into staging table and another cron job to translate the data into my final table.

So I am looking at a solution in which we can bind all the actions into a single cron job.

Thanks all for your time and consistent support.

- Scott.
Re: SQL *Loader - Loading data into multiple tables [message #396054 is a reply to message #396001] Fri, 03 April 2009 11:57 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You can use dbms_scheduler to group jobs together. You can specify job_type of executable to run any commands that can be run from the operating system, such as sqlldr. The link below is to some 10g documentation on dbms_scheduler.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sched.htm#ARPLS138
Previous Topic: Import & Export utility
Next Topic: Export via conventional path v/s direct path
Goto Forum:
  


Current Time: Fri Mar 29 10:50:42 CDT 2024