External table and null value

[ccot...@yahoo.com]

HI, I know that I can specify that when a column in the flat file of my
external table is missing, the column will have a null value. Is it
possible to issue a NVL(NULLCOLUMN, '000000') on that column at table
creation so the column instead of containing null values would contain
'000000'?

Here is my DDL script:
CREATE TABLE STG_CAR_PRODUCT_PRICE_T
(
PRODUCT_ID VARCHAR2(10 BYTE),
ITEM_PRICE1 NUMBER(8,2),
ITEM_PRICE1 NUMBER(8,2),
ITEM_MTH_YEAR VARCHAR2(6 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY Products
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE

FIELDS TERMINATED BY ' ' OPTIONALLY ENCLOSED BY '"'
MISSING FIELD VALUES ARE NULL
)
LOCATION ('PRODUCT_FILE.dat')
)
REJECT LIMIT UNLIMITED
NOLOGGING
NOCACHE
NOPARALLEL;

I would like that when the column ITEM_MTH_YEAR is null, instead of
containing nothing, it would have a value of '000000'

Thank you,
Ccote

[Sybrand Bakker]

On 13 Jul 2005 06:27:50 -0700, "ccot...@yahoo.com"
<ccot...@yahoo.com> wrote:

>I would like that when the column ITEM_MTH_YEAR is null, instead of
>containing nothing, it would have a value of '000000'

You know where to find the documentation?

--
Sybrand Bakker, Senior Oracle DBA

[ccot...@yahoo.com]

OH wow, thank you so much for your help! The first thing I did is to
look in the documentation for external tables, of course! If I post
something here, it is because I did not find anything in the
documentation. Believe me, I have so many things to do else than
posting messages on boards for nothing. When I post a message, it is
because I need some help.

[Phil]

Hi,

I have a little experience with this. It stems from the fact that I
had used SQL Loader to emulate such behavior before (i.e. using Oracle
functions on columns to change their "in-file" values).

It seems there isn't an easy way to do this with External Tables,
because Oracle is counting on you doing the transformation with SQL in
an INSERT statement into the "real" destination table.

The only thing I can think of would be for you to insulate your
external table with a view which does the "NVL" function for you.
Otherwise - you will have to do an INSERT from the external table into
a "real" Oracle table...

Hope this helps...

Sincerely,

Philip