用sqlldr阿,生成控制文件:
SCRIPT TO GENERATE CONTROL FILE AND DATA FILE FOR USE WITH SQL*LOADER:
---------------------------------------------------------------------/*
||
|| Description:
|| Given a tablename generates
|| 1. Flat ascii file with delimiters
|| 2. SQL*Load control file to load the data
||
*/set echo off
set doc off
set pagesize 0
set feedback off
set verify off
set pause offaccept vtbl_name prompt 'Enter table name: 'define ColumnDelim = "|"
define DateFormat = "yyyymmddhhmiss"define dataname = &&vtbl_name..datspool data.sqlprompt ALTER SESSION SET NLS_DATE_FORMAT="&DateFormat.";;
--
-- Header info
--
prompt /*
prompt || Script Name: data.sql
prompt || Author: Ramesh K Meda
prompt || Date: Feb 1995 (Does day matter?)
prompt || Copyright info: Feel free to copy!
prompt || Fees: As you please!
prompt */prompt clear columns--
-- Set up column formats
--select 'column ' || column_name || ' format ' ||
DECODE (data_type,
'DATE', 'A14'
,'NUMBER' ,
decode (data_scale, 0,
rpad('0', nvl(data_precision - nvl(data_scale,0), 38), '0')
, rpad('0', nvl(data_precision - nvl(data_scale,0), 38), '0')
|| '.' ||
rpad('0', nvl(data_scale, 5), '0'))
,'A' || data_length
)
from user_tab_columns
where table_name = UPPER('&&vtbl_name')
order by column_id;--
-- Set Line size to export data
--
select 'set linesize '||
sum(DECODE(data_type
,'DATE', 25
,'NUMBER', nvl(data_precision,45) + 5
, data_length + 5
)
)
from user_tab_columns
where table_name = UPPER('&&vtbl_name');prompt set echo off
prompt set pagesize 0
prompt set space 0
prompt set feedback off
prompt set verify off
prompt set pause off
prompt set termout offprompt spool &datanameprompt select
select decode(column_id, 1, ' ', ',')
, column_name
, ',''&ColumnDelim.'''
from user_tab_columns
where table_name = upper('&&vtbl_name')
order by column_id
/
prompt from &&vtbl_name;;prompt spool off
prompt set pagesize 15
prompt set feedback on
prompt set verify on
prompt set heading on
prompt set linesize 80
prompt set termout onspool off--
-- Create control file
--
prompt Generating control file for SQL*Load
set lines 100
spool &&vtbl_name..ctlprompt
prompt load data
prompt replace
prompt into table &vTbl_Name
prompt (select decode (column_id, 1, ' ', ',') ||
column_name ||
chr(9) ||
decode(data_type, 'DATE', ' DATE "&DateFormat"', ' CHAR ') ||
chr(9) ||
' terminated by ''&ColumnDelim.'' ' ||
' nullif ' ||
column_name ||
' = blanks '
from user_tab_columns
where table_name = upper('&vtbl_name')
order by column_id
/prompt )spool offclear scree
prompt Generating data [email protected] Files generated:
prompt SQL script: data.sql
prompt data file: &&vtbl_name..dat
prompt control: &&vtbl_name..ctl
SCRIPT TO GENERATE CONTROL FILE AND DATA FILE FOR USE WITH SQL*LOADER:
---------------------------------------------------------------------/*
||
|| Description:
|| Given a tablename generates
|| 1. Flat ascii file with delimiters
|| 2. SQL*Load control file to load the data
||
*/set echo off
set doc off
set pagesize 0
set feedback off
set verify off
set pause offaccept vtbl_name prompt 'Enter table name: 'define ColumnDelim = "|"
define DateFormat = "yyyymmddhhmiss"define dataname = &&vtbl_name..datspool data.sqlprompt ALTER SESSION SET NLS_DATE_FORMAT="&DateFormat.";;
--
-- Header info
--
prompt /*
prompt || Script Name: data.sql
prompt || Author: Ramesh K Meda
prompt || Date: Feb 1995 (Does day matter?)
prompt || Copyright info: Feel free to copy!
prompt || Fees: As you please!
prompt */prompt clear columns--
-- Set up column formats
--select 'column ' || column_name || ' format ' ||
DECODE (data_type,
'DATE', 'A14'
,'NUMBER' ,
decode (data_scale, 0,
rpad('0', nvl(data_precision - nvl(data_scale,0), 38), '0')
, rpad('0', nvl(data_precision - nvl(data_scale,0), 38), '0')
|| '.' ||
rpad('0', nvl(data_scale, 5), '0'))
,'A' || data_length
)
from user_tab_columns
where table_name = UPPER('&&vtbl_name')
order by column_id;--
-- Set Line size to export data
--
select 'set linesize '||
sum(DECODE(data_type
,'DATE', 25
,'NUMBER', nvl(data_precision,45) + 5
, data_length + 5
)
)
from user_tab_columns
where table_name = UPPER('&&vtbl_name');prompt set echo off
prompt set pagesize 0
prompt set space 0
prompt set feedback off
prompt set verify off
prompt set pause off
prompt set termout offprompt spool &datanameprompt select
select decode(column_id, 1, ' ', ',')
, column_name
, ',''&ColumnDelim.'''
from user_tab_columns
where table_name = upper('&&vtbl_name')
order by column_id
/
prompt from &&vtbl_name;;prompt spool off
prompt set pagesize 15
prompt set feedback on
prompt set verify on
prompt set heading on
prompt set linesize 80
prompt set termout onspool off--
-- Create control file
--
prompt Generating control file for SQL*Load
set lines 100
spool &&vtbl_name..ctlprompt
prompt load data
prompt replace
prompt into table &vTbl_Name
prompt (select decode (column_id, 1, ' ', ',') ||
column_name ||
chr(9) ||
decode(data_type, 'DATE', ' DATE "&DateFormat"', ' CHAR ') ||
chr(9) ||
' terminated by ''&ColumnDelim.'' ' ||
' nullif ' ||
column_name ||
' = blanks '
from user_tab_columns
where table_name = upper('&vtbl_name')
order by column_id
/prompt )spool offclear scree
prompt Generating data [email protected] Files generated:
prompt SQL script: data.sql
prompt data file: &&vtbl_name..dat
prompt control: &&vtbl_name..ctl
可以用其他方法,
1)远程连接数据库,直接操作数据库,要求Oracle放在网上;
2)通过Web server,用Asp或Asp.net页面操作数据库;
3)通过Web server建立Web Service,远程调用Web Service,以更新数据
可是,一个一个的倒入居然这么慢
语句是:
WinExecAndWait32('COMMAND.COM /C sqlldr cnls/cnls@cnls control=c:\temp\cph.ctl errors=1000',SW_hide);
(用delphi语言)