Sqlldr to accept 1 type of date format

Question

I have a sql script file that dynamically generates a control file. It accepts date fields in date formats for mm/dd/yyyy. The sqlldr is loading the dates from the csv file, but it is also accepting date formats such as "mm\dd\yyyy" or "mm.dd.yyyy". How do i make it only accept MM/DD/YYYY?

set echo off ver off feed off pages 0
accept fname prompt 'Enter Name of File: '

spool &fname..ctl


select 'OPTIONS (SKIP=1)' || chr (10) ||
       'LOAD DATA'|| chr (10) ||
       'DISCARDMAX 99999' || chr (10) ||
       'APPEND'||chr (10)||
       'INTO TABLE MY_TABLE' || chr (10)||
       'FIELDS TERMINATED BY '',''' || chr (10)||
       'OPTIONALLY ENCLOSED BY ''"''' || chr (10) ||
       'TRAILING NULLCOLS' || chr(10) ||
       ' (col1,' || chr (10) ||
       '  col2,' || chr (10) ||
       '  col3,' || chr (10) ||
       '  col4,' || chr (10) ||
       '  col5,' || chr (10) ||
       '  col6,' || chr (10) ||
       '  col7 DATE "mm/dd/yyyy",' || chr (10) ||
       '  col8 DATE "mm/dd/yyyy",' || chr (10) ||
       '  col9,' || chr (10) ||
       '  col10,' || chr (10) ||
       '  col11,' || chr (10) ||
       '  col12,' || chr (10) ||
       '  col13,' || chr (10) ||
       '  col14,' || chr (10) ||
       '  col15,' || chr (10) ||
       '  col16,' || chr (10) ||
       '  processid "' || MAX(processid) || '",' || chr (10) ||
       '  staging_id "1",' || chr (10) ||
       '  FILENAME "''&fname''",' || chr (10) ||
       '  LINENO SEQUENCE(1)' || chr (10) ||
       ' )' || chr (10)
from   process_id
where  filename = '&fname';

Official

Answer 1

Why worry about the separator? Consider this procedure that is a member of our utility package that I threw together for a similar need. You pass it a table name and a separator and it reads USER_TAB_COLUMNS and outputs a skeleton control file which I then save into a file (I use Toad but of course you could spool it into a file too). I use it all the time. It's not pretty, but it meets my needs. Tweak to meet your needs, I believe it may save you some time retyping column names and data types.

Source:

