oracle表结构生成器,根据表结构,自动生成导入脚本

以下脚本,自动生成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;