You Asked
So, I queried the following to (hopefully) answer what you've asked for -
SELECT PRODUCT, VERSION FROM SYS.PRODUCT_COMPONENT_VERSION;
NLSRTL 11.2.0.3.0
Oracle Database 11g Enterprise Edition 11.2.0.3.0
PL/SQL 11.2.0.3.0
TNS for 64-bit Windows: 11.2.0.3.0
select dbms_utility.port_string from dual;
IBMPC/WIN_NT64-9.1.0
Also, here's the sample data (from xyz.csv)-
Provider_Name Last_Name First_Name Email Credit_Hours Completion_date Activity_Title
State Farm Zone 8 Mid-America Adams IsabellaÜ Rani tyagi@theinstitutes.org 1.25 12/6/2016 Special Char 1 ¢
State Farm Zone 8 Mid-America tyagi swatiÜ tyagi@theinstitutes.org 1.25 12/6/2016 Special Char 2 æ
State Farm Zone 8 Mid-America tyagi swatiß tyagi@theinstitutes.org 3 12/6/2016 Special Char 3 ?
If you noticed, the columns have special characters, also in this sample, one column ("Activity_Type") is missing, so ideally this file should be rejected entirely and a .bad file should be created.
When I have my external table created without "CHARACTERSET WE8MSWIN1252" clause (for the same data set), it creates the .bad file just fine.But then, I need this CHARACTERSET clause to be able to handle/interpret special characters correctly.
Please let me know if I answered your questions.
Thanks again in advance for your help with this.
-Rita
************************************************************************************
Thanks for your response!
The reason I had to get rid of the CREATE table part was that I was not sure if you're actually seeing my updates. I guess I missed reading the middle part in your email and I was just looking at the last questions which were still the same.
My apologies!
CREATE TABLE xyz_external_credits_ext
(provider_name VARCHAR2(120 BYTE),
last_name VARCHAR2(30 BYTE),
first_name VARCHAR2(30 BYTE),
email VARCHAR2(50 BYTE),
credit_hours NUMBER,
completion_date DATE,
activity_type VARCHAR2(50 BYTE),
activity_title VARCHAR2(200 BYTE))
ORGANIZATION EXTERNAL (
DEFAULT DIRECTORY TMP
ACCESS PARAMETERS(records delimited by newline characterset we8mswin1252
SKIP 1
BADFILE TMP:'xyz.bad'
LOGFILE TMP:'xyz.log'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
MISSING FIELD VALUES ARE NULL
(PROVIDER_NAME,LAST_NAME,FIRST_NAME,EMAIL,CREDIT_HOURS,COMPLETION_DATE DATE 'mm/dd/yyyy',ACTIVITY_TYPE,ACTIVITY_TITLE)
)
LOCATION (
TRANSFER_CPCUCE_IN:'xyz.csv'
)
)
REJECT LIMIT 10
NOPARALLEL;
For commas missing in the sample data - I directly copied the sample data from a .csv file where the commas were already taken care of into different columns. But yes, it will be a .csv file.
I will see the commas if I open it in some other editor (say Sublime Text). Here's the sample data again -
Provider_Name,Last_Name,First_Name,Email,Credit_Hours,Completion_date,Activity_Title
State Farm Zone 8 Mid-America,Adams,IsabellaÜ Rani,tyagi@theinstitutes.org,1.25,12/6/2016,Special Char 1 ¢
State Farm Zone 8 Mid-America,tyagi,swatiÜ,tyagi@theinstitutes.org,1.25,12/6/2016,Special Char 2 æ
State Farm Zone 8 Mid-America,tyagi,swatiß,tyagi@theinstitutes.org,3,12/6/2016,Special Char 3 ?
Yes, the column missing is Activity_Type and not Activity_Title (Sorry!). In this case that entire column is missing, so to your question, it will be missing for every row.
If there is a format issue with one record, I do not want to reject the entire file but rather just that record should be captured in the .bad file (say the name field exceeds 30 characters for a record).
It works just fine for me when I do not mention 'characterset we8mswin1252' in the CREATE Table DDL.
Could it be the Oracle version issue?
I am trying to execute SELECT * FROM xyz_external_credits_ext from SQL Navigator 6.2.1.
The Oracle version and OS version are as mentioned above.
Any help would be greatly appreciated.
Thanks,
Rita.
and Chris said...
So: if I modify your example slightly so activity_type is 16 bytes loading the final "Special char" fields into that, I get a .bad file for the two rows which are slightly too long:
create table t (
provider_name varchar2 ( 120 byte ) , last_name varchar2 ( 30 byte ) ,
first_name varchar2 ( 30 byte ) , email varchar2 ( 50 byte ) ,
credit_hours number, completion_date date,
activity_type varchar2 ( 16 byte ) , activity_title varchar2 ( 200 byte )
) organization external (
default directory tmp access parameters (
records delimited by newline characterset we8mswin1252
skip 1
badfile tmp:'xyz.bad'
logfile tmp:'xyz.log'
fields terminated by ','
optionally enclosed by '"'
missing field values are null
( provider_name,last_name,first_name,email,credit_hours,
completion_date date 'mm/dd/yyyy',activity_type,activity_title )
) location ( tmp:'xyz.csv' )
)
reject limit 10 noparallel;
bash-3.00$ ls xyz*
xyz.csv
bash-3.00$ sqlplus xxxx/xxxx
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 4 07:41:44 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
SQL> select * from t;
PROVIDER_NAME
------------------------------------------------------------------------------------------------------------------------
LAST_NAME FIRST_NAME EMAIL
------------------------------ ------------------------------ --------------------------------------------------
CREDIT_HOURS COMPLETIO ACTIVITY_TYPE
------------ --------- ----------------
ACTIVITY_TITLE
------------------------------------------------------------------------------------------------------------------------
State Farm Zone 8 Mid-America
tyagi swati?? tyagi@theinstitutes.org
3 06-DEC-16 Special Char 3 ?
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
bash-3.00$ ls xyz*
xyz.bad xyz.csv xyz.log
bash-3.00$ cat xyz.bad
State Farm Zone 8 Mid-America,Adams,IsabellaÜ Rani,tyagi@theinstitutes.org,1.25,12/6/2016,Special Char 1 ¢
State Farm Zone 8 Mid-America,tyagi,swatiÜ,tyagi@theinstitutes.org,1.25,12/6/2016,Special Char 2 æ
This is on 11.2.0.4 though; I don't have a Windows version of 11.2.0.3 handy to test at the moment. So the lack of a badfile when you specify a characterset could a bug in your version.
I can't see any related bugs filed in MOS; but it's worth contacting support to see if they can help you.
Is this answer out of date? If it is, please let us know via a Comment