Re: How to process data within SQL*Loader?

winstead_at_cs.tulane.edu (Teddy Winstead) wrote:
>I'm trying to read in a flat file with SQL*Loader. I'd like to strip
>the "-" characters out of the social security numbers in the text file.
>
>In other words, I want to go from "111-22-3333" to "111223333".
>
>Is there an easy way to do this?
>
>--
>Nathaniel Scott "Teddy" Winstead | http://www.cs.tulane.edu/www/Winstead
>winstead_at_cs.tulane.edu (Preferred) | WARNING! -- The views expressed are
>winstead%brauerei.uucp_at_cs.tulane.edu | those of a platypus and are not
>Fanatical Homebrewer & CS Student | necessarily my own.

The following is the ulcase7.ctl sample control file (found in $ORACLE_HOME/rdbms/demo):

LOAD DATA
INFILE 'ulcase7.dat'
APPEND
INTO TABLE emp
  WHEN (57)='.'
  TRAILING NULLCOLS
  (hiredate SYSDATE,
   deptno POSITION(1:2) INTEGER EXTERNAL(3)

            NULLIF deptno=BLANKS,
   job      POSITION(7:14)   CHAR  TERMINATED BY WHITESPACE
            NULLIF job=BLANKS  "UPPER(:job)",
   mgr      POSITION(28:31)  INTEGER EXTERNAL TERMINATED BY WHITESPACE
            NULLIF mgr=BLANKS,
   ename    POSITION (34:41) CHAR  TERMINATED BY WHITESPACE
            "UPPER(:ename)",
   empno    INTEGER EXTERNAL  TERMINATED BY WHITESPACE,
   sal      POSITION(51)  CHAR  TERMINATED BY WHITESPACE
            "TO_NUMBER(:sal,'$99,999.99')",
   comm     INTEGER EXTERNAL  ENCLOSED BY '(' AND '%'
            ":comm * 100"

Notice the line with sal on it. It shows how to use SQL functions on incoming data.  You can use "REPLACE( :ssn, '-', '' )" on your ssn field to replace your - with nothing. Any SQL function can be used as the above example illustrates. Note, you cannot use the direct path loader if you are using SQL functions as the direct path bypasses the sql engine all together.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Fri Jun 23 1995 - 00:00:00 CEST