根據表結構,自動生成匯入指令碼

以下指令碼,自動生成sqlldr匯入需要的ctl檔案

使用示例:

exec DUMP_CTL('MY_DIR','a','AUTHEN','APPEND','|',P_ENCLOSURE => NULL,P_TERMINATOR => '|') ;

[@more@]

CREATE OR REPLACE PROCEDURE DUMP_CTL( P_DIR IN VARCHAR2,
P_FILENAME IN VARCHAR2,
P_TNAME IN VARCHAR2,
P_MODE IN VARCHAR2,
P_SEPARATOR IN VARCHAR2,
P_ENCLOSURE IN VARCHAR2,
P_TERMINATOR IN VARCHAR2)

IS
L_OUTPUT UTL_FILE.FILE_TYPE;
L_SEP VARCHAR2(5);
L_STR VARCHAR2(5);
L_PATH VARCHAR2(5);
IS_IN_DATATYPE INTEGER := 0;
G_NL VARCHAR2(2) DEFAULT CHR(10);

cursor c(v_tabname varchar2) is select * from user_tab_columns where table_name = v_tabname order by column_id asc;
BEGIN

dbms_output.put_line(P_DIR);
L_OUTPUT := UTL_FILE.FOPEN( P_DIR, P_FILENAME || '.CTL', 'w' );
UTL_FILE.PUT_LINE( L_OUTPUT, 'LOAD DATA' );
UTL_FILE.PUT_LINE( L_OUTPUT, 'INFILE * ' || ' "STR X''' ||
UTL_RAW.CAST_TO_RAW(chr(10) ) || '''"' );
UTL_FILE.PUT_LINE( L_OUTPUT,P_MODE || ' INTO TABLE ' || P_TNAME );

UTL_FILE.PUT_LINE( L_OUTPUT, 'FIELDS TERMINATED BY ''' ||
P_SEPARATOR ||
''' ENCLOSED BY X''' ||
P_ENCLOSURE || ''' ' );

UTL_FILE.PUT_LINE( L_OUTPUT, '(' );

FOR I IN c(P_TNAME) LOOP

IF ( i.data_type in ('DATE') ) THEN
UTL_FILE.PUT( L_OUTPUT, L_SEP || i.COLumn_NAME ||
' DATE ''DDMMYYYYHH24MISS'' ');
IS_IN_DATATYPE := 1;
END IF;
IF ( i.data_type in ('VARCHAR2','CHAR') ) THEN
UTL_FILE.PUT( L_OUTPUT, rpad(L_SEP || i.COLumn_NAME,20,' ') ||
' CHAR(' ||
TO_CHAR(i.DATA_LENGTH*2) ||' )' );
-- UTL_FILE.PUT( L_OUTPUT,' terminated by '''||P_TERMINATOR||'''' );
END IF;

IF ( i.data_type in ('FLOAT','NUMBER') ) THEN
UTL_FILE.PUT( L_OUTPUT, rpad(L_SEP || i.COLumn_NAME,20,' ')) ;
-- UTL_FILE.PUT( L_OUTPUT,' terminated by '''||P_TERMINATOR||'''' );
IS_IN_DATATYPE := 1;
END IF;

IF IS_IN_DATATYPE = 0 THEN
UTL_FILE.PUT( L_OUTPUT, rpad(L_SEP || i.COLumn_NAME,20,' ') );
END IF;
L_SEP := ','||G_NL ;
--L_SEP := ' terminated by '||P_TERMINATOR||','||G_NL ;
END LOOP;
UTL_FILE.PUT_LINE( L_OUTPUT,G_NL || ')' );
UTL_FILE.FCLOSE( L_OUTPUT );
END;