Home » RDBMS Server » Server Utilities » Merge two control files
Merge two control files [message #554493] Mon, 14 May 2012 19:03 Go to next message
namitanamburi
Messages: 35
Registered: March 2009
Member
Hello,

1)Is there a way to load two tables from two input files in one control file?

I have two control files.


LOAD DATA
INFILE 'C:\name.txt'
BADFILE 'C:\name.bad'
DISCARDFILE 'C:\name.dsc'
APPEND
INTO emp_tab
fields terminated by ";"
TRAILING NULLCOLS
( Emp_id ,
name,
age)



LOAD DATA
INFILE 'C:\job.txt'
BADFILE 'C:\job.bad'
DISCARDFILE 'C:\job.dsc'
APPEND
INTO job_tab
fields terminated by ";"
TRAILING NULLCOLS
( job_id ,
title,
salary)




Can i load emp_tab and job_table using one control file and two input files name.txt and job.txt?

2)Is there a way to pass the path as a parameter in the control file?

In the job below, when i execute the sqlldr , can I pass C:/job.txt as an input instead of specifying it in control file?


Thanks
Nammu


Re: Merge two control files [message #554495 is a reply to message #554493] Mon, 14 May 2012 19:21 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>2)Is there a way to pass the path as a parameter in the control file?
control file is a plain static text file; so the short answer is "NO!"
Re: Merge two control files [message #554499 is a reply to message #554493] Mon, 14 May 2012 23:53 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Quote:


1)Is there a way to load two tables from two input files in one control file?


I have to wonder why, but yes, it can be done as demonstrated below. However, you have to have something that can be used in a WHEN clause to determine which table the data should be loaded into, unless the data for one table is of different types and lengths that would be rejected for the other table.

-- name.txt:
E;7369;SMITH;31
E;7499;ALLEN;31
E;7521;WARD;31
E;7566;JONES;31
E;7654;MARTIN;30
E;7698;BLAKE;31
E;7782;CLARK;30
E;7788;SCOTT;29
E;7839;KING;30
E;7844;TURNER;30
E;7876;ADAMS;29
E;7900;JAMES;30
E;7902;FORD;30
E;7934;MILLER;30


-- job.txt:
J;20;CLERK;800
J;30;SALESMAN;1600
J;30;SALESMAN;1250
J;20;MANAGER;2975
J;30;SALESMAN;1250
J;30;MANAGER;2850
J;10;MANAGER;2450
J;20;ANALYST;3000
J;10;PRESIDENT;5000
J;30;SALESMAN;1500
J;20;CLERK;1100
J;30;CLERK;950
J;20;ANALYST;3000
J;10;CLERK;1300


-- test.ctl:
LOAD DATA
INFILE 'name.txt' BADFILE 'name.bad' DISCARDFILE 'name.dsc'
INFILE 'job.txt' BADFILE 'job.bad' DISCARDFILE 'job.dsc'
APPEND INTO TABLE emp_tab WHEN whichtable='E'
FIELDS TERMINATED BY ";" TRAILING NULLCOLS
(whichtable FILLER, emp_id, name, age)
INTO TABLE job_tab WHEN whichtable='J'
FIELDS TERMINATED BY ";" TRAILING NULLCOLS
(whichtable FILLER POSITION(1), job_id, title, salary)


-- tables are empty:
SCOTT@orcl_11gR2> SELECT COUNT(*) FROM emp_tab
  2  /

  COUNT(*)
----------
         0

1 row selected.

SCOTT@orcl_11gR2> SELECT COUNT(*) from job_tab
  2  /

  COUNT(*)
----------
         0

1 row selected.


-- load and results:
SCOTT@orcl_11gR2> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log

SCOTT@orcl_11gR2> SELECT * FROM emp_tab
  2  /

    EMP_ID NAME              AGE
---------- ---------- ----------
      7369 SMITH              31
      7499 ALLEN              31
      7521 WARD               31
      7566 JONES              31
      7654 MARTIN             30
      7698 BLAKE              31
      7782 CLARK              30
      7788 SCOTT              29
      7839 KING               30
      7844 TURNER             30
      7876 ADAMS              29
      7900 JAMES              30
      7902 FORD               30
      7934 MILLER             30

14 rows selected.

SCOTT@orcl_11gR2> SELECT * FROM job_tab
  2  /

    JOB_ID TITLE         SALARY
---------- --------- ----------
        20 CLERK            800
        30 SALESMAN        1600
        30 SALESMAN        1250
        20 MANAGER         2975
        30 SALESMAN        1250
        30 MANAGER         2850
        10 MANAGER         2450
        20 ANALYST         3000
        10 PRESIDENT       5000
        30 SALESMAN        1500
        20 CLERK           1100
        30 CLERK            950
        20 ANALYST         3000
        10 CLERK           1300

14 rows selected.

Re: Merge two control files [message #554500 is a reply to message #554493] Tue, 15 May 2012 00:03 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Quote:


can I pass C:/job.txt as an input instead of specifying it in control file?


Yes, you could use a control file and two command lines as shown below.

-- control file:
LOAD DATA
APPEND INTO TABLE emp_tab WHEN whichtable='E'
FIELDS TERMINATED BY ";" TRAILING NULLCOLS
(whichtable FILLER, emp_id, name, age)
INTO TABLE job_tab WHEN whichtable='J'
FIELDS TERMINATED BY ";" TRAILING NULLCOLS
(whichtable FILLER POSITION(1), job_id, title, salary)


-- command lines:
HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test1.log DATA='name.txt'
HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test2.log DATA='job.txt'

Previous Topic: impdp getting grants and privs
Next Topic: oracle metalink
Goto Forum:
  


Current Time: Thu Mar 28 10:05:29 CDT 2024