Home » RDBMS Server » Server Utilities » Sql Loader value needed from another table (Oracle 10g)
Sql Loader value needed from another table [message #519303] Wed, 10 August 2011 22:33 Go to next message
alvinng0618
Messages: 16
Registered: March 2011
Location: Malaysia
Junior Member
Hi Guys,

I have a file having 10k of rows and I need to use *sql loader to insert the data into table. Below are the information.
SQL> desc EMPLOYEE
 Name	  Type
EMP_ID    NUMBER(10)    -- PrimaryKey
EMP_NAME  VARCHAR2(30)
DEPT_ID  NUMBER(10)     -- ForeignKey from DEPARTMENT 

SQL> desc DEPARTMENT
 Name	  Type
DEPT_ID  NUMBER(10)
DEPT_NAME  VARCHAR2(30)

myFile.txt
------------
1,Edward,Account
2,Andrew,Finance
3,Sam, IT

CONTROL FILE (SQLLOADER)
------------
load data
infile myFile.txt
append into table EMPLOYEE
FIELDS TERMINATED BY ','
(EMP_ID,
EMP_NAME,
DEPT_ID  )   <--- ?? What should do in here

what should i do in this line because the value that i want is DEPT_ID but the file is giving the DEPART_NAME.
If there any sql statement can be used in control file?

Guys, please help me. Thank you.

Re: Sql Loader value needed from another table [message #519310 is a reply to message #519303] Thu, 11 August 2011 00:54 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Create a function that would return department number, and use it in a control file. As I don't have your tables, I created an example based on Scott's schema (I suppose you'll manage to get the idea):
SQL> create or replace function fun_deptno (par_dname in char)
  2    return number
  3  is
  4    retval number;
  5  begin
  6    select deptno
  7      into retval
  8      from dept
  9      where dname = upper(par_dname);
 10
 11    return (retval);
 12  exception
 13    when no_data_found then
 14      return null;
 15  end;
 16  /

Function created.

SQL>

A control file:
load data
infile *
append
into table emp
fields terminated by ','
  (empno,
   ename,
   deptno "fun_deptno(:deptno)"
  )
  
begindata
1,Edward,Accounting
2,Andrew,Research
3,Sam,xxx

Before loading session:
SQL> select deptno, empno, ename from emp order by empno;

    DEPTNO      EMPNO ENAME
---------- ---------- ----------
        20       7369 SMITH
        30       7499 ALLEN
        30       7521 WARD
        20       7566 JONES
        30       7654 MARTIN
        30       7698 BLAKE
        10       7782 CLARK
        20       7788 SCOTT
        10       7839 KING
        30       7844 TURNER
        20       7876 ADAMS
        30       7900 JAMES
        20       7902 FORD
        10       7934 MILLER

14 rows selected.

Execution and the result:
SQL> $sqlldr scott/tiger@ora10 control=test12.ctl log=test12.log

SQL*Loader: Release 10.2.0.1.0 - Production on ╚et Kol 11 07:53:40 2011

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

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

SQL> select deptno, empno, ename from emp order by empno;

    DEPTNO      EMPNO ENAME
---------- ---------- ----------
        10          1 Edward
        20          2 Andrew
                    3 Sam
        20       7369 SMITH
        30       7499 ALLEN
        30       7521 WARD
        20       7566 JONES
        30       7654 MARTIN
        30       7698 BLAKE
        10       7782 CLARK
        20       7788 SCOTT
        10       7839 KING
        30       7844 TURNER
        20       7876 ADAMS
        30       7900 JAMES
        20       7902 FORD
        10       7934 MILLER

17 rows selected.

SQL>
Re: Sql Loader value needed from another table [message #519326 is a reply to message #519310] Thu, 11 August 2011 02:01 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

Hi Littlefoot,

I think there is very small mistake in the code, Sorry I m not point at your talent but may due to negligence:

Check this out.

First the function id created successfully no mistake:

SQL> create or replace function fun_deptno (par_dname in char)
  2    return number
  3  is
  4    retval number;
  5  begin
  6    select deptno
  7      into retval
  8      from dept
  9      where dname = upper(par_dname);
 10
 11    return (retval);
 12  exception
 13    when no_data_found then
 14      return null;
 15  end;
 16  /

Function created.


Then comes the part where the correction is to be made: CONTROL FILE

Your Code
load data
infile *
append
into table emp
fields terminated by ','
  (empno,
   ename,
   deptno "fun_deptno(:deptno)"
  )
  
begindata
1,Edward,Accounting
2,Andrew,Research
3,Sam,xxx


Correct code:
While using the function value we have to pass the variable returning value not the column name.


deptno "fun_deptno(:retval)"



Correct me if I am also wrong.

Thanks n Regards
Deepak

[Updated on: Thu, 11 August 2011 02:02]

Report message to a moderator

Re: Sql Loader value needed from another table [message #519327 is a reply to message #519326] Thu, 11 August 2011 02:02 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You are wrong.
Re: Sql Loader value needed from another table [message #519328 is a reply to message #519327] Thu, 11 August 2011 02:11 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

Got it, it was due to the similar variables used by me as column name in the tables and the in the function created.

Is it like the variable which you used while calling function in the column name from the dept table which contains the deptno and dept_name. Right!

Regards
Deepak
Re: Sql Loader value needed from another table [message #519331 is a reply to message #519328] Thu, 11 August 2011 02:34 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
deptno "fun_deptno(:deptno)"
   ^                  
   |    
This DEPTNO is a table column (EMP.DEPTNO)

deptno "fun_deptno(:deptno)"
                      ^
                      |
We are passing DEPTNO value from an input file, i.e. it is a value that represents the DEPTNO.
However, as the input file contains DNAME (such as "Accounting"), we are - really - passing
DNAME to the function.
Colon in front of the parameter name (:DEPTNO) means that actual value from the input file is
used ("Accounting").

Re: Sql Loader value needed from another table [message #519336 is a reply to message #519331] Thu, 11 August 2011 02:40 Go to previous message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member

Got it!

Thanks
Deepak
Previous Topic: sqlloader
Next Topic: Data Pump and resumable_timeout
Goto Forum:
  


Current Time: Thu Mar 28 08:08:45 CDT 2024