Home » RDBMS Server » Server Utilities » Bug in sqlldr using trim in ctl (control file) (Oracle 11G)
Bug in sqlldr using trim in ctl (control file) [message #596088] Tue, 17 September 2013 22:31 Go to next message
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 #596092 is a reply to message #596088] Wed, 18 September 2013 00:11 Go to previous messageGo to next message
Littlefoot
Messages: 19526
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK, we have your control file and CREATE TABLE statement. Now, please, post several input records (or attach a TXT file) so that we could try it. I don't know whether it matters, but - specify 11g version number (11.x.x.x), as well as operating system you use.
Re: Bug in sqlldr using trim in ctl (control file) [message #596121 is a reply to message #596092] Wed, 18 September 2013 03:01 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7973
Registered: November 2002
Location: California, USA
Senior Member
The following is a simplified example. Both rows are greater than 10 characters, with the second row only greater than 10 characters if you include the trailing spaces. The first row is properly rejected. Despite the fact that the table's column is varchar2(10), it does not reject the second row. Apparently it trims the spaces first, then checks the length, not the other way around.

SCOTT@orcl12c> HOST TYPE test.ctl
LOAD DATA
INFILE *
INTO TABLE s_test_trim
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(field_1 CHAR "TRIM(:field_1)" )
BEGINDATA:
123456789012345|
12345678       |

SCOTT@orcl12c> CREATE TABLE s_test_trim
  2    (field_1 VARCHAR2(10))
  3  /
Table created.

SCOTT@orcl12c> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log

SQL*Loader: Release 12.1.0.1.0 - Production on Wed Sep 18 00:48:34 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional

Commit point reached - logical record count 2

Table S_TEST_TRIM:

  1 Row successfully loaded.

Check the log file:

  test.log

for more information about the load.

SCOTT@orcl12c> SELECT * FROM s_test_trim
  2  /

FIELD_1
----------
12345678

1 row selected.

Re: Bug in sqlldr using trim in ctl (control file) [message #596129 is a reply to message #596121] Wed, 18 September 2013 04:02 Go to previous messageGo to next message
somnathgiri
Messages: 5
Registered: June 2008
Location: India
Junior Member

Hi,

I have realized the source of problem here.

It is the parameter DIRECT=TRUE in ctl file

When I am giving the parameter, it is ignoring the length defined for the columns in the table and is inserting the data.

But, is it the desired functionality?
Re: Bug in sqlldr using trim in ctl (control file) [message #596132 is a reply to message #596129] Wed, 18 September 2013 04:07 Go to previous messageGo to next message
cookiemonster
Messages: 10929
Registered: September 2008
Location: Rainy Manchester
Senior Member
What exact data is getting inserted that you think violates the size rule?
Re: Bug in sqlldr using trim in ctl (control file) [message #596139 is a reply to message #596088] Wed, 18 September 2013 04:44 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2193
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 #596180 is a reply to message #596129] Wed, 18 September 2013 12:09 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7973
Registered: November 2002
Location: California, USA
Senior Member
somnathgiri wrote on Wed, 18 September 2013 02:02
Hi,

I have realized the source of problem here.

It is the parameter DIRECT=TRUE in ctl file

When I am giving the parameter, it is ignoring the length defined for the columns in the table and is inserting the data.

But, is it the desired functionality?


Obviously that is not the cause, since I did not use DIRECT=TRUE in the example that I posted.

Re: Bug in sqlldr using trim in ctl (control file) [message #605198 is a reply to message #596180] Tue, 07 January 2014 23:59 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7973
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 #605396 is a reply to message #605312] Thu, 09 January 2014 02:38 Go to previous messageGo to next message
cookiemonster
Messages: 10929
Registered: September 2008
Location: Rainy Manchester
Senior Member
It 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.
Re: Bug in sqlldr using trim in ctl (control file) [message #605457 is a reply to message #605396] Thu, 09 January 2014 09:20 Go to previous message
Barbara Boehmer
Messages: 7973
Registered: November 2002
Location: California, USA
Senior Member
cookiemonster wrote on Thu, 09 January 2014 00:38
It 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.
Previous Topic: ORA-01722: invalid number sql* loader
Next Topic: ORA-14460: only one COMPRESS or NOCOMPRESS clause may be specified
Goto Forum:
  


Current Time: Sun Aug 31 00:02:46 CDT 2014

Total time taken to generate the page: 0.29341 seconds