• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3482
  • Last Modified:

PROBLEM WITH SQL LOADER

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
gunners1219
Asked:
gunners1219
  • 15
  • 5
  • 5
  • +2
1 Solution
 
sujith80Commented:
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
 
gunners1219Author Commented:
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
 
sujith80Commented:
give the command you use to invoke sql loader
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MarkusIdCommented:
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
 
gunners1219Author Commented:
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
 
MarkusIdCommented:
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
 
gunners1219Author Commented:
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
 
MarkusIdCommented:
Have you tried this with a blank between test_123 and the backslash?
0
 
gunners1219Author Commented:
No
0
 
MarkusIdCommented:
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
 
gunners1219Author Commented:
In the above i just added the an example
what i actually do is specify the dir and then file name
0
 
sujith80Commented:
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
 
gunners1219Author Commented:
PLEASE FIND BELOW THE EXACT CODE WHICH AM RUNNING
Sql-loader.txt
0
 
MarkusIdCommented:
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
 
MikeOM_DBACommented:

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
 
gunners1219Author Commented:
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
 
MikeOM_DBACommented:

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

0
 
gunners1219Author Commented:
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
 
MikeOM_DBACommented:

Sorry, my crystal ball is broken and I cannot see your sql loader log file, maybe you could post it?
0
 
gunners1219Author Commented:
Please find the attached logfile
Logfile.txt
0
 
gunners1219Author Commented:
Any solution for this issue guys?
0
 
gunners1219Author Commented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
gunners1219Author Commented:
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
 
MikeOM_DBACommented:

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
 
gunners1219Author Commented:
If i remove the postion it is just failing in the begining itself
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
gunners1219Author Commented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
gunners1219Author Commented:
Ok Thanks for your quick response  this shud solve my issue
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 15
  • 5
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now