My Oracle Support Banner

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

Last updated on OCTOBER 07, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.8 and later
Information in this document applies to any platform.

NOTE: The document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product.
Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

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 test_ext
(
COL1 CHAR(13),
COL2 CHAR(40),
COL3 CHAR(3),
COL4 CHAR(10),
COL5 CHAR(3),
COL6 NUMBER(10,0),
COL7 CHAR(1)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY DIR__DATA
ACCESS PARAMETERS
( RECORDS FIXED 81
READSIZE 81000
BADFILE dir_log:'test_ext.bad'
LOGFILE dir_log:'test_ext.log'
FIELDS (
COL1 position(1:13) CHAR(13),
COL2 position(14:53) CHAR(40),
COL3 position(54:56) CHAR(3),
COL4 position(57:66) CHAR(10),
COL6 position(70:79) CHAR(10),
COL7 position(80:80) CHAR(1)
))
LOCATION ('TEST.TXT'))

Solution

To view full details, sign in with your My Oracle Support account.

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


In this Document
Goal
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.