OraFAQ Forum: Server Utilities » SQL Loader not loading the data file

Home » RDBMS Server » Server Utilities » SQL Loader not loading the data file (Oracle 10g, Solaris 5.8)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Loader not loading the data file [message #424307]

Thu, 01 October 2009 08:45

amarjeetk
Messages: 4
Registered: October 2009
Location: INDIA

Junior Member

We are trying to load a CTL control file but it is giving not loading the data completely. CTL file contains definition for three tables (ACTIONS, STEP_POOL_TMPLS and PARAMETER_POOL_TMPLS).

[see Issue.CTL file attached for more information]

CTL control file is being loaded using the command:

sqlldr userid=dataload/dataload@fldesi control=Issue.CTL log=Issue.LOG bindsize=30000000 readsize=10000000

After finishing the loading, review the Issue.LOG file (generated during loading). See the error in the log file

Record 2: Rejected - Error on table "OSD1"."STEP_POOL_TMPLS", column ID.

Field in data file exceeds maximum length

Record 3: Rejected - Error on table "OSD1"."PARAMETER_POOL_TMPLS", column ID.

Field in data file exceeds maximum length

Steps to replicate:

Create the following tables:
ACTIONS

STEP_POOL_TMPLS

PARAMETER_POOL_TMPLS

[see Issue.CTL and Issue.LOG file for details]

Create the following sequences
spt_seq (for STEP_POOL_TMPLS)

ppt_seq (PARAMETER_POOL_TMPLS)

[see Issue.CTL and Issue.LOG file for details]

Load the CTL file with command:
sqlldr userid=dataload/dataload@fldesi control=Issue.CTL log=Issue.LOG bindsize=30000000 readsize=10000000

View Issue.LOG file generated beside Issue.CTL file. Notice the error in the file:
Record 2: Rejected - Error on table "OSD1"."STEP_POOL_TMPLS", column ID.

Field in data file exceeds maximum length

Record 3: Rejected - Error on table "OSD1"."PARAMETER_POOL_TMPLS", column ID.

Field in data file exceeds maximum length

It works when

Same CTL contol file is able to load the data for STEP_POOL_TMPLS and PARAMETER_POOL_TMPLS tables in either of the below cases:

I comment out the header definition for ACTIONS table
I do not use the spt.seq.nextval and ppt.seq.nextval to populate the ID fields in STEP_POOL_TMPLS and PARAMETER_POOL_TMPLS tables respectively and use the fixed values in data section instead.

Version Information:

SQL Loader Version

SQL*Loader: Release 8.1.7.4.0

SQL*Loader: Release 10.1.0.4.0 (also replicated on)

Operating System:

Machine hardware: sun4u

OS version: 5.8

Processor type: sparc

Hardware: SUNW,Sun-Fire-480R

================================

Attachment: Issue.CTL
(Size: 2.61KB, Downloaded 1562 times)

Report message to a moderator

Re: SQL Loader not loading the data file [message #424365 is a reply to message #424307]

Thu, 01 October 2009 14:47

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Quote:

Field in data file exceeds maximum length

Isn't this message self-descriptive enough?

Report message to a moderator

Re: SQL Loader not loading the data file [message #424376 is a reply to message #424365]

Thu, 01 October 2009 21:51

amarjeetk
Messages: 4
Registered: October 2009
Location: INDIA

Junior Member

LittleFoot,
thank you for posting but, can you please be clearer and tell me where exactly is it exceeding? (CTL file is attached with main post)

Also, if problem is with the data size then why should it work when-
1. I comment out the ACTIONS table header (see Issue.CTL)

--INTO TABLE ACTIONS
--WHEN (1:7) = 'Actions'
--FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
--( ID POSITION(9), VN, FROM_PRODUCT_VN, TO_PRODUCT_VN, ACTION_TYPE, DESCRIPTION, FROM_PRODUCT_ID, TO_PRODUCT_ID, TIGHT_BUNDLE_YN, BLACKLISTED_YN, BLACKLIST_THRESHOLD, BLACKLIST_COUNT, CANCEL_ALLOWED_YN, MANUAL_ABORT_ALLOWED_YN, MANUAL_COMPLETE_ALLOWED_YN)

OR

2. I use the fixed values in data section instead of oracle table sequences (spt_seq.nextval)

INTO TABLE STEP_POOL_TMPLS
WHEN (1:15) = 'Step_Pool_Tmpls'
FIELDS TERMINATED BY "," TRAILING NULLCOLS
--( ID "spt_seq.nextval", VN POSITION(17), CALL_SEQ_TMPL_ID, CALL_SEQ_TMPL_VN, STEP_POOL CHAR(10485760) ENCLOSED BY '"')

( ID POSITION(17), VN, CALL_SEQ_TMPL_ID, CALL_SEQ_TMPL_VN, STEP_POOL CHAR(10485760) ENCLOSED BY '"')

Report message to a moderator

Re: SQL Loader not loading the data file [message #424380 is a reply to message #424376]

Thu, 01 October 2009 22:16

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

The control file is only half the problem.
Your problem is that the data contains more digits than the field can hold.

sqlldr -help
userid ORACLE username/password
control Control file name
log Log file name
bad Bad file name

Specify on command line "bad=bad.log" to capture where problem occurs.

Report message to a moderator

Re: SQL Loader not loading the data file [message #424418 is a reply to message #424376]

Fri, 02 October 2009 04:58

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

This is the illustration of what is happening during your loading session:

SQL> create table test (col varchar2(3));

Table created.

SQL> insert into test (col) values ('abc');

1 row created.

SQL> insert into test (col) values ('abcdefgh');
insert into test (col) values ('abcdefgh')
                               *
ERROR at line 1:
ORA-12899: value too large for column "SCOTT"."TEST"."COL" (actual: 8, maximum: 3)


SQL>

Report message to a moderator

Re: SQL Loader not loading the data file [message #424424 is a reply to message #424307]

Fri, 02 October 2009 05:19

amarjeetk
Messages: 4
Registered: October 2009
Location: INDIA

Junior Member

I looked into bad file for STEP_POOL_TMPLS table I can see the below record-

Step_Pool_Tmpls,0,134153,0,"<?xml version=""1.0"" encoding=""UTF-8""?>
<Step_Pool_Template xmlns:xsi=""XMLSchema-instance"" xsi:noNamespaceSchemaLocation=""StepPool.xsd"">
<StepPool Vn=""5"" Cnt=""1"">
<Step Nm=""RULAdd"" Desc=""PLEASE ENTER DESCRIPTION"" SeqNo=""10"" DrvType=""RULE"" DayRipe=""600"" NightRipe=""600"">
<Inputs Cnt=""1"">
<Input Nm=""ignore"" VT=""11395"" SeqNo=""0"" Param=""1454280""/>
</Inputs>
<Outputs Cnt=""1"">
<Output Nm=""oResult"" FT=""8315"" FTCode=""CANCELLED"" Param=""1454279""/>
</Outputs>
<ErrorMaps Cnt=""0""/>
</Step>
</StepPool>
</Step_Pool_Template>",

I don't think there is any record larger than its column size. Last column which is holding XML part is of type LONG which can hold huge amount of data. Atleast larger than what is being loaded at the moment.

Did you notice point in my previous post...

Same record gets loaded when I comment out the ACTIONS table header definition (see Issue.CTL). Why it is so??
[Its another thing that records for ACTIONS will be discarded while loading the CTL file]

Report message to a moderator

Re: SQL Loader not loading the data file [message #425468 is a reply to message #424424]

Fri, 09 October 2009 04:48

amarjeetk
Messages: 4
Registered: October 2009
Location: INDIA

Junior Member

** SOLVED** see the resolution below by Oracle Support

ACTION PLAN
============

This apparently strange behavior is produced by columns:

ID "spt_seq.nextval"

in table STEP_POOL_TMPLS and:

ID "ppt_seq.nextval"

in table PARAMETER_POOL_TMPLS.

If you specifiy the sequence in that mpode for multiple tables, then SQL*Loader first reads further in file and wrongly interp
rets the field content. There is neither a POSITION specified for this field to
set the pointer within input file (comes later with next field VN), nor is an EX
PRESSION specified (that means, the content is *not* taken from input file).

To correct this and allow all the tables are loaded as expected, modify the lines:

ID EXPRESSION "spt_seq.nextval"
...
ID EXPRESSION "ppt_seq.nextval"
...

Example of the output after that changed
================================

$ sqlldr userid=dataload/dataload control=Issue.CTL log=Issue.LOG bindsize=30000000 re
adsize=10000000

Ouput after change that:

SQL> select count(*) from STEP_POOL_TMPLS;

COUNT(*)
----------
1
SQL> select count(*) from PARAMETER_POOL_TMPLS;

COUNT(*)
----------
1
SQL> select count(*) from actions;

COUNT(*)
----------
1

Report message to a moderator

Previous Topic:	impdp issue - ORA-02264
Next Topic:	loading null values in date column using sql loader (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 11 04:52:43 CST 2025