Oracle eBS經驗分享

資料來源: OTN

This tip comes from Navroz Kapadia, DBA, Economics & Business Cluster in Toronto, Ontario, Canada.

This script will generate a SQL*Loader control file for any specified table, which can then be customized as per individual needs. Ensure it is run by a user with SELECT_CATALOG_ROLE. Parameter 1 is the owner of the table. Parameter 2 is the tablename.

set verify off



set heading off



set embedded on



set recsep off



set pages 500



set lines 80



set feedback off



define &&1 = ''



spool c:\your_path_name\&&2..ctl



select 



'load data 



infile ''' || 'C:\your_path_name\' || upper('&2') || '.CSV'''|| ' 



into table ' || upper('&1') || '.' || upper('&2') || '



replace 



fields terminated by '',''' || '



trailing nullcols' 



from all_tables where owner = upper('&1') and table_name = upper('&2')



/



select decode(a.column_id, '1', '(') || a.column_name || 



(case when a.column_id < max(b.column_id) then ',' else ')' END)



from all_tab_columns a, all_tab_columns b



where a.owner = upper('&1') and a.table_name = upper('&2') and



b.owner = upper('&1') and b.table_name = upper('&2')



group by a.column_name, a.column_id



order by a.column_id



/



spool off



set verify on



set heading on



set embedded off



set recsep wrapped



set pages 64



set lines 80



set feedback 6



undefine 1



undefine 2