| Constraints in External Table. [message #653377] |
Mon, 04 July 2016 05:48  |
 |
jeeva7311
Messages: 5
Registered: June 2016
Location: chennai
|
Junior Member |
|
|
Hi Guys
I have created an External Table & now have to set constraints such as for BIN_NUMBER is NULL & PROCESSOR_CONTROL_NUMBER is NULL, then it should move to the reject table. How to proceed further? Please Guide me.
CREATE TABLE "PAYER_EXT"
( "ID_340B" VARCHAR2(11 BYTE),
"BIN_NUMBER" NUMBER,
"PROCESSOR_CONTROL_NUMBER" VARCHAR2(100 BYTE),
"ELIGIBLE_340B" VARCHAR2(100 BYTE),
"CREATED_DATE" DATE,
"DATE_UPDATE" DATE,
"CREATED_BY" VARCHAR2(100 BYTE),
"UPDATED_BY" VARCHAR2(100 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "PAYER"
ACCESS PARAMETERS
( records delimited by newline
badfile PAYER_bad:'PAYER_EXT%a_%p.bad'
logfile PAYER_log:'PAYER_EXT%a_%p.log'
fields terminated by ','
missing field values are null
(
ID_340B,
BIN_NUMBER,
PROCESSOR_CONTROL_NUMBER,
ELIGIBLE_340B,
CREATED_DATE char date_format date mask "MM/DD/YYYY",
DATE_UPDATE char date_format date mask "MM/DD/YYYY",
CREATED_BY,
UPDATED_BY
)
)
LOCATION
( Payer.csv'
)
)
REJECT LIMIT UNLIMITED
PARALLEL ;
Regards,
Jeeva
|
|
|
|
|
|
|
|
| Re: Constraints in External Table. [message #653382 is a reply to message #653377] |
Mon, 04 July 2016 08:27  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
You can't move records where BIN_NUMBER is NULL & PROCESSOR_CONTROL_NUMBER is NULL to rejected file but you can move it to discarded file. Just add:
CREATE TABLE "PAYER_EXT"
( "ID_340B" VARCHAR2(11 BYTE),
"BIN_NUMBER" NUMBER,
"PROCESSOR_CONTROL_NUMBER" VARCHAR2(100 BYTE),
"ELIGIBLE_340B" VARCHAR2(100 BYTE),
"CREATED_DATE" DATE,
"DATE_UPDATE" DATE,
"CREATED_BY" VARCHAR2(100 BYTE),
"UPDATED_BY" VARCHAR2(100 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "PAYER"
ACCESS PARAMETERS
( records delimited by newline
load when (BIN_NUMBER != blanks or PROCESSOR_CONTROL_NUMBER != blanks)
badfile PAYER_bad:'PAYER_EXT%a_%p.bad'
logfile PAYER_log:'PAYER_EXT%a_%p.log'
discardfile PAYER_dsc:'PAYER_EXT%a_%p.dsc'
fields terminated by ','
missing field values are null
(
ID_340B,
BIN_NUMBER,
PROCESSOR_CONTROL_NUMBER,
ELIGIBLE_340B,
CREATED_DATE char date_format date mask "MM/DD/YYYY",
DATE_UPDATE char date_format date mask "MM/DD/YYYY",
CREATED_BY,
UPDATED_BY
)
)
LOCATION
( Payer.csv'
)
)
REJECT LIMIT UNLIMITED
PARALLEL ;
SY.
[Updated on: Mon, 04 July 2016 08:28] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
