Need To Replace Nulls With Spaces Within an External Table Definition (Doc ID 739147.1)

Last updated on SEPTEMBER 23, 2008

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.8
Information in this document applies to any platform.

Goal

You have an external table with some columns which have spaces in the columns, but after loading the
data, they show nulls instead. You would like to know if it is possible to address this problem within
the table definition.

TABLE DEFINITION
===============

CREATE TABLE geo_ext
(
MRCH_SHORT_CITY_NM CHAR(13),
MRCH_CITY_ENR CHAR(40),
MRCH_STATE_ENR CHAR(3),
MRCH_ZIP_ENR CHAR(10),
MRCH_CTRY_ENR CHAR(3),
MRCH_GEO_ID NUMBER(10,0),
NEWLINE_LAST_CHAR CHAR(1)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY DIR__DATA
ACCESS PARAMETERS
( RECORDS FIXED 81
READSIZE 81000
BADFILE dir_log:'geo_ext.bad'
LOGFILE dir_log:'geo_ext.log'
FIELDS (
MRCH_SHORT_CITY_NM position(1:13) CHAR(13),
MRCH_CITY_ENR position(14:53) CHAR(40),
MRCH_STATE_ENR position(54:56) CHAR(3),
MRCH_ZIP_ENR position(57:66) CHAR(10),
MRCH_CTRY_ENR position(67:69) CHAR(3),
MRCH_GEO_ID position(70:79) CHAR(10),
NEWLINE_LAST_CHAR position(80:80) CHAR(1)
))
LOCATION ('GEO.TXT'))

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms