?
Solved

error while running sqlloader:

Posted on 2007-08-09
15
Medium Priority
?
1,392 Views
Last Modified: 2013-12-19
When i run sqlloader on sqlplus9.2, i get the following error:

SQLLDR USERID=xyz\xyz,CONTROL=C:\loaddata\item_maint.ctl,DATA=C:\loaddata\item_maint.dat,LOG=C:\loaddata\item_maint.log,BAD=C:\loaddata\item_maint.bad,DISCARD=C:\loaddata\item_maint.dsc;
or
SQLLDR USERID=xyz\xyz,CONTROL=item_maint.ctl,DATA=item_maint.dat,LOG=item_maint.log,BAD=item_maint.bad,DISCARD=item_maint.dsc;


tdev>SQLLDR USERID=xya\xyz,CONTROL=C:\loaddata\item_maint.ctl,
DATA=C:\loaddata\item_maint.dat,
LOG=C:\loaddata\item_maint.log,
BAD=C:\loaddata\item_maint.bad,
DISCARD=C:\loaddata\item_maint.dsc;
SP2-0734: unknown command beginning "SQLLDR USE..." - rest of line ignored.

Seems sqlldr utility is not loaded into sqlplus.

How can i run sqlldr without this error?
0
Comment
Question by:gram77
  • 7
  • 5
  • 2
  • +1
15 Comments
 
LVL 12

Accepted Solution

by:
jwahl earned 1600 total points
ID: 19661786
you have to start sqlldr from os.
inside sqlplus you can start external commands only by using the HOST command ...
0
 

Author Comment

by:gram77
ID: 19662200
I have all my data in Oracle database, accessed through SqlPlus.
And I have the datafile in Unix.

I have ftp'ed the datafile to Windows XP, where i have installed the Oracle client SqlPlus9.2.

Now how do i run sqlldr in windows OS without using SqlPlus. How do i do it?

Do i use sqlldr from Start->Run dialog box and enter sqlldr command there?

0
 
LVL 12

Expert Comment

by:jwahl
ID: 19662209
yes.
0
Technology Partners: 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!

 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 19662356
SQL*Loader works best and most efficiently when it is run on the server.  If you want to run SQL*Loader from a client machine (it will be slower!) you can do that if:
1. the Oracle client version is the same version of Oracle as the database
2. you start it from the O/S, so in Windows, this means you need to open a DOS (cmd) window to run it from.
0
 

Author Comment

by:gram77
ID: 19662595
column REL_DTE data rejected:
Here is the output:

Sql Loader Output:

SQL*Loader: Release 9.2.0.1.0 - Production on Thu Aug 9 19:41:39 2007

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

