資料來源: 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

Somebaby 發表在 痞客邦 留言(1) 人氣()