Home » RDBMS Server » Server Utilities » sql*loader
sql*loader [message #73115] |
Wed, 04 February 2004 06:11 |
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|field2|field3|field4|field5|field6
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 #73124 is a reply to message #73116] |
Sat, 07 February 2004 02:30 |
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 |
|
Mahesh Rajendran
Messages: 10708 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 |
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');
|
|
|
Goto Forum:
Current Time: Thu Dec 26 07:27:24 CST 2024
|