Re: External table field options

From: Barbara Boehmer <baboehme_at_hotmail.com>
Date: 29 May 2005 19:41:59 -0700
Message-ID: <1117420919.569534.32650@z14g2000cwz.googlegroups.com>

Well, it isn't a csv (comma separated value) file, since it is separated by semicolons, but that really doesn't matter. You can trim the zeroes from in front of the addrcode, but why not make a number column instead? I have been in the same situation, where you don't have control of the structure that the data comes from or goes into and you have to parse an address column and load the data into separate fields in a new table. Although nothing is foolproof, most conversions are better than not converting it, or losing the data. I like to use functions to extract and validate. You can use such functions directly in SQL*Loader, but I don't think you can use them directly in external tables. However, you can load your data into a staging table, then insert it into your other table using the functions. Please see the demonstration below, which shows how to do it either using SQL*Loader or an external table and an insert. The functions were created to your specifications, but you may want to test, check your data, and see if you need to make revisions.

functions: scott_at_ORA92> CREATE OR REPLACE FUNCTION extract_streetname 2 (p_streetname IN VARCHAR2) 3 RETURN VARCHAR2 4 AS 5 v_int INTEGER := 6; 6 BEGIN 7 WHILE SUBSTR (p_streetname, v_int, 1) 8 NOT IN ('1', '2', '3', '4', '5', '6', '7', '8', '9') 9 LOOP 10 v_int := v_int + 1; 11 END LOOP; 12 RETURN SUBSTR (p_streetname, 1, v_int - 1); 13 EXCEPTION 14 WHEN OTHERS THEN 15 RETURN p_streetname; 16 END extract_streetname; 17 /

Function created.

scott_at_ORA92> SHOW ERRORS
No errors.
scott_at_ORA92> CREATE OR REPLACE FUNCTION extract_streetno   2 (p_streetname IN VARCHAR2)
  3 RETURN VARCHAR2
  4 AS
  5 v_streetno VARCHAR2(25) := SUBSTR (p_streetname, 6);   6 BEGIN
  7 WHILE SUBSTR (v_streetno, 1, 1) NOT IN   8 ('1', '2', '3', '4', '5', '6', '7', '8', '9')   9 LOOP
10 v_streetno := SUBSTR (v_streetno, 2); 11 END LOOP;
12 RETURN v_streetno;
13 EXCEPTION
14 WHEN OTHERS THEN
15 RETURN NULL;
16 END extract_streetno;
17 /

Function created.

scott_at_ORA92> SHOW ERRORS
No errors.
scott_at_ORA92> CREATE OR REPLACE FUNCTION my_to_date   2 (p_date IN VARCHAR2)
  3 RETURN DATE
  4 AS
  5 BEGIN
  6 RETURN TO_DATE (p_date, 'DDMMYYYY');   7 EXCEPTION
  8 WHEN OTHERS THEN
  9 RETURN NULL;
10 END my_to_date;
11 /

Function created.

scott_at_ORA92> SHOW ERRORS
No errors.

table: scott_at_ORA92> CREATE TABLE addr_ext 2 (addrcode VARCHAR2(10), 3 streetname VARCHAR2(25), 4 streetno VARCHAR2(25), 5 addrdate DATE) 6 /

Table created.

SQL*Loader control file and load: LOAD DATA INFILE 'c:\ext_tables\addr.csv' INTO TABLE addr_ext FIELDS TERMINATED BY ';' TRAILING NULLCOLS (addrcode "LTRIM (:addrcode, '0')", streetname "extract_streetname (:streetname)", addrdate "my_to_date (:addrdate)", streetno "extract_streetno (:streetname)")

scott_at_ORA92>-HOST SQLLDR scott/tiger CONTROL=addr.ctl LOG=addr.log

or external table and insert: scott_at_ORA92> CREATE OR REPLACE DIRECTORY ext_tables AS 'c:\ext_tables' 2 /

Directory created.

scott_at_ORA92> CREATE TABLE addr_ext_load
2 (addrcode VARCHAR2(10),

  3  	streetname VARCHAR2(25),
  4  	addrdate   VARCHAR2(8))

5 ORGANIZATION external
6 (TYPE ORACLE_LOADER

  7  	DEFAULT DIRECTORY ext_tables
  8  	ACCESS PARAMETERS
  9  	  (RECORDS DELIMITED BY NEWLINE
 10  	   FIELDS TERMINATED BY ";"
 11  	   MISSING FIELD VALUES ARE NULL
 12  	     (addrcode	 CHAR,
 13  	      streetname CHAR,
 14  	      addrdate	 CHAR))
 15  	LOCATION ('addr.csv'))

16 REJECT LIMIT UNLIMITED
17 /

Table created.

scott_at_ORA92> INSERT INTO addr_ext (addrcode, streetname, streetno, addrdate)
2 SELECT LTRIM (addrcode, '0'),

  3  	    extract_streetname (streetname),
  4  	    extract_streetno (streetname),
  5  	    my_to_date (addrdate)

6 FROM addr_ext_load
7 /

3 rows created.

results either way: scott_at_ORA92> SELECT * FROM addr_ext 2 /

ADDRCODE   STREETNAME                STREETNO                  ADDRDATE
---------- ------------------------- -------------------------
---------
101        Fox Street                20 A
27-MAY-05
102        42nd Street               20921
25-FEB-05
105        Fox Street                21

scott_at_ORA92> Received on Sun May 29 2005 - 21:41:59 CDT