| Bug in sqlldr using trim in ctl (control file) [message #596088] |
Tue, 17 September 2013 22:31  |
somnathgiri
Messages: 5
Registered: June 2008
Location: India
|
Junior Member |
|
|
Hi Guys,
When I am trying to upload data using ctl file having trim in one of the fields, the length of that column in database is not being considered.
The control file is
OPTIONS (SKIP=1, ERRORS=99999999, DIRECT=TRUE )
LOAD DATA
CHARACTERSET WE8ISO8859P15
APPEND
INTO TABLE s_test_trim
WHEN RECORD_TYPE='D'
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
static_1 CONSTANT "<DATE>"
,static_2 CONSTANT "<REGION>"
,static_flag EXPRESSION "CASE WHEN tatus = 'A' THEN 'V' ELSE 'I' END"
,field_1 CHAR "TRIM(:field_1)"
)
The ddl for table is
CREATE TABLE s_test_trim
(
static_1 DATE NOT NULL
,static_2 VARCHAR2(10) NOT NULL
,static_flag VARCHAR2(1) DEFAULT 'V' NOT NULL
,field_1 VARCHAR2(10)
)
COMPRESS
;
Now, when I am trying to load data of field length > 10 for field_1, it is allowing me to do so.
I cannot find any reference on any of the web sites.
Please guide me.
I am using 11g
The data loaded in DB is > 10 and not getting truncated/throwing error.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Bug in sqlldr using trim in ctl (control file) [message #596139 is a reply to message #596088] |
Wed, 18 September 2013 04:44  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
somnathgiri wrote on Wed, 18 September 2013 09:01
field_1 CHAR "TRIM(:field_1)"
)
,field_1 VARCHAR2(10)
)
The data loaded in DB is > 10 and not getting truncated/throwing error.
TRIM is working the way it is supposed to. Since you have specified the column as VARCHAR2, the trailing blanks will be trimmed. Leading whitespace is also removed from a field when optional enclosure delimiters are specified but not present.
Please read the documentation for Trimming Whitespace.
Quote:Now, when I am trying to load data of field length > 10 for field_1, it is allowing me to do so.
There are ways to limit the characters while loading. You could specify the POSITION(from:to) or use SUBSTR. Please post some records of your data file.
Regards,
Lalit
|
|
|
|
|
|
| Re: Bug in sqlldr using trim in ctl (control file) [message #605198 is a reply to message #596180] |
Tue, 07 January 2014 23:59 |
 |
oraclemav
Messages: 1
Registered: January 2014
|
Junior Member |
|
|
I tried the below statements.
create table test_char (char1 varchar2(10 char));
test.ctl
OPTIONS (ERRORS=99999999, DIRECT=TRUE )
LOAD DATA
CHARACTERSET WE8ISO8859P15
APPEND
INTO TABLE test_char
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS (char1 CHAR "TRIM(:char1)")
test.dat
123456789|
A123456789 |
A12345678912345|
Command: sqlldr scott/tiger data=test.dat control=test.ctl log=test.log
test.log
SQL*Loader: Release 11.2.0.2.0 - Production on Wed Jan 8 05:49:34 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Control File: test.ctl
Character Set WE8ISO8859P15 specified for all input.
Data File: test.dat
Bad File: test.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 99999999
Continuation: none specified
Path used: Direct
Table TEST_CHAR, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
CHAR1 FIRST * | CHARACTER
SQL string for column : "TRIM(:char1)"
Table TEST_CHAR:
3 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 3
Total logical records rejected: 0
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 1
Total stream buffers loaded by SQL*Loader load thread: 0
Run began on Wed Jan 08 05:49:34 2014
Run ended on Wed Jan 08 05:49:35 2014
Elapsed time was: 00:00:00.51
CPU time was: 00:00:00.07.
In the previous case, all 3 rows got loaded.
When I remove trim from ctl file, only 1 record is loaded
test.ctl
OPTIONS (ERRORS=99999999, DIRECT=TRUE )
LOAD DATA
CHARACTERSET WE8ISO8859P15
APPEND
INTO TABLE test_char
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS (char1 CHAR)
test.log
SQL*Loader: Release 11.2.0.2.0 - Production on Wed Jan 8 05:53:14 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Control File: test.ctl
Character Set WE8ISO8859P15 specified for all input.
Data File: test.dat
Bad File: test.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 99999999
Continuation: none specified
Path used: Direct
Table TEST_CHAR, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
CHAR1 FIRST * | CHARACTER
Record 2: Rejected - Error on table TEST_CHAR, column CHAR1.
ORA-12899: value too large for column CHAR1 (actual: 16, maximum: 10)
Record 3: Rejected - Error on table TEST_CHAR, column CHAR1.
ORA-12899: value too large for column CHAR1 (actual: 15, maximum: 10)
Table TEST_CHAR:
1 Row successfully loaded.
2 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 3
Total logical records rejected: 2
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 1
Total stream buffers loaded by SQL*Loader load thread: 0
Run began on Wed Jan 08 05:53:14 2014
Run ended on Wed Jan 08 05:53:15 2014
Elapsed time was: 00:00:00.49
CPU time was: 00:00:00.08
So, I agree with somnathgiri that there is a bug with sqlldr with trim.
[mod-edit] color removed.
[Updated on: Wed, 08 January 2014 08:28] by Moderator Report message to a moderator |
|
|
|
| Re: Bug in sqlldr using trim in ctl (control file) [message #605312 is a reply to message #605198] |
Wed, 08 January 2014 11:48 |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
oraclemav,
Welcome to the OraFAQ forums and thank you very much for providing the test case and demonstration. I tested using your test case and found that it works as expected in 12.1.0.1.0, but not in 11.2.0.1.0. So, I agree that it was a bug associated with SQL*Loader and trim in 11g that was fixed in 12c. I have posted my tests below.
Barbara
-- 12c (no bug):
SCOTT@orcl12c> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE 12.1.0.1.0 Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production
5 rows selected.
SCOTT@orcl12c> host type test.dat
123456789|
A123456789 |
A12345678912345|
SCOTT@orcl12c> host type test.ctl
OPTIONS (ERRORS=99999999, DIRECT=TRUE )
LOAD DATA
CHARACTERSET WE8ISO8859P15
APPEND
INTO TABLE test_char
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS (char1 CHAR "TRIM(:char1)")
SCOTT@orcl12c> create table test_char (char1 varchar2(10 char));
Table created.
SCOTT@orcl12c> host sqlldr scott/tiger data=test.dat control=test.ctl log=test.log
SQL*Loader: Release 12.1.0.1.0 - Production on Wed Jan 8 09:39:46 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Path used: Direct
Load completed - logical record count 3.
Table TEST_CHAR:
2 Rows successfully loaded.
Check the log file:
test.log
for more information about the load.
SCOTT@orcl12c> select * from test_char;
CHAR1
----------
123456789
A123456789
2 rows selected.
-- 11g (bug):
SCOTT@orcl> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
5 rows selected.
SCOTT@orcl> host type test.dat
123456789|
A123456789 |
A12345678912345|
SCOTT@orcl> host type test.ctl
OPTIONS (ERRORS=99999999, DIRECT=TRUE )
LOAD DATA
CHARACTERSET WE8ISO8859P15
APPEND
INTO TABLE test_char
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS (char1 CHAR "TRIM(:char1)")
SCOTT@orcl> create table test_char (char1 varchar2(10 char));
Table created.
SCOTT@orcl> host sqlldr scott/tiger data=test.dat control=test.ctl log=test.log
SQL*Loader: Release 11.2.0.1.0 - Production on Wed Jan 8 09:39:52 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Load completed - logical record count 3.
SCOTT@orcl> select * from test_char;
CHAR1
----------
123456789
A123456789
A123456789
3 rows selected.
|
|
|
|
|
|
| Re: Bug in sqlldr using trim in ctl (control file) [message #605457 is a reply to message #605396] |
Thu, 09 January 2014 09:20 |
|
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
cookiemonster wrote on Thu, 09 January 2014 00:38It also only happens when you use char semantics in the column definition. If you do:
create table test_char (char1 varchar2(10));
it works as expected.
Thanks, cookiemonster. That's another reason why my first test ran as expected. I thought it was just a difference in versions.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
