Solved

PROBLEM WITH SQL LOADER

Posted on 2008-10-30
34
2,640 Views
Last Modified: 2013-12-19
THIS IS THE CTL FILE AM USING

LOAD DATA
replace
INTO TABLE XYZ
TRAILING NULLCOLS
(
    ID     POSITION( 1:9)    
  , EMAIL_TX         POSITION(10)    TERMINATED BY WHITESPACE
  , DT           DATE "MM/DD/YYYY"
  )

THIS IS LOADING THE RECORDS PROPERLY INTO TABLE XYZ

BUT THIS GIVES AN ERROR

LRM-00112: multiple values not allowed for parameter 'data'

hOW TO GET RID OF THIS ERROR  AS THIS CAUSES OTHER JOBS AFTER SQLLOADER NT TO RUN
THE JOB JUST QUITS AFTER SQLLOADER

BELOW IS ATTACHED SAMPLE TEXT FILE WHICH AM LOADING

THANKS IN ADVANCE
test-123.txt
0
Comment
Question by:gunners1219
  • 15
  • 5
  • 5
  • +2
34 Comments
 
LVL 27

Expert Comment

by:sujith80
ID: 22847327
Looks like you have tab delimited data, Try to use this control file.
Are you passing the right file name to sql loader? (I didnt see an infile clause in the control file)



LOAD DATA

replace

INTO TABLE XYZ

fields terminated by '\t' optionally enclosed by '"'

TRAILING NULLCOLS

(

    ID         

  , EMAIL_TX         

  , DT           DATE "MM/DD/YYYY"

  )

Open in new window

0
 

Author Comment

by:gunners1219
ID: 22848165
THE FILE IS NT TAB DELIMITED
AM PASSING THE RGT FILE NAME TO LOADER
AND THE FILE GETS LOADED INTO TABLE WITH THE CTLFILE I MENTIONED ABOVE
BUT GIVES THE ERROR CODE AS MENTIONED
0
 
LVL 27

Expert Comment

by:sujith80
ID: 22848204
give the command you use to invoke sql loader
0
 
LVL 9

Expert Comment

by:MarkusId
ID: 22848206
Seems to be a command line issue - how do you invoke the loader?

Do you name your file like the file you attached (with test-123.txt)? This might cause the problem, I donÄ't know how the loader will do with the hyphen between test and 123
0
 

Author Comment

by:gunners1219
ID: 22848928
I INVOKE THE LOADER IN A SHELL SCRIPT

THE NAME TEXT HYPEN 123 IS JUST THE EXAMPLE THAT IS NT THE ORGINAL NAME OF THE FILE
0
 
LVL 9

Expert Comment

by:MarkusId
ID: 22848958
Would you mind telling us the line? The error message indicates that the data-parameter is given twice on the command-line, but this doesn't have to be though, maybe it's a filename with a blank or something like that.
0
 

Author Comment

by:gunners1219
ID: 22849385
below is the script i run

CTLFILE=tester_123.ctl
LOGFILE=tester_123log
BADFILE=tester_123.bad
DISCFILE=tester_123.dsc

sqlldr   control=$CTLFILE data=test_123\
log=$LOGFILE bad=$BADFILE discard=$DISCFILE \
userid=XXXX\
rows=10000 errors=99999 silent=all
the above script runs but it is giving me the error code at line above
log=$LOGFILE bad=$BADFILE discard=$DISCFILE \
0
 
LVL 9

Expert Comment

by:MarkusId
ID: 22849441
Have you tried this with a blank between test_123 and the backslash?
0
 

Author Comment

by:gunners1219
ID: 22849548
No
0
 
LVL 9

Expert Comment

by:MarkusId
ID: 22849624
Then do it please, could be possible, that, due to the backslash right next to the filename, the log= ist added to the data-parameter as well.
0
 

Author Comment

by:gunners1219
ID: 22849659
In the above i just added the an example
what i actually do is specify the dir and then file name
0
 
LVL 27

Expert Comment

by:sujith80
ID: 22851303
from the example above it looks like there is no space between the file name and the \.

FYI, the more info and accurate info you provide helps the experts to comment on your problem better. You may get the resolution faster.
0
 

Author Comment

by:gunners1219
ID: 22851469
PLEASE FIND BELOW THE EXACT CODE WHICH AM RUNNING
Sql-loader.txt
0
 
LVL 9

Expert Comment

