OraFAQ Forum: Server Utilities » Sequence Generator & Skip n

Home » RDBMS Server » Server Utilities » Sequence Generator & Skip n

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Sequence Generator & Skip n [message #74319]

Fri, 12 November 2004 12:23

Ming
Messages: 2
Registered: October 2001

Junior Member

Hi,

�Let me say first, I found that this web site is an excellent source for Oracle sql loader.

�I have tried two examples here but both of them fail

to work as expected.

�1. SKIP n
�Got the following errow mesg.

�SQL*Loader-410: Number to skip must be load-level, not table-level

�2. Use Oracle sequence during the load
�Record 1: Rejected - Error on table SRC_GBS_TRD, column ATLAS_ID.
�Field in data file exceeds maximum length

�Following is my control file.
�Note: without SKIP and squence, the data
�is successfully loaded into the table.
�Atlas_Id is defined as NUMBER.

Did I miss something here? Thank you for any help

in advance.

Ming

�options (direct=true) unrecoverable load data
�infile '/home/nbk6x7k/gbs/data/FXATLASHK1A20041025.TXT'
�APPEND
�into table SRC_GBS_TRD
�fields terminated by '|'
�SKIP 2
�(
�TRD_ID,
�SRC_ID,
�PRD_CODE,
�PRD_NAME,
�PORTF_ID,
�TRD_TMS,
�COMPUTRON_CC,
�GBS_ID,
�BOOK_ID,
�BOOK_LOC,
�TRADR_ID,
�GL_ACCT_NUM,
�MKTR_LOC,
�CNTRY_CODE,
�CNTRPTY_DESC,
�CNTRPTY_TYPE_CODE,
�MTM_SRC,
MTM,
�MTM_USD,
�ATLAS_ID "ATL_TRD_KEY.nextval"
�)

Report message to a moderator

Re: Sequence Generator & Skip n [message #74320 is a reply to message #74319]

Fri, 12 November 2004 18:48

Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA

Senior Member

The SKIP command needs to be either with your options in your control file, as shown below, or in your command line.

I am unable to reproduce your other error, but I don't think you can use a SQL sequence with direct=true option. So, you can either remove the direct=true or use a SQL*Loader sequence, as shown below.

If this does not solve your problem, then please provide a few lines of sample data.

options (direct=true, SKIP=2) unrecoverable load data
infile '/home/nbk6x7k/gbs/data/FXATLASHK1A20041025.TXT'
APPEND
into table SRC_GBS_TRD
fields terminated by '|'
(
TRD_ID,
SRC_ID,
PRD_CODE,
PRD_NAME,
PORTF_ID,
TRD_TMS,
COMPUTRON_CC,
GBS_ID,
BOOK_ID,
BOOK_LOC,
TRADR_ID,
GL_ACCT_NUM,
MKTR_LOC,
CNTRY_CODE,
CNTRPTY_DESC,
CNTRPTY_TYPE_CODE,
MTM_SRC,
MTM,
MTM_USD,
ATLAS_ID SEQUENCE
)

Report message to a moderator

Previous Topic:	SQL*Loader SQLLDR how is this invoked?
Next Topic:	Very urgent ORA-12154 TNS Could not resolve service name error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 03 08:54:42 CDT 2024