Sqlldr 接受 1 种日期格式

我有一个动态生成控制文件的sql脚本文件。它接受日期格式为 mm/dd/yyyy 的日期字段。 sqlldr 正在从 csv 文件加载日期，但它也接受日期格式，例如“mm\dd\yyyy”或“mm.dd.yyyy”。我如何让它只接受 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';

为什么要担心分隔符？考虑一下这个程序，它是我们的实用程序包的一个成员，我出于类似的需要而将其放在一起。您向它传递一个表名和一个分隔符，它会读取 USER_TAB_COLUMNS 并输出一个骨架控制文件，然后我将其保存到一个文件中（我使用 Toad，但当然您也可以将其假脱机到一个文件中）。我用它所有的时间。虽然不漂亮，但是满足了我的需求。进行调整以满足您的需求，我相信这可以节省您重新输入列名称和数据类型的时间。

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)
)