输入表名后自动生成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;
/

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7199859/viewspace-552/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7199859/viewspace-552/