Re: External table problem

From: <TESTAJ3_at_nationwide.com>
Date: Fri, 15 Jan 2010 13:04:53 -0500
Message-ID: <OF81154060.17A33AC8-ON852576AC.0063017F-852576AC.0063529B_at_lnotes-gw.ent.nwie.net>



Ok for the life of me and i've been doing this since oracle 5, is a number(3,2), total of 5 digits with 2 to the right of the decimal or is it 3 digits with 2 to the right of the decimal. I wonder if the issue is really the next to last field? Its friday so feel free to shoot me, my brain is mush.

joe



Joe Testa, Oracle Certified Professional Senior Engineering & Administration Lead
(Work) 614-677-1668
(Cell) 614-312-6715

Interested in helping out your marriage? Ask me about "Weekend to Remember"
Feb 12-Feb 14, 2010, Valentine's Day Weekend in Akron, OH

From:
Sharon.Kovac_at_Rotometrics.com
To:
oracle-l_at_freelists.org
Date:
01/15/2010 12:58 PM
Subject:
External table problem
Sent by:
oracle-l-bounce_at_freelists.org

Hi all,

I need help with creating an external table. I'm working with a simple csv file, example data below:

1,450,01/03/10,01/09/10,39.02,0.00 
1,311,01/03/10,01/09/10,0.00,0.00 
2,120,01/03/10,01/09/10,0.00,0.00 
2,160,01/03/10,01/09/10,0.00,0.00 
2,655,01/03/10,01/09/10,39.92,0.00 

When I query the external table, I get the "reject limit reached" error. The log contains the following for each row:

KUP-04021: field formatting error for field HOME_WG_3 
KUP-04025: field extends beyond end of record 
KUP-04101: record 1 rejected in file /home/aeexport/HOURSEXP.CSV 

I looked up the error message and all I get is "This can happen when specifying either absolute or relative positioning for a field." I'm not specifying an absolute or relative position for a field.

Here's my create table statement:

CREATE TABLE ROT_EMP_HOURS_CSV
( HOME_WG_1 NUMBER(1),

  HOME_WG_3 NUMBER(3),
  START_DATE DATE,
  END_DATE DATE,

  REG         NUMBER(3,2), 
  OT          NUMBER(3,2)) 

ORGANIZATION EXTERNAL
  ( TYPE ORACLE_LOADER
     DEFAULT DIRECTORY AEEXPORT 
     ACCESS PARAMETERS 
       ( records delimited by newline 
           fields terminated by ',' 
                    ( 
                                      HOME_WG_1   , 
                                     HOME_WG_3   , 
                                    START_DATE  DATE 'MM-DD-YYYY', 
                                     END_DATE    DATE 'MM-DD-YYYY', 
                                     REG         , 
                                     OT          ) 
                    ) 
     LOCATION ('HOURSEXP.CSV') 

  )

Oracle 10.2.0.2 on Solaris.

Thanks for your help!

Sharon

Sharon Kovac | RotoMetrics
Phone: +1 636 587 3600 | Fax: +1 636 587 3701 800 Howerton Lane | Eureka, MO 63025 | USA http://www.rotometrics.com | http://www.myroto.com



This e-mail message is intended only for the personal use of the recipient(s) named above. This message is confidential. If you are not an intended recipient, you may not review, copy or distribute this message. If you have received this communication in error, please notify the sender immediately by e-mail and delete the original message.

.



--
http://www.freelists.org/webpage/oracle-l


picture
Received on Fri Jan 15 2010 - 12:04:53 CST

Original text of this message