OraFAQ Forum: Server Utilities » SQLLDR - look up on table to find column value

Home » RDBMS Server » Server Utilities » SQLLDR - look up on table to find column value

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQLLDR - look up on table to find column value [message #403786]

Mon, 18 May 2009 12:21

nidi_03
Messages: 10
Registered: May 2009
Location: US

Junior Member

Hi,

I am loading table Emp using sqlldr. I need to populate the column Date_paid_key of table Emp by lookin up on another table dates. Sample data is as given below. How can I do it using sqlldr? I can load my csv in a temp table and then proceed. Any other suggestions?

Src.csv			
EmpId	EmpName	Salary	Date_paid
1	A	10	4/15/2009
2	B	20	4/18/2009

Lookup Table dates
Date	       Date_key		
4/15/2009	1001		
4/16/2009	1002		
4/17/2009	1003		
4/18/2009	1004		

Target table Emp		
EmpId	EmpName	Salary	Date_paid_key
1	A	10	1001
2	B	20	1004

[EDITED by LF: applied [PRE] tags]

[Updated on: Mon, 18 May 2009 14:15] by Moderator

Report message to a moderator

Re: SQLLDR - look up on table to find column value [message #403790 is a reply to message #403786]

Mon, 18 May 2009 13:11

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Use external table.

Regards
Michel

Report message to a moderator

Re: SQLLDR - look up on table to find column value [message #403796 is a reply to message #403786]

Mon, 18 May 2009 14:35

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Here's SQL*Loader based solution.

First, let's create our environment:

SQL> desc o_emp;
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- ---------------

 EMPID                                                                      NUMBER
 EMPNAME                                                                    VARCHAR2(20)
 SALARY                                                                     NUMBER
 DATE_PAID_KEY                                                              NUMBER

SQL> desc o_lookup;
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- ---------------

 DATE_KEY                                                                   NUMBER
 DATE_COL                                                                   DATE

SQL> select * from o_lookup;

  DATE_KEY DATE_COL
---------- ----------
      1001 15.04.2009
      1002 16.04.2009
      1003 17.04.2009
      1004 18.04.2009

SQL>

In order to find DATE_KEY from the lookup table, I'll create a rudimentary function:

SQL> CREATE OR REPLACE FUNCTION o_test (par_date_paid IN DATE)
  2    RETURN NUMBER
  3  IS
  4    retval O_LOOKUP.date_key%TYPE;
  5  BEGIN
  6    SELECT date_key INTO retval
  7               FROM O_LOOKUP
  8      WHERE date_col = par_date_paid;
  9    RETURN (retval);
 10  END;
 11  /

Function created.

SQL>

This is a control file (I have included input sample data into it, for my own convenience). Note the way function is used in order to fill in the missing value:

load data
  infile *
  replace
into table o_emp
  fields terminated by ','
  trailing nullcols
(empid,
 empname,
 salary,
 date_paid_key "o_test(to_date(:date_paid_key, 'mm/dd/yyyy'))"
)

begindata
1,A,10,4/15/2009
2,B,20,4/18/2009

Finally, loading session and the result:

SQL> $sqlldr scott/tiger control=test.ctl log=test.log

SQL*Loader: Release 10.2.0.1.0 - Production on Pon Svi 18 21:33:15 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 1
Commit point reached - logical record count 2

SQL> select * from o_emp;

     EMPID EMPNAME                  SALARY DATE_PAID_KEY
---------- -------------------- ---------- -------------
         1 A                            10          1001
         2 B                            20          1004

SQL>

Report message to a moderator