My Oracle Support Banner

THE SQL LOADER AS PART OF EXTERNAL TABLE IS NOT LOADING ROWS PROPERLY WITH SKIP OPTION (Doc ID 2579194.1)

Last updated on SEPTEMBER 11, 2019

Applies to:

Oracle Database Cloud Service - Version N/A to N/A [Release 1.0]
Information in this document applies to any platform.

Symptoms

In 12.2 External table is not getting populated properly as per content of .csv file but it works as expected in 12.1.
The .csv files are generated from BI Publisher reports and used as data file for external tables.
We want to avoid loading the first row i.e. heading for which SKIP 1 option is used.
But it still loads first row with last two character of that line. Without SKP it load all rows.
However when same .csv files is used in 12.1.0.2 database it works as expected i.e. skips the first row.

12.1
--------
$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Apr 12 13:31:16 2019

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Oracle Label Security and Real Application Testing options

SQL> create directory dir1 as '/home/oracle';

Directory created.

SQL> grant read,write on directory dir1 to public;

Grant succeeded.

SQL> create table x.y (
col1 VARCHAR2(120)
)
organization external
(
type oracle_loader
default directory dir1
access parameters
(
records delimited by newline
characterset UTF8
readsize 100000000
badfile 'bad_file.bad'
logfile 'log_file.log'
skip 1
fields terminated by "," optionally enclosed by '"' missing field
values are null
(
"col1" char(4000)
)
)
location
(
'data1.csv'
)
) REJECT LIMIT UNLIMITED
;

Table created.

--- The data file just had one record which is expected to be skipped and the table should not show any record.

SQL> select * from x.y;

no rows selected

12.2
--------
$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Apr 12 13:43:21 2019

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production

SQL> create directory dir1 as '/home/oracle';

Directory created.

SQL> grant read,write on dir1 to public;

Grant succeeded.

SQL> create table x.y (
col1 VARCHAR2(120)
)
organization external
 (
type oracle_loader
default directory dir1
 access parameters
(
records delimited by newline
characterset UTF8
readsize 100000000
badfile 'bad_file.bad'
logfile 'log_file.log'
skip 1
fields terminated by "," optionally enclosed by '"' missing field
values are null
(
 "col1" char(4000)
)
)
location
(
'data1.csv'
 )
) REJECT LIMIT UNLIMITED
 ;

Table created.

--- The data file just had one record which is expected to be skipped and the table should not show any record. But as seen below it loads one record with a portion from the end.

SQL> select * from x.y;

COL1
------------------------------------------------------------------------------
DE

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.