Re: sqlldr ignores skip

From: ddf <oratune_at_msn.com>
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

Original text of this message