Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> External table and null value

External table and null value

From: <ccote_msl_at_yahoo.com>
Date: 13 Jul 2005 06:27:50 -0700
Message-ID: <1121261270.862382.75210@f14g2000cwb.googlegroups.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 Received on Wed Jul 13 2005 - 08:27:50 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US