Re: How do I change the interpreted decimal character in an external table file

From: Fabrizio <fabrizio.magni_at_mycontinent.com>
Date: Wed, 31 Aug 2005 12:07:47 +0200
Message-ID: <43158174$0$34319$892e7fe2@authen.white.readfreenews.net>

Jaap W. van Dijk wrote:
> I searched the page you mentioned ('14 Loading and Transformation' of
> the 'Database Data Warehousing Guide 10g') for 'decimal' and for
> 'territory' and for 'nls_lang', but with no result. I skimmed the text
> but I could not find any reference to changing the decimal character.
> Could you point me to the exact spot on the page?

Jaap,
this is the example I was referring to:

CREATE TABLE sales_transactions_ext
(PROD_ID NUMBER, CUST_ID NUMBER,
  TIME_ID DATE, CHANNEL_ID NUMBER,
  PROMO_ID NUMBER, QUANTITY_SOLD NUMBER,   AMOUNT_SOLD NUMBER(10,2), UNIT_COST NUMBER(10,2),   UNIT_PRICE NUMBER(10,2))
ORGANIZATION external (TYPE oracle_loader

   DEFAULT DIRECTORY data_file_dir ACCESS PARAMETERS    (RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII

     BADFILE log_file_dir:'sh_sales.bad_xt'
     LOGFILE log_file_dir:'sh_sales.log_xt'
     FIELDS TERMINATED BY "|" LDRTRIM
     ( PROD_ID, CUST_ID,
       TIME_ID         DATE(10) "YYYY-MM-DD",
       CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD,
       UNIT_COST, UNIT_PRICE))

   location ('sh_sales.dat')
)REJECT LIMIT UNLIMITED; You can find more hint on CHARCATERSET here (even if referring to sql*loader):

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_control_file.htm#sthref745

This is a stripped example of one of my external tables:

CREATE TABLE FOO
(

    ID NUMBER(15),
    TICKET_ASSIGNED_A_RUOLO VARCHAR2(30) )
ORGANIZATION EXTERNAL
   ( TYPE ORACLE_LOADER

      DEFAULT DIRECTORY ADMIN_DAT_DIR
      ACCESS PARAMETERS
        ( records delimited by newline
                  badfile admin_bad_dir:'foo_sa%a_%p.bad'
                  logfile admin_log_dir:'foo_sa%a_%p.log'
                  CHARACTERSET ITALIAN_ITALY.WE8MSWIN1252
                  fields
                  (
                   ID POSITION (1:15) INTEGER EXTERNAL DEFAULTIF ID=BLANKS ,
                   COD_STATO POSITION (1:1) INTEGER EXTERNAL DEFAULTIF 
COD_STATO=BLANKS
                  )
        )
      LOCATION (ADMIN_DAT_DIR:'RICHIESTA.TXT')

   )
REJECT LIMIT UNLIMITED
NOLOGGING
NOCACHE
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT ); Regards

-- 
Fabrizio Magni

fabrizio.magni_at_mycontinent.com

replace mycontinent with europe

Received on Wed Aug 31 2005 - 05:07:47 CDT