SQLLDR接受1种类型的日期格式 [英] Sqlldr to accept 1 type of date format
本文介绍了SQLLDR接受1种类型的日期格式的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个动态生成控制文件的sql脚本文件.它接受日期格式为mm/dd/yyyy的日期字段. sqlldr正在从csv文件加载日期，但它也接受日期格式，例如"mm \ dd \ yyyy"或"mm.dd.yyyy".如何使其仅接受MM/DD/YYYY?
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';
推荐答案
为什么要担心分隔符?考虑一下此过程，该过程是我们的实用程序包的成员，出于类似的需要，我将该程序打包在一起.您为它传递一个表名和一个分隔符，它会读取USER_TAB_COLUMNS并输出一个骨架控制文件，然后将其保存到文件中(我使用Toad，但是当然也可以将其假脱机到文件中).我用它所有的时间.它不漂亮，但是可以满足我的需求.进行调整以满足您的需求，我相信这可以为您节省一些重新输入列名和数据类型的时间.
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.
来源:
/********************************************************************************************************
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)
)
这篇关于SQLLDR接受1种类型的日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
