External table field options

From: Kirmo Uusitalo <kkklll_at_luukku.com.invalid>
Date: Sun, 29 May 2005 22:28:29 +0300
Message-ID: <usuj911c37se1kmioaborqk1kbsm5nl5ab@4ax.com>

Hi,

Given this csv file:
0101;Fox Street 20 A;27052005
00102;42nd Street 20921;25022005
00105;Fox Street 21;00000000

I wish to read its contents into Oracle 9i external table. I have created the following sql statement to create the table but it needs a few fixes.

CREATE OR REPLACE DIRECTORY EXT_TABLES AS 'C:\EXT_TABLES'; drop table addr_ext;

CREATE TABLE addr_ext

	(
	addrcode varchar2(10),
	streetname VARCHAR2(25),
	streetno VARCHAR2(25),
	addrdate date
	)

  ORGANIZATION EXTERNAL
    (

      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY ext_tables
      ACCESS PARAMETERS
      (
        RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY ';'
        MISSING FIELD VALUES ARE NULL
	(
	 addrcode char,
	 streetname char,
	 streetno char,
	 addrdate DATE "DDMMYYYY" NULLIF addrdate='00000000'
	)
      )
      LOCATION ('addr.csv')

    )
  REJECT LIMIT UNLIMITED; The external table gets created but
select * from addr_ext currently doesn'r work as I would it to be:

SQL> select * from addr_ext;

ADDRCODE STREETNAME STREETNO ADDRDATE
---------- -------------------- ---------- -------------

0101       Fox Street 20 A      27052005
00102      42nd Street 20921    25022005
00105      Fox Street 21        00000000

-I would like to remove the 0's from the beginning of the addrcode
field.

I tried to change the

         addrcode char,
x row to

         addrcode char "ltrim(:addrcode,'0')",

but this causes an ORA-29913 error when the table is queried. I believe the above syntax is correct for sql*loader but how to specify this in Oracle External Tables syntax?

-I would like to extract the streetname from the streetaddress field
in the csv file above so that everything starting from the first number after position 5 would be located in the streetno field. This should be possible, but as I'm not too familiar with sql*loader rather complex control file syntax.

I wish to receive it like this
SQL> r
  1* select * from addr_ext

ADDRCODE STREETNAME STREETNO ADDRDATE
---------- -------------------- ---------- -------------

101        Fox Street           20 A       27.05.2005
102        42nd Street          20921      25.02.2005
105        Fox Street           21         <NULL>

I am also wondering how I can I specify external table column having a constant value such as addrdate DATE constant NULL. I tried the constant keyword but didn't even get the syntax correct.

To try by yourself, copy the data rows into c:\ext_tables\addr.csv (or modify the sql accordingly). I'd really appreciate your suggestions.

Regards,

Kirmo Uusitalo Received on Sun May 29 2005 - 14:28:29 CDT