Skip to Main Content
  • Questions
  • External File with CHARACTERSET WE8MSWIN1252 does not create a .bad file (added additional information at the end)

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Rita.

Asked: December 07, 2016 - 8:46 pm UTC

Last updated: January 04, 2017 - 3:46 pm UTC

Version: Oracle 11g

Viewed 1000+ times

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

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.