Home » RDBMS Server » Server Utilities » External Table
External Table [message #457773] Wed, 26 May 2010 00:13 Go to next message
zion123
Messages: 13
Registered: May 2010
Junior Member
Hi,
below is the external table definition and the content of csv file

please let me know why the first record fails ?

0546-0*LB-CRP*16*"Tech", ZAO*29-DEC-2009*29-DEC-2010***A051453*RU*29-DEC-2009*0***21-MAY-2010*21-MAY-2010 --FAILED
0546-0*LB-CRP*16*ID"Tech", ZAO*29-DEC-2009*29-DEC-2010***A051453*RU*29-DEC-2009*0***21-MAY-2010*21-MAY-2010 -- SUCCESS
0546-0*LB-CRP*16*"Tech, ZAO"*29-DEC-2009*29-DEC-2010***A051453*RU*29-DEC-2009*0***21-MAY-2010*21-MAY-2010 -- SUCCESS


KUP-04021: field formatting error for field PART_NAME
KUP-04037: terminator not found
KUP-04101: record 1 rejected in file /oradata10B/ucssit5/ftp_in/KRS_DATA_KRS_RATING_MS.csv

CREATE TABLE XTERN_KRS_RATING_MS
(
RAT_KD_ID VARCHAR2(256 BYTE),
RAT_SYSTEM VARCHAR2(20 BYTE),
RATING_NOTE VARCHAR2(20 BYTE),
PART_NAME VARCHAR2(256 BYTE),
RAT_DATUM DATE,
WVL_DATUM DATE,
RAT_UEBERN_FLAG VARCHAR2(1 BYTE),
DRING_KZ VARCHAR2(20 BYTE),
ERST_NAME VARCHAR2(256 BYTE),
TEAM_BEZ VARCHAR2(256 BYTE),
DAT_TEAMZUORD DATE,
REF_CTR NUMBER(3),
KONZ_ID VARCHAR2(256 BYTE),
FINANZ_TYP VARCHAR2(256 BYTE),
PRCSS_DT DATE,
BTCH_TEMP_DT DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY INCOMING_DIR
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
badfile SPOOL_DIR:'XTERN_KRS_RATING_MS.bad'
LOGFILE SPOOL_DIR:'XTERN_KRS_RATING_MS.log'
FIELDS TERMINATED BY '*'
OPTIONALLY ENCLOSED BY '"'
MISSING FIELD VALUES ARE NULL(
RAT_KD_ID,
RAT_SYSTEM,
RATING_NOTE,
PART_NAME,
RAT_DATUM DATE "dd-mon-rrrr",
WVL_DATUM DATE "dd-mon-rrrr",
RAT_UEBERN_FLAG,
DRING_KZ,
ERST_NAME,
TEAM_BEZ,
DAT_TEAMZUORD DATE "dd-mon-rrrr",
REF_CTR,
KONZ_ID,
FINANZ_TYP,
PRCSS_DT DATE "dd-mon-rrrr",
BTCH_TEMP_DT DATE "dd-mon-rrrr")
)
LOCATION (INCOMING_DIR:'KRS_DATA_KRS_RATING_MS.csv')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;

[Updated on: Wed, 26 May 2010 00:19]

Report message to a moderator

Re: External Table [message #457780 is a reply to message #457773] Wed, 26 May 2010 00:33 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member

Quote:

,



Regards
Ved

Report message to a moderator

Re: External Table [message #457783 is a reply to message #457773] Wed, 26 May 2010 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
*"Tech", ZAO*

As it starts with optionaly enclosing character ("), Oracle takes it as it and not as part of the data, and so ends the data with the next " which should be followed by the fields terminator which is not the case.

Regards
Michel

Report message to a moderator

Re: External Table [message #457871 is a reply to message #457783] Wed, 26 May 2010 04:23 Go to previous messageGo to next message
zion123
Messages: 13
Registered: May 2010
Junior Member
Hi,
Thanks.But we have to process part_name *"Tech", ZAO* with this value. is there anyway we can achieve it?

Report message to a moderator

Re: External Table [message #457874 is a reply to message #457871] Wed, 26 May 2010 04:32 Go to previous messageGo to next message
zion123
Messages: 13
Registered: May 2010
Junior Member
Hi,
for the below format also same error.

0546*LB-CRP*16*"DS" OOO*29-DEC-2009*29-DEC-2010***A051453*RU*29-DEC-2009*0***21-MAY-2010*21-MAY-2010 --failed
0546*LB-CRP*16*ID"DS" OOO*29-DEC-2009*29-DEC-2010***A051453*RU*29-DEC-2009*0***21-MAY-2010*21-MAY-2010 --Success
0546*LB-CRP*16*"DS OOO"*29-DEC-2009*29-DEC-2010***A051453*RU*29-DEC-2009*0***21-MAY-2010*21-MAY-2010 --Success

KUP-04021: field formatting error for field PART_NAME
KUP-04037: terminator not found
KUP-04101: record 1 rejected in file /oradata10B/ucssit5/ftp_in/KRS_DATA_KRS_RATING_MS.csv

Report message to a moderator

Re: External Table [message #457882 is a reply to message #457871] Wed, 26 May 2010 04:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
zion123 wrote on Wed, 26 May 2010 11:23
Hi,
Thanks.But we have to process part_name *"Tech", ZAO* with this value. is there anyway we can achieve it?

I think you enclose it between ": *""Tech", ZAO"*
You can also wait for Barbara, I'm pretty confident she will find a nice trick.

Regards
Michel

Report message to a moderator

Re: External Table [message #457971 is a reply to message #457882] Wed, 26 May 2010 10:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
Senior Member
You have not said what value you want to extract. If you want the whole value between the asterisks:

"Tech", ZAO

then just remove the line that says:

OPTIONALLY ENCLOSED BY '"'

If you want to remove the quotes, then you will need to use the replace function on that column when selecting from the external table for insert or other usage. If you were using sql*loader, you could use the replace function during the load, but functions are not allowed during external table creation.

Report message to a moderator

Re: External Table [message #457976 is a reply to message #457971] Wed, 26 May 2010 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
/forum/fa/3518/0/ Of course, yes, if no value is enclosed between ", why using this clause?

Regards
Michel

Report message to a moderator

Re: External Table [message #458078 is a reply to message #457971] Thu, 27 May 2010 04:27 Go to previous message
zion123
Messages: 13
Registered: May 2010
Junior Member
Thanks

Report message to a moderator

Previous Topic: running export utility online
Next Topic: EXP-00011...
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 18 20:19:21 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.