Home » RDBMS Server » Server Utilities » About sql loader (Oracle 10g)
About sql loader [message #569433] Fri, 26 October 2012 05:13 Go to next message
spatava
Messages: 12
Registered: April 2012
Location: Pune india
Junior Member
Hi,
I am having query regarding sql loader.
my data file is comm(,) seperated and I want to load the whole file in oracle table 'bill_temp' except 1st column data of data file.
e.g.
File name: bill_file.dat
fields seperated by comma ','
values are like
emp_id,emp_name,emp_sal,join_date

oracle table bill_temp having the below column:
emp_name,emp_sal,join_date

Here I want load the emp_name,emp_sal and join_date into oracle table bill_temp.
emp_id should not get loaded into table.

Is there any way to skip the loading of particular column data from data file into table?
Re: About sql loader [message #569435 is a reply to message #569433] Fri, 26 October 2012 05:26 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

This is the sample code.. Use FILLER to skip the column..
LOAD DATA
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ',' ( field1, field2 FILLER, field3 )
Re: About sql loader [message #569436 is a reply to message #569433] Fri, 26 October 2012 05:28 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
Hi there,

There is an article in orafaq wiki about SQL*Loader options : SQL*Loader_FAQ
Quote:
... from Oracle 8i one can specify FILLER columns. FILLER columns are used to skip columns/fields in the load file, ignoring fields that one does not want. Look at this example: ...



Regards,
Dariyoosh
Re: About sql loader [message #569438 is a reply to message #569435] Fri, 26 October 2012 05:32 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Just sample code...Try like...
LOAD DATA
INFILE *
INSERT INTO TABLE TEST
Fields terminated by ";" Optionally enclosed by '"'
(
  ENAME,
  EMPNO FILLER,
  SAL 
)
BEGINDATA
"JONES";7566;3405
"BLAKE";7698;2980
"CLARK";7782;8000
"SCOTT";7788;3430
"KING";7839;6430
"JONES";7566;3405
"JONES";7566;3405
"BLAKE";7698;2980
"CLARK";7782;8000
"SCOTT";7788;3430
"KING";7839;6430
 

Re: About sql loader [message #569439 is a reply to message #569435] Fri, 26 October 2012 05:37 Go to previous messageGo to next message
spatava
Messages: 12
Registered: April 2012
Location: Pune india
Junior Member
Thanks Murali and Dariyoosh for your reply.

File is very large..
Below are the sample records:

emp_id,emp_name,emp_sal,join_date
102,Anthony,23000,31/03/2003
103,Mike,38000,01/08/2005
104,John,15000,08/02/2010
105,Phill,35000,19/12/2004

and I want to load data from 2nd field. i.e. EMP_ID (102,103,104,105) should not get loaded into table.
Please note that oracle table having 3 column like emp_name,emp_sal,join_date
Re: About sql loader [message #569444 is a reply to message #569439] Fri, 26 October 2012 05:52 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Try like..
LOAD DATA
INFILE *
INSERT INTO TABLE TEST
Fields terminated by "," 
(
  emp_name ,
  NULL FILLER,
  emp_sal,
  join_date DATE "DD/MM/YYYY"
)
BEGINDATA
102,Anthony,23000,31/03/2003
103,Mike,38000,01/08/2005
104,John,15000,08/02/2010
105,Phill,35000,19/12/2004 
Re: About sql loader [message #569446 is a reply to message #569444] Fri, 26 October 2012 06:07 Go to previous messageGo to next message
Michel Cadot
Messages: 58851
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NULL is a reserved word, use anything but a reserved word.

Regards
Michel
Re: About sql loader [message #569448 is a reply to message #569446] Fri, 26 October 2012 06:19 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

@Micheal, if we use reserved word what's problem.. Can you explain this one..
Re: About sql loader [message #569459 is a reply to message #569446] Fri, 26 October 2012 08:46 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1994
Registered: January 2010
Senior Member
Michel Cadot wrote on Fri, 26 October 2012 07:07
NULL is a reserved word, use anything but a reserved word.


I agree using NULL isn't a good idea for readability reasons, but NULL is not a reserved word in SQL*Loader.

SY.
Re: About sql loader [message #569460 is a reply to message #569448] Fri, 26 October 2012 09:14 Go to previous messageGo to next message
Michel Cadot
Messages: 58851
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
if we use reserved word what's problem..


Anyone can guess what is the problem of using of reserved word or a word that is part of the language for something that is different that its actual meaning.

Regards
Michel
Re: About sql loader [message #569465 is a reply to message #569439] Fri, 26 October 2012 12:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7966
Registered: November 2002
Location: California, USA
Senior Member
The FILLER keyword goes after the field that you want to skip loading, as in the demonstration below.

-- bill_file.dat:
emp_id,emp_name,emp_sal,join_date
102,Anthony,23000,31/03/2003
103,Mike,38000,01/08/2005
104,John,15000,08/02/2010
105,Phill,35000,19/12/2004


-- test.ctl:
OPTIONS (SKIP=1)
LOAD DATA
INFILE bill_file.dat
INTO TABLE bill_temp
FIELDS TERMINATED BY ','
( emp_id FILLER
, emp_name
, emp_sal
, join_date "TO_DATE (:join_date, 'DD/MM/YYYY')")


-- table:
SCOTT@orcl_11gR2> CREATE TABLE bill_temp
  2    (emp_name   VARCHAR2(8),
  3  	emp_sal    NUMBER,
  4  	join_date  DATE)
  5  /

Table created.


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

SQL*Loader: Release 11.2.0.1.0 - Production on Fri Oct 26 10:11:20 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 4


-- results:
SCOTT@orcl_11gR2> SELECT * FROM bill_temp
  2  /

EMP_NAME    EMP_SAL JOIN_DATE
-------- ---------- ---------
Anthony       23000 31-MAR-03
Mike          38000 01-AUG-05
John          15000 08-FEB-10
Phill         35000 19-DEC-04

4 rows selected.

Re: About sql loader [message #569516 is a reply to message #569465] Mon, 29 October 2012 00:51 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

@ Barbar ..Thanks for good explanation...
Re: About sql loader [message #569522 is a reply to message #569516] Mon, 29 October 2012 02:41 Go to previous message
spatava
Messages: 12
Registered: April 2012
Location: Pune india
Junior Member
It Works now..
Thanks all for resolving my query...
Smile
Previous Topic: Impdp - Packge Body Import taking Huge Time
Next Topic: Issue/failure in IMPDP
Goto Forum:
  


Current Time: Thu Aug 21 04:01:04 CDT 2014

Total time taken to generate the page: 0.08692 seconds