Home » RDBMS Server » Server Utilities » SQL Loader not loading the data file (Oracle 10g, Solaris 5.8)
SQL Loader not loading the data file [message #424307] Thu, 01 October 2009 08:45 Go to next message
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 1283 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 Go to previous messageGo to next message
Littlefoot
Messages: 21807
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 21807
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 Go to previous messageGo to next message
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 Go to previous message
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:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Apr 16 16:28:38 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.