Control File:   C:\loaddata\item_maint.ctl
Data File:      C:\loaddata\item_maint.dat
  Bad File:     C:\loaddata\item_maint.bad
  Discard File: C:\loaddata\item_maint.dsc;
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table XXINV_ITEM_MAINFRAME, loaded from every logical record.
Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EAN                                 FIRST     *   |  O(") CHARACTER            
STS                                  NEXT     *   |  O(") CHARACTER            
ITM                                  NEXT     *   |  O(") CHARACTER            
ART                                  NEXT     *   |  O(") CHARACTER            
TTL                                  NEXT     *   |  O(") CHARACTER            
MER_CDE                              NEXT     *   |  O(") CHARACTER            
PRD_LNE                              NEXT     *   |  O(") CHARACTER            
CLS_CDE                              NEXT     *   |  O(") CHARACTER            
CTG_IND                              NEXT     *   |  O(") CHARACTER            
FOR_CDE                              NEXT     *   |  O(") CHARACTER            
SHP_CDE                              NEXT     *   |  O(") CHARACTER            
CAT                                  NEXT     *   |  O(") CHARACTER            
SEG                                  NEXT     *   |  O(") CHARACTER            
SUB_SEG                              NEXT     *   |  O(") CHARACTER            
MER_TYP                              NEXT     *   |  O(") CHARACTER            
VND                                  NEXT     *   |  O(") CHARACTER            
MFG_LBL                              NEXT     *   |  O(") CHARACTER            
MFG_PFX                              NEXT     *   |  O(") CHARACTER            
MFG_SFX                              NEXT     *   |  O(") CHARACTER            
BSE_COS                              NEXT     *   |  O(") CHARACTER            
VND_MSL                              NEXT     *   |  O(") CHARACTER            
MSL                                  NEXT     *   |  O(") CHARACTER            
OSP_FLG                              NEXT     *   |  O(") CHARACTER            
BOX_LOT                              NEXT     *   |  O(") CHARACTER            
ITM_VOL_FCT                          NEXT     *   |  O(") CHARACTER            
WGT                                  NEXT     *   |  O(") CHARACTER            
HGT                                  NEXT     *   |  O(") CHARACTER            
WDT                                  NEXT     *   |  O(") CHARACTER            
DPH                                  NEXT     *   |  O(") CHARACTER            
CLM_SHL_FLG                          NEXT     *   |  O(") CHARACTER            
REL_DTE                              NEXT     *   |  O(") CHARACTER            
ITM_RTN_POL                          NEXT     *   |  O(") CHARACTER            
LAS_REQ_RTN_DTE                      NEXT     *   |  O(") CHARACTER            
CLL_BCK_DTE                          NEXT     *   |  O(") CHARACTER            
MNT_CDE                              NEXT     *   |  O(") CHARACTER            
CUR_DEC                              NEXT     *   |  O(") CHARACTER            
LAST_UPDATE_DATE                     NEXT     *   |  O(") CHARACTER            
LAST_UPDATED_BY                      NEXT     *   |  O(") CHARACTER            
CREATED_BY                           NEXT     *   |  O(") CHARACTER            
CREATION_DATE                        NEXT     *   |  O(") CHARACTER            
HDL_ORGANIZATION                     NEXT     *   |  O(") CHARACTER            
VEW_RTE_CDE                          NEXT     *   |  O(") CHARACTER            
ORGANIZATION_ID                      NEXT     *   |  O(") CHARACTER            
COU                                  NEXT     *   |  O(") CHARACTER            
COR                                  NEXT     *   |  O(") CHARACTER            
INA_DTE                              NEXT     *   |  O(") CHARACTER            
REA_DTE                              NEXT     *   |  O(") CHARACTER            

Record 1: Rejected - Error on table xyz, column REL_DTE.
ORA-01861: literal does not match format string

Record 2: Rejected - Error on table xyz, column REL_DTE.
ORA-01861: literal does not match format string
..
..
..

******************************************************

Sample Data:
0096741168826|A|0200001|V/A XMAS                      |CHRISTMAS CRONER              |  |01|002|Y|0|C |280|2000|2030|4000|12479|HDL|CD   |NA     |00005.25|00012.98|009.93|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20070801|RR|        |        |A|R|20070521|2725|2725|20070521|A|  |084|1|001|        |        |

0096741169021|A|0200002|V/A CNTRY XMAS                |CHRISTMAS                     |  |01|002|Y|0|C |280|0300|0330|4000|12479|HDL|CD   |NA     |00005.25|00012.98|009.93|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20070801|RR|        |        |A|R|20070521|2725|2725|20070521|A|  |084|1|001|        |        |

0049656513472|A|0200003|V/A CHRISTMAS                 |XM07397                       |  |01|002|Y|0|C |280|3000|2030|6000|02001|HDL|397  |CD     |00000.90|00004.98|003.97|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20070307|NR|        |        |A|R|20070502|2725|2725|20070502|A|  |084|1|001|        |        |


REL_DTE is 17 column from back.
Example of rejected data: RelDate:20070801


******************************************************
The table structure is:
CREATE TABLE xyz
(EAN                  VARCHAR2(40)      NOT NULL,
STS                  VARCHAR2(10)      NOT NULL,
ITM                  VARCHAR2(25)      NOT NULL,
ART                  VARCHAR2(30)      NOT NULL,       
TTL                  VARCHAR2(30)      NOT NULL,
MER_CDE                  VARCHAR2(30)      NOT NULL,
PRD_LNE                  VARCHAR2(40)      NOT NULL,
CLS_CDE                  VARCHAR2(40)      NOT NULL,
CTG_IND                  VARCHAR2(40)      NOT NULL,
FOR_CDE                  VARCHAR2(40)      NOT NULL,
SHP_CDE                  VARCHAR2(40)      NOT NULL,
CAT                  VARCHAR2(40)      NOT NULL,
SEG                  VARCHAR2(40)      NOT NULL,
SUB_SEG                  VARCHAR2(40)      NOT NULL,
MER_TYP                  VARCHAR2(40)      NOT NULL,
VND                  VARCHAR2(40)      NOT NULL,
MFG_LBL                  VARCHAR2(40)      NOT NULL,
MFG_PFX                  VARCHAR2(25)      NOT NULL,
MFG_SFX                  VARCHAR2(25)      NOT NULL,
BSE_COS                  NUMBER            NOT NULL,
VND_MSL                  VARCHAR2(40)      NOT NULL,
MSL                  VARCHAR2(40)      NOT NULL,
OSP_FLG                  VARCHAR2(40)      NOT NULL,
BOX_LOT                  NUMBER            NOT NULL,
ITM_VOL_FCT            NUMBER            NOT NULL,
WGT                  NUMBER            NOT NULL,
HGT                  NUMBER            NOT NULL,
WDT                  NUMBER            NOT NULL,
DPH                  NUMBER            NOT NULL,
CLM_SHL_FLG            VARCHAR2(40)      NOT NULL,
REL_DTE                  DATE            NOT NULL,      
ITM_RTN_POL            VARCHAR2(240)      NOT NULL,
LAS_REQ_RTN_DTE            DATE,      
CLL_BCK_DTE            DATE,      
MNT_CDE                  VARCHAR2(1)      NOT NULL,
CUR_DEC                  VARCHAR2(40)      NOT NULL,
LAST_UPDATE_DATE      DATE,      
LAST_UPDATED_BY            NUMBER,      
CREATED_BY             NUMBER,      
CREATION_DATE             DATE,      
HDL_ORGANIZATION       VARCHAR2(240)      NOT NULL,
VEW_RTE_CDE            VARCHAR2(10)      NOT NULL,
ORGANIZATION_ID            NUMBER            NOT NULL,
COU                  VARCHAR2(1)      NOT NULL,
COR                  VARCHAR2(3)      NOT NULL,
INA_DTE                  DATE,
REA_DTE                  DATE,
CONSTRAINT xyz_PK PRIMARY KEY (COU,COR,ITM));

Q. How do i modify the sqlldr data so that I get data: 20070801 entered into the data column REL_DTE?
0
 
LVL 32

Expert Comment

by:awking00
ID: 19662726
You need to use the to_date function in the control file
REL_DTE                              NEXT     *   |  O(") CHARACTER
"to_date(:REL_DATE,'YYYYMMDD')",

0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 400 total points
ID: 19662743
Sorry, added an extra letter -
REL_DTE                              NEXT     *   |  O(") CHARACTER
"to_date(:REL_DTE,'YYYYMMDD')",


0
 

Author Comment

by:gram77
ID: 19662927
control file entry:
LOAD DATA
INFILE 'C:\loaddata\XXINV.dat'
INTO TABLE xyz
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'              
(EAN,STS,ITM,ART,TTL,MER_CDE,PRD_LNE,CLS_CDE,CTG_IND,FOR_CDE,SHP_CDE,CAT,SEG,SUB_SEG,MER_TYP,VND,MFG_LBL,MFG_PFX,MFG_SFX,
BSE_COS,VND_MSL,MSL,OSP_FLG,BOX_LOT,ITM_VOL_FCT,WGT,HGT,WDT,DPH,CLM_SHL_FLG,REL_DTE,ITM_RTN_POL,LAS_REQ_RTN_DTE,CLL_BCK_DTE,      
MNT_CDE,CUR_DEC,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATED_BY,CREATION_DATE,HDL_ORGANIZATION,VEW_RTE_CDE,ORGANIZATION_ID,
COU,COR,INA_DTE,REA_DTE)

How do i provide datatype to induvidual fields like that to REL_DTE "to_date(:REL_DTE,'YYYYMMDD')" in this control file
0
 

Author Comment

by:gram77
ID: 19662940
What will be the entry for varchar2, number and date datatypes
0
 
LVL 32

Expert Comment

by:awking00
ID: 19663228
You don't need to use anything differently for varchar2 or number because they will be implicitly cast correctly (assuming there are no non-numeric characters in the number fields) just as you wouldn't have had to use the to_date function if your date were in the format of say, 01-AUG-07, because that format will also implicitly cast in Oracle. You will need to apply the to_date() function to all of the fields that are of date datatype in your table.
Assuming those fields end in the "DTE" or contain the word "DATE" it would be like this -
INFILE 'C:\loaddata\XXINV.dat'
INTO TABLE xyz
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'              
(EAN,STS,ITM,ART,TTL,MER_CDE,PRD_LNE,CLS_CDE,CTG_IND,FOR_CDE,SHP_CDE,CAT,SEG,SUB_SEG,MER_TYP,VND,MFG_LBL,MFG_PFX,MFG_SFX,
BSE_COS,VND_MSL,MSL,OSP_FLG,BOX_LOT,ITM_VOL_FCT,WGT,HGT,WDT,DPH,CLM_SHL_FLG
,REL_DTE "to_date(:REL_DTE,'YYYYMMDD')"
,ITM_RTN_POL
,LAS_REQ_RTN_DTE"to_date(:LAS_REQ_RTN_DTE,'YYYYMMDD')"
,CLL_BCK_DTE"to_date(:CLL_BCK_DTE,'YYYYMMDD')"
,MNT_CDE,CUR_DEC
,LAST_UPDATE_DATE"to_date(:LAST_UPDATE_DATE,'YYYYMMDD')"
,etc.
0
 
LVL 32

Expert Comment

by:awking00
ID: 19663258
A little too much copying and pasting. There needs to be a space between the attribute name and the function declaration. For example, LAS_REQ_TRN_DTE"to_date(... needs to be
LAS_REQ_TRN_DTE "to_date(...
                              ^
                     Space here
0
 

Author Comment

by:gram77
ID: 19667886
and now an error due to Null column.

How do i accomodate Null columns in the data?

Record 1: Rejected - Error on table XYZ.
ORA-01400: cannot insert NULL into ("XYZ"."MER_CDE")

Record 2: Rejected - Error on table XYZ.
ORA-01400: cannot insert NULL into ("XYZ"."MER_CDE")

Record 3: Rejected - Error on table XYZ.
ORA-01400: cannot insert NULL into ("XYZ"."MER_CDE")

Data:
0096741168826|A|0200001|A XMAS                |CHR CROO                    |  |01|002|Y|0|C |280|2000|2030|4000|12479|LDL|CD   |NA     |00005.25|00012.98|009.93|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20070801|RR|        |        |A|R|20070521|2725|2725|20070521|A|  |084|1|001|        |        |

0096741169021|A|0200002|C XMAS                |COU CHR VOL                 |  |01|002|Y|0|C |280|0300|0330|4000|12479|LDL|CD   |NA     |00005.25|00012.98|009.93|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20070801|RR|        |        |A|R|20070521|2725|2725|20070521|A|  |084|1|001|        |        |

0049656513472|A|0200003|C                     |XM073                       |  |01|002|Y|0|C |280|3000|2030|6000|02001|LDL|397  |CD     |00000.90|00004.98|003.97|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20070307|NR|        |        |A|R|20070502|2725|2725|20070502|A|  |084|1|001|        |        |

0049656513489|A|0200004|C                     |XM076                       |  |01|002|Y|0|C |280|3000|2030|6000|02001|LDL|687  |CD     |00002.75|00006.98|006.92|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20070307|NR|        |        |A|R|20070502|2725|2725|20070502|A|  |084|1|001|        |        |

0018111770422|A|0200005|C                     |FROSTY                      |  |01|002|Y|0|C |280|0700|0730|6000|04630|LDL|18111|76104  |00003.30|00007.99|006.93|N|030|001.2|00000000.50000|05.000|05.500|00.500|N|20070529|RR|        |        |A|D|20070502|2725|2725|20070502|A|  |084|1|001|        |        |


MER_CDE is 6th column from right.

0
 

Author Comment

by:gram77
ID: 19668027
while creating the table, the following columns shown under have been declared as not null.
While in the data in these columns may have a null value. For Example MER_CODE column has null values in the sample data shown below


MER_CDE                  VARCHAR2(30)      NOT NULL, --value null
PRD_LNE                  VARCHAR2(40)      NOT NULL,
CLS_CDE                  VARCHAR2(40)      NOT NULL,
CTG_IND                  VARCHAR2(40)      NOT NULL,
FOR_CDE                  VARCHAR2(40)      NOT NULL,
SHP_CDE                  VARCHAR2(40)      NOT NULL,
0
 
LVL 32

Expert Comment

by:awking00
ID: 19669824
To accommodate null values in the data, you need to alter the table -
ALTER TABLE xyz
MODIFY(MER_CDE    VARCHAR2(30)      NULL,
PRD_LNE                  VARCHAR2(40)      NULL,
CLS_CDE                  VARCHAR2(40)      NULL,
CTG_IND                  VARCHAR2(40)       NULL,
FOR_CDE                  VARCHAR2(40)      NULL,
SHP_CDE                  VARCHAR2(40)      NULL);

0
 

Author Comment

by:gram77
ID: 19669932
awking00:

I have an legacy database that has a table that allows null values
In the new table, the same columns are defined as not null.

I rather want to
1. disable all the Null and Primary Key constraints
2. Load all data through sqlldr
3. Enable all constraints with NOVALIDATE option


Can you give me the ALTER TABLE syntax for:
1. disabling all constraints on a table by a single command.
2. Enabling all constraints with NOVALIDATE option without having to name induvidual
not null constraint?
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month13 days, 19 hours left to enroll

809 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