輸入表名後自動生成sqlldr控制檔案儲存過程

自己寫的一個儲存過程輸入表名生成對應表的控制檔案

CREATE OR REPLACE PROCEDURE P_generate_sqlldr_null(p_table_name IN VARCHAR2) AS
l_curr_line LONG;
l_table_name user_tables.table_name%TYPE;
BEGIN
select table_name
into l_table_name
from user_tables
where table_name =upper(p_table_name);
l_curr_line := '
LOAD DATA
INFILE '''||lower(l_table_name)||'.txt'||''' '||'"'||'str X'||'''7C0D0A'''||'"'||'
INTO TABLE ';
l_curr_line := l_curr_line||l_table_name||'
FIELDS TERMINATED BY '||''''||'|\t'||''''||'
TRAILING NULLCOLS
(';
for rec in ( select table_name,column_name,column_id,nullable
from user_tab_columns
where table_name =upper(p_table_name)
order by column_id) loop
if rec.column_id = 1 THEN
IF rec.nullable = 'Y' THEN
l_curr_line := l_curr_line||'
'||rpad(rec.column_name||' NULLIF '||rec.column_name||'='||'''\\N''',80);
ELSE
l_curr_line := l_curr_line||'
'||rpad(rec.column_name,80);
END IF;
ELSE
IF rec.nullable = 'Y' THEN
l_curr_line := l_curr_line||'
,'||rpad(rec.column_name||' NULLIF '||rec.column_name||'='||'''\\N''',80);
ELSE
l_curr_line := l_curr_line||'
,'||rpad(rec.column_name,80);
END IF;

end if;
end loop;
l_curr_line := l_curr_line||')';
sp_write_log(l_curr_line,l_table_name||'.ctl');
END P_generate_sqlldr_null;
/