oracle数据库导入TXT文件方法介绍

<span style="color:#3333ff;">create table user_svc_info( 
  phone varchar2(20) not null, 
  svc_id varchar2(32) not null, 
  p_username varchar2(100) null, 
  user_status number not null , 
  p_alias varchar2(50) null, 
  imsi varchar2(32) null, 
  svc_updatetime timestamp(3) null, 
  svc_type number not null, 
  svc_status number not null, 
  rec_updatetime varchar2(20) null, 
  svc_idk varchar2(32) null, 
  prostart_time varchar2(24) null, 
  proend_time varchar2(24) null, 
  down_type number null )；</span> 

<span style="color:#3333ff;">在e:\sqlldr下创建一个控制命令的脚本文件，通常以.ctl结尾，如info.ctl 
脚本内容如下： 
load data 
infile 'info.txt' 
append into table user_svc_info 
fields terminated by ',' 
trailing nullcols 
(phone,svc_id,p_username,user_status,p_alias,imsi, 
svc_updatetime timestamp 'yyyy-mm-dd hh24:mi:ss.ff', 
svc_type,svc_status,rec_updatetime,svc_idk, 
prostart_time,proend_time,down_type)</span> 

select phone,svc_type,svc_udatetime from user_svc_ifno where rownum<100； 
可以oracle用户登录系统，创建脚本如vim info.sh 
#!/bin/bash 
sqlplus "app/app"<<eof 
set heading off; 
set echo off; 
set feedback off; 
set verify off; 
set wrap off; 
set pagesize 0; 
set linesize 2500; 
set trimout on; 
set trimspool on; 
spool /usr/local/oracle/user_svc_info_sql.txt; 
select phone||','||svc_type||','||svc_updatetime from user_svc_info where rownum<100; 
set define on; 
set heading on; 
set echo on; 
set feedback on; 
spool off 
quit; 
eof 

./info.sh