by:MarkusId
ID: 22851973
The command line looks fine, at least in Windows on one line (unfortunately I don't have a unix envirnoment at hand at the moment), so it may be a problem of either the separation into several lines or with the name of the data file itself.

What I tried:
sqlldr control=test.ctl data=%verzeichnis%/%datei% log=test.log bad=test.bad discard=test.dis userid=%USERNAME%  rows=10000 errors=99999 silent=all

0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 22852783

Your controlfile should look EXACTLY like this:

http://www.experts-exchange.com/Database/Oracle/10.x/Q_23859531.html?cid=748#a22850819

LOAD DATA

REPLACE

INTO TABLE xxxx

FIELDS TERMINATED BY " " TRAILING NULLCOLS

-- This is a TAB -----^

(

    ID

  , EMAIL_TX

  , DT   DATE "MM/DD/YYYY"

)

Open in new window

0
 

Author Comment

by:gunners1219
ID: 22852834
Illegal combination of non-alphanumeric characters
#LOAD DATA

This is the error i get when i run the same the control file provided  above
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 22853655

Please post all output from the sql loader log and a copy of your controlfile.

0
 

Author Comment

by:gunners1219
ID: 22853692
WHEN I USE THE THE CONTROL FILE WITH NO POSITION THE LOG FILE SHOWS RECORDS INSERTED =0
WHEN I USE THE CONTROL FILE WITH FIXED POSITION ALL THE RECORDS ARE LOADING IN THE TABLE BUT GIVES ME THE ERRORCODE AND QUITS FROM THE SCRIPT WITHOUT EXECUTING THE REST
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 22892030

Sorry, my crystal ball is broken and I cannot see your sql loader log file, maybe you could post it?
0
 

Author Comment

by:gunners1219
ID: 22894908
Please find the attached logfile
Logfile.txt
0
 

Author Comment

by:gunners1219
ID: 23151861
Any solution for this issue guys?
0
 

Author Comment

by:gunners1219
ID: 23152761
can somebody please answer this question this shud solve my problem
If the data file has headers for the colums in the file how is it going to work?

0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 23312524
Hi,

i can see the data file which you are trying to load "test-123.txt"

i can see the ksh file which which invokes sqlldr "Sql-loader.txt"

i can also see the sql ldr log file "Logfile.txt"

BUT where is the control file "tester_123.ctl" ? can you please attach that as a file here.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 23312533
1) Also your log file "Logfile.txt" shows that you are using "test.ctl" as the ctl file and not "tester_123.ctl" ? --> can you please confirm ????

2) Also i can see the below from "Logfile.txt" meaning only 2 records are rejected. So what is the acutal issue now ????

  1261 Rows successfully loaded.
  2 Rows not loaded due to data errors.
0
 

Author Comment

by:gunners1219
ID: 23316038
Thanks for the response
the ctl file am using is
LOAD DATA
REPLACE
INTO TABLE xyz
TRAILING NULLCOLS
(
    ID     POSITION( 1:9)
   , EMAIL     POSITION(11)   TERMINATED BY WHITESPACE  
   , DT    DATE  "MM/DD/YYYY"
   , RCE    CONSTANT 0
  , STATUS  CONSTANT "P"
)
In the real data file there is a header for it
If i remove the header the process is running fine but i cant manually remove the header each time
As i want to automate the process.as of now am running it maually so it is working fine but once it is automated it definately fails.
So can you please suggest me how to over the issue
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 23318817

As per my post #22852783, you should have modified your control file to REMOVE the "POSITION" options.

Try this control file:

LOAD DATA

REPLACE

INTO TABLE xyz

fields terminated by '\t' optionally enclosed by '"'

TRAILING NULLCOLS

(

     ID

   , EMAIL    

   , DT     DATE  "MM/DD/YYYY" 

   , RCE    CONSTANT 0

  , STATUS  CONSTANT "P"

)

Open in new window

0
 

Author Comment

by:gunners1219
ID: 23318840
If i remove the postion it is just failing in the begining itself
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 23322239
you can very well skip the header records which you do not want to load with the SKIP parameter.

you have to specify skip=n when you invoke sqlldr in yourcase it will be skip=1 because you want to skip 1 record which is the header

see the below link :

http://www.orafaq.com/wiki/SQL*Loader_FAQ#Can_one_skip_header_records_while_loading.3F

0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 23322256
i mean, you can just try with your current control file which is giving error when trying to load header but just include this SKIP parameter as well....
0
 

Author Comment

by:gunners1219
ID: 23322360
yaa i used skip  in the command line this morning and it was working fine
So is that  the only solution to overcome it..?
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 23322411
yes.. that is the reason why we have SKIP parameter/option of sql loader. I have used this option for a similar case in my experience and also seen others using it for the same.

But i cannot tell you whether this is the only solution because there are a variety of answers for each question as you might be knowing... but Definitely one answer is SKIP option

Ok...apart from the above, i can think of WHEN clause in the sql loader control file where you can give a condition to say that you only if that condition is met, load the record otherwise discard them.

so if your header is always going to have first record with field name and that field name is also going to remain the same ...

WHEN (ID  <> 'FIELD1')

in the above example, i am trying to say load only those records which have the first column/field which are not having a value 'FIELD1'. in your case i assumed that your header record will have something like FIELD1 for the first column of data....
0
 

Author Comment

by:gunners1219
ID: 23322427
Ok Thanks for your quick response  this shud solve my issue
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now