OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » About sql loader (Oracle 10g)

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

About sql loader [message #569433]

Fri, 26 October 2012 05:13

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?

Report message to a moderator

Re: About sql loader [message #569435 is a reply to message #569433]

Fri, 26 October 2012 05:26

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 )

Report message to a moderator

Re: About sql loader [message #569436 is a reply to message #569433]

Fri, 26 October 2012 05:28

dariyoosh
Messages: 538
Registered: March 2009
Location: 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

Report message to a moderator

Re: About sql loader [message #569438 is a reply to message #569435]

Fri, 26 October 2012 05:32

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

Report message to a moderator

Re: About sql loader [message #569439 is a reply to message #569435]

Fri, 26 October 2012 05:37

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

Report message to a moderator

Re: About sql loader [message #569444 is a reply to message #569439]

Fri, 26 October 2012 05:52

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

Report message to a moderator

Re: About sql loader [message #569446 is a reply to message #569444]

Fri, 26 October 2012 06:07

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

NULL is a reserved word, use anything but a reserved word.

Regards
Michel

Report message to a moderator

Re: About sql loader [message #569448 is a reply to message #569446]

Fri, 26 October 2012 06:19

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

@Micheal, if we use reserved word what's problem.. Can you explain this one..

Report message to a moderator

Re: About sql loader [message #569459 is a reply to message #569446]

Fri, 26 October 2012 08:46

Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA

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.

Report message to a moderator

Re: About sql loader [message #569460 is a reply to message #569448]

Fri, 26 October 2012 09:14

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: About sql loader [message #569465 is a reply to message #569439]

Fri, 26 October 2012 12:17

Barbara Boehmer
Messages: 9104
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.

Report message to a moderator