OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » External Table

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

External Table [message #457773]

Wed, 26 May 2010 00:13

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

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

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

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

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

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

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

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

Barbara Boehmer
Messages: 9102
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