/********************************************************************************************************
    Name:       GEN_CTL_FILE

    Desc:       Generates a skeleton control file for loading data via SQL*Loader.

    Args:       tablename_in IN VARCHAR2, delim_in VARCHAR2 DEFAULT '|'

    Returns:    None.

    Usage:      utl.gen_ctl_file('tablename');

    Notes:      Prints a skeleton control file.

                If a template for a fixed-length data file is desired, use 'FIXED' for the delim_in string.

                Example usage:

                set serveroutput on;
                execute utl.gen_ctl_file('tablename', '*');

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.1         6/6/2013    LanceLink        - Created procedure.
   1.2         10/8/2013   LanceLink        - Fixed decode statement. 
                                            - Added option to generate a fixed-length template.
   ************************************************************************************************************************/
  PROCEDURE GEN_CTL_FILE(tablename_in IN VARCHAR2, delim_in VARCHAR2 DEFAULT thc_utl.PIPE) IS
    ERRNULLTABLENAME     CONSTANT NUMBER        := -20103; -- User-defined error numbers and messages.
    ERRNULLTABLENAMEMSG  CONSTANT VARCHAR2(100) := 'A table name is required.';
    USAGE                CONSTANT VARCHAR2(100) := '*   USAGE: UTL.GEN_CTL_FILE(tablename_in IN VARCHAR2, fieldsep_in VARCHAR2 DEFAULT ''|'')';
    v_delim                       VARCHAR2(20)  := NVL(delim_in, utl.PIPE);

    CURSOR COL_CUR  IS
      SELECT COLUMN_NAME, 
      DECODE(COLUMN_ID, 1, ' ', ',') || RPAD(COLUMN_NAME, 32) || case upper(v_delim)
        when 'FIXED' then 'POSITION(99:99) '
        else NULL
      end|| DECODE(DATA_TYPE,
             'VARCHAR2', 'CHAR('||DATA_LENGTH||') NULLIF(' || COLUMN_NAME || '=BLANKS)',
             'FLOAT', 'DECIMAL EXTERNAL NULLIF(' || COLUMN_NAME || '=BLANKS)',
             'NUMBER', DECODE(                                                 DATA_PRECISION,
             0, 'INTEGER EXTERNAL NULLIF (' || COLUMN_NAME || '=BLANKS)',
             DECODE(DATA_SCALE, 0, 'INTEGER EXTERNAL NULLIF (' || COLUMN_NAME || '=BLANKS)', 'DECIMAL EXTERNAL NULLIF (' || COLUMN_NAME || '=BLANKS)')),
             'DATE', 'DATE "MM/DD/YYYY" NULLIF (' || COLUMN_NAME || '=BLANKS)',
             data_type)
               AS COL_DATA
      FROM  USER_TAB_COLUMNS
      WHERE TABLE_NAME = UPPER(tablename_in)
      ORDER BY COLUMN_ID;

  BEGIN

    IF tablename_in IS NULL THEN
      RAISE_APPLICATION_ERROR(ERRNULLTABLENAME, ERRNULLTABLENAMEMSG || CR || USAGE);
    END IF;

    DBMS_OUTPUT.PUT_LINE('--');
    DBMS_OUTPUT.PUT_LINE('-- NOTE - When using DIRECT=TRUE to perform block inserts to a table,');
    DBMS_OUTPUT.PUT_LINE('--        the table''s triggers will not be used! Plan accordingly to');
    DBMS_OUTPUT.PUT_LINE('--        manually perform the trigger actions after loading, if needed.');
    DBMS_OUTPUT.PUT_LINE('--');
    DBMS_OUTPUT.PUT_LINE('OPTIONS (DIRECT=TRUE)');
    DBMS_OUTPUT.PUT_LINE('UNRECOVERABLE');
    DBMS_OUTPUT.PUT_LINE('LOAD DATA');
    DBMS_OUTPUT.PUT_LINE('APPEND');
    DBMS_OUTPUT.PUT_LINE('INTO TABLE ' || UPPER(tablename_in));
    DBMS_OUTPUT.PUT_LINE('EVALUATE CHECK_CONSTRAINTS');
    if upper(v_delim) != 'FIXED' then
      DBMS_OUTPUT.PUT_LINE('FIELDS TERMINATED BY ''' || v_delim || '''');
      DBMS_OUTPUT.PUT_LINE('OPTIONALLY ENCLOSED BY ''""''');
      DBMS_OUTPUT.PUT_LINE('TRAILING NULLCOLS');
    end if;
    DBMS_OUTPUT.PUT_LINE('(');

    -- The cursor for loop construct implicitly opens and closes the cursor.
    FOR COL IN COL_CUR
    LOOP
      IF COL.COLUMN_NAME != 'LOAD_DATE' THEN
        IF COL.COLUMN_NAME = 'LOAD_SEQ_ID' THEN
          dbms_output.put_line(','||RPAD('LOAD_SEQ_ID', 32)||'CONSTANT 0');
        ELSE
          DBMS_OUTPUT.PUT_LINE(COL.COL_DATA);
        END IF;
      END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(')' || CR);

  EXCEPTION
    WHEN OTHERS THEN
      -- if any error occurs, print the SQLCODE message.
      PRINT_ERROR;
  END; -- GEN_CTL_FILE

Run it:

exec utl.gen_ctl_file('TEST_TABLE');

Output:

--
-- NOTE - When using DIRECT=TRUE to perform block inserts to a table,
--        the table's triggers will not be used! Plan accordingly to
--        manually perform the trigger actions after loading, if needed.
--
OPTIONS (DIRECT=TRUE)
UNRECOVERABLE
LOAD DATA
APPEND
INTO TABLE TEST_TABLE
EVALUATE CHECK_CONSTRAINTS
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
COLA                            CHAR(200) NULLIF(COLA=BLANKS)
,COLB                            CHAR(100) NULLIF(COLB=BLANKS)
,COLC                            CHAR(100) NULLIF(COLC=BLANKS)
,COLD                            INTEGER EXTERNAL NULLIF (COLD=BLANKS)
)

Librarianship

Beware of 'CHAR('||DATA_LENGTH||'...) if preprocessing by external user-defined function was used/generated - see https://mcmap.net/q/1409013/-field-in-data-file-exceeds-maximum-length-error for more details. – Vaasa

Answer 2

I found the answer. Date formatting in oracle allows options FX and FM for exact formatting

for example

select to_date('6/21/2016', 'FXfmMM/FXdd/FXYYYY') from dual;

returns 6/21/2016

select to_date('6-21-2016', 'FXfmMM/FXdd/FXYYYY') from dual;

will return error "literal does not match format string"

so in my control file sql script i added the FX and FM commands

'  col7 DATE "FXFMMM/FXDD/FXYYYY",' || chr (10) ||
'  col8 DATE "FXFMMM/FXDD/FXYYYY",' || chr (10) ||

now only dates with exactly mm/dd/yyyy format will be accepted and the rest will be rejected as a bad row

Official