Re: sqlldr ignores skip
Date: Wed, 6 May 2009 07:56:01 -0700 (PDT)
Message-ID: <ebb88b49-e03f-454c-886b-ef57ee2d7d4b_at_l5g2000vbc.googlegroups.com>
On May 6, 8:45 am, Thomas Poenicke <poeni..._at_freenet.de> wrote:
> Hi all,
>
> I'm invoking sqlldr from commandline:
>
> sqlldr userid=user/password_at_//localhost/XE control=file.ini SKIP=7
> log=logfile.log
>
> Sqlldr always ignores the option and skips only one line.
> I've tried to set the option in the controlfile with OPTION (SKIP=7)
> but that is ignored too.
>
> The skipped "records" are header lines with the complete tab delimited
> columns (but trailing nullcol) plus the table header line.
> This lines are discarded beacause of the trailing nulcols and the
> length of the entries in the table header, so the loading result is
> what i need. I just wan't to built the effort on an misbehaviour.
>
> Can anyone please give an hint?
>
> Thanks.
> Thomas
I'm having no issue with using skip:
skipload.ctl --
load data
infile skipload.dat
replace
into table skiptbl
fields terminated by ',' optionally enclosed by '"'
(mydata)
skipload.dat --
Yankenflermer
Yankenflermer
Yankenflermer
Yankenflermer
Yankenflermer
Yankenflermer
Yankenflermer
Yankenflermer
Yankenflermer
Yankenflermer
sqlldr_skip_ex.sql --
create table skiptbl(
mydata varchar2(70)
);
$sqlldr #######/%%%%%%% skipload.ctl SKIP=9 log=skipload.log
select mydata
from skiptbl;
$sqlldr #######/%%%%%%% skipload.ctl skip=9 log=skipload.log
select mydata
from skiptbl;
drop table skiptbl purge;
output from test --
SQL> _at_sqlldr_skip_ex
SQL> create table skiptbl(
2 mydata varchar2(70)
3 );
Table created.
SQL>
SQL> $sqlldr #######/%%%%%% skipload.ctl SKIP=9 log=skipload.log
SQL*Loader: Release 11.1.0.6.0 - Production on Wed May 6 09:49:53 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Commit point reached - logical record count 1
SQL>
SQL> select mydata
2 from skiptbl;
MYDATA
Yankenflermer
SQL>
SQL> $sqlldr #######/%%%%%% skipload.ctl skip=9 log=skipload.log
SQL*Loader: Release 11.1.0.6.0 - Production on Wed May 6 09:49:53 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Commit point reached - logical record count 1
SQL>
SQL> select mydata
2 from skiptbl;
MYDATA
Yankenflermer
SQL>
SQL> drop table skiptbl purge;
Table dropped.
SQL> skipload.log ---
SQL*Loader: Release 11.1.0.6.0 - Production on Wed May 6 09:49:53 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Control File: skipload.ctl Data File: skipload.dat Bad File: skipload.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL Number to skip: 9 Errors allowed: 50 Bind array: 64 rows, maximum of 256000 bytes Continuation: none specified Path used: Conventional
Table SKIPTBL, loaded from every logical record. Insert option in effect for this table: REPLACE
Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- -------------------- MYDATA FIRST * , O(") CHARACTER
Table SKIPTBL:
1 Row 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.
Space allocated for bind array: 16512 bytes(64 rows)Read buffer bytes: 1048576
Total logical records skipped: 9 Total logical records read: 1 Total logical records rejected: 0 Total logical records discarded: 0
Run began on Wed May 06 09:49:53 2009
Run ended on Wed May 06 09:49:53 2009
Elapsed time was: 00:00:00.05 CPU time was: 00:00:00.03
Which release of Oracle are you using? Without that information it's practically impossible to solve your problem.
David Fitzjarrell Received on Wed May 06 2009 - 09:56:01 CDT