Home » RDBMS Server » Server Utilities » sql*loader
sql*loader [message #73115] Wed, 04 February 2004 06:11 Go to next message
laxman
Messages: 14
Registered: October 2000
Junior Member
One can skip columns by using FILLER columns ,ignoring columns that one does not want to load in ,but how can we skip some field that are present in the load file which has a delimited format.
eg. i have got a delimited file like

field1&#124field2&#124field3&#124field4&#124field5&#124field6

I want to load these fields in a table having 3 columns A,B,C such that
field1 goes into column A
field2 goes into column B
field5 goes into column C
How do I skip the fields 3 & 4
How do I go about this?
Re: sql*loader [message #73116 is a reply to message #73115] Wed, 04 February 2004 08:01 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
please have a look here
You can selectively remove a certain fields of the datafile.
if this does not help you..let us know..

[Updated on: Tue, 06 January 2009 11:08]

Report message to a moderator

Re: sql*loader [message #73124 is a reply to message #73116] Sat, 07 February 2004 02:30 Go to previous messageGo to next message
laxman
Messages: 14
Registered: October 2000
Junior Member
Thanks for the valuable reply mahesh.
We run the sql loader on windows2000.
Is there any utility in windows.
The scene is that we get a file having about 50 lakh records each with 100 fields. We have to process these records .The file has fields which we want as well as unwanted fields.We do not want to load the unwanted fields in our system at all.
The truncating of the unwanted fields by using OS utlities will take a lot of time.
Can they be directly skipped by the sql loader itself?
Re: sql*loader [message #73125 is a reply to message #73124] Sat, 07 February 2004 04:33 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Might be!.
we have some custom written perl/shell parser scripts which go through multiple files (each a few Gigabytes) and do the same job.
if you want to use sqlldr directly try this.
how about using arbitray column names in the control file for non existing columns in table?
--
-- this table has only 3 columns
--

mag@mutation_mutation > desc filler;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 COL1                                                           VARCHAR2(5)
 COL2                                                           VARCHAR2(5)
 COL3                                                           VARCHAR2(5)

mag@mutation_mutation > select * from filler;

no rows selected

mag@mutation_mutation > exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

--
-- this is the data file
-- it contains 10 fields.
-- and we want to load only 1,2 and 5 fields into col1,col2,col3
--
--

bash-2.03$ cat filler.dat
123,246,333,121,121,121,24d,zzz,777,xxx
123,246,333,121,121,121,24d,zzz,777,xxx
123,246,333,121,121,121,24d,zzz,yyy,xxx
123,246,333,121,121,121,24d,zzz,yyy,xxx

--
-- this is the control file
-- create dummy columns to match the number of fields in data file.
-- use original column names ( as in table) for the fields u want to load
-- for the rest dummy column names ( which dont exist in the table) use FILLER
--
bash-2.03$ cat filler.ctl
load data
infile 'filler.dat'
insert into table filler
fields terminated by ',' trailing nullcols
(
col1 ,
col2 ,
dummy_col3 FILLER,
dummy_col4 FILLER,
col3 ,
dummy_col6 FILLER,
dummy_col7 FILLER,
dummy_col8 FILLER,
dummy_col9 FILLER,
dummy_col10 FILLER
)

--
-- Lets load this and check
--

bash-2.03$ sqlldr userid=mag/mag control=filler.ctl

SQL*Loader: Release 9.2.0.1.0 - Production on Sat Feb 7 09:28:20 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 4
bash-2.03$ sqlplus mag/mag

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Feb 7 09:28:25 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

mag@mutation_mutation > select * from filler;

COL1  COL2  COL3
----- ----- -----
123   246   121
123   246   121
123   246   121
123   246   121

Re: sql*loader [message #73558 is a reply to message #73125] Fri, 21 May 2004 13:40 Go to previous message
Asalm
Messages: 6
Registered: May 2004
Junior Member
Hi

Culd you please help fro your experiences?

I'm trying to call SQLLoader (SQLLDR) from Perl and need to know how to specify the Oracle directories within the script and execute the call.

I've experimented with this, firstly Perl is not recognising the sqlldr command and secondly in the call it's getting confused with the use @dbname in the call ie.

system('sqlldr cntl = main.ctl userid=peter/pwd123@dbname');
Previous Topic: sql loader - insert records base on condition.
Next Topic: submitted job not running
Goto Forum:
  


Current Time: Thu Apr 15 03:26:03 CDT 2021