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, но, конечно, вы также можете добавить его в файл). Я использую это все время. Это не очень, но это отвечает моим потребностям. Тщательность, чтобы удовлетворить ваши потребности, я считаю, что это может сэкономить вам некоторое время, переименовывая имена столбцов и типы данных.

Источник:

 /******************************************************************************************************** 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 

Запустить его:

 exec utl.gen_ctl_file('TEST_TABLE'); 

Вывод:

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

Я нашел ответ. Форматирование даты в oracle позволяет опции FX и FM для точного форматирования

например

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

возвращает 6/21/2016

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

будет возвращать ошибку "literal не соответствует строке формата"

поэтому в моем файле sql-файла управляющего файла я добавил команды FX и FM

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

теперь будут приняты только даты с точно форматом mm / dd / yyyy, а остальные будут отвергнуты как плохая строка