DECLARE
str varchar2(100);
u_name varchar2(100);
tab_name varchar2(100);
Type Tab_L is Table of Varchar2(100);
T_Tab Tab_L := Tab_L('CORECAT.FISCAL_PERIOD','CRX_COR.CORPORATE_ACTION',
'CRX_COR.MARKET_DIVIDEND','CRX_COR.EARNINGS_PER_SHARE',
'CFM_COR.COMPANY_PROFILE','CRX_COR.TOTAL_SHARES_OUTSTANDING',
'CFM_COR.PROFIT_AND_LOSS','CFM_COR.REPORT_DETAIL',
'CORECAT.CURRENCY_RSHIP','CORECAT.BODY_ASSET_ROLE',
'CORECAT.CONSENSUS_EST_REPORT','CORECAT.ASSET_ASSOC',
'CFM_COR.BALANCE_SHEET','PAN_COR.HISTORIC_ANALYTIC_RESULT',
'CORECAT.IDENTIFIER','CORECAT.ASSET','CORECAT.PERIODIC_CONSENSUS_EST');
BEGIN
For I in 1..T_Tab.Count Loop
str:=t_tab(i);
select substr(str,1,instr(str,'.',1,1)-1) into u_name from dual;
select substr(str,instr(str,'.',1,1)+1,length(str)) into tab_name from dual;
dbms_output.put_line( u_name||'.'||tab_name || '.ctl' ||' ****************');
dbms_output.put_line( 'load data');
dbms_output.put_line( 'infile '|| u_name||'.' ||tab_name ||'.txt');
dbms_output.put_line( 'into table '|| tab_name);
dbms_output.put_line( 'FIELDS TERMINATED BY '|| chr(39)|| '|' || chr(39) || ' (' );
dbms_output.put_line( 'trailing nullcols');
for j in (select column_name,data_type from dba_tab_columns where owner = 'CORECAT' and table_name=tab_name order by column_id) loop
if j.data_type='DATE' then
dbms_output.put_line(j.column_name || ' date '|| chr(39) || 'YYYY-MM-DD:HH24:MI:SS' || chr(39) || ',');
else
dbms_output.put_line(j.column_name ||',');
end if ;
end loop ;
dbms_output.put_line(chr(10));
End Loop;
END;