1、从access导出保存为','分割的文本文件。
2、在oracle里建立同结构的表。
3、使用sql*loader,编写控制文件倒入。
2、在oracle里建立同结构的表。
3、使用sql*loader,编写控制文件倒入。
解决方案 »
- 导入.dmp时出错:IMP-00003: 遇到 ORACLE 错误 942
- rownum 和 order by 的问题?
- 求一个sql 语句
- 如图:有限制条件的分类求和语句怎么写?
- oracle 初始化配置文件的配置
- 菜鸟求助...oracle 10g对象权限问题
- oracle developer的连接问题
- 请问 oracle 9i 是不是一定要在有网络环境下才能使用?
- 求助一个问题关于sql字符串合并
- oracle备份出了一点问题,请教各位前辈
- JAVA或JSP语言中如何与操作系统为red hat linux 7.2数据库版本为8.1.7的服务器相连??????
- 存储过程中对delete from a where id=1;的错误如何判断?在线等待!!!!
FYI:
External tables are flat files stored outside of the database that Oracle treats as a table.The data is read-only and no indexes can be created.Object rights are controlled through ‘SELECT TABLE’ and ‘READ DIRECTORY’ privileges.UTL_FILE_DIR must be set appropriately.CREATE DIRECTORY external_tables AS ‘c:\oracle\oradata\freeney9\external’;CREATE TABLE EMP_EXT (EMPNO NUMBER(4,0), ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4,0), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2,0))
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY external_tables
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
BADFILE external_tables:‘bad_emp_ext.txt’
LOGFILE external_tables:‘log_emp_ext.txt’
FIELDS TERMINATED BY ‘,’
MISSING FIELD VALUES ARE NULL)
LOCATION (‘emp.txt’))
REJECT LIMIT UNLIMITED;
INFILE 'ULCASE7.DAT'
APPEND
INTO TABLE emp
WHEN (57) = '.'
TRAILING NULLCOLS
(hiredate SYSDATE,
deptno POSITION(1:2) INTEGER EXTERNAL(3)
NULLIF deptno=BLANKS,
job POSITION(7:14) CHAR TERMINATED BY WHITESPACE
NULLIF job=BLANKS "UPPER(:job)",
mgr POSITION(28:31) INTEGER EXTERNAL
TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
ename POSITION(34:41) CHAR
TERMINATED BY WHITESPACE "UPPER(:ename)",
empno POSITION(45) INTEGER EXTERNAL
TERMINATED BY WHITESPACE,
sal POSITION(51) CHAR TERMINATED BY WHITESPACE
"TO_NUMBER(:sal,'$99,999.99')",
comm INTEGER EXTERNAL ENCLOSED BY '(' AND '%'
":comm * 100"
)
G:\>sqlldr scott/tiger control=I:\db\db_infor_loadSQL*Loader: Release 9.2.0.1.0 - Production on 星期四 8月 22 15:19:24 2002Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL*Loader-704: 内部错误: ulconnect: OCIServerAttach [0]
ORA-12560: TNS: 协议适配器错误
控制文件是这样写的
LOAD DATA
INFILE 'db_infor.dat'
INTO TABLE db_infor
FIELDS TERMINATED BY ','
(id SEQUENCE (MAX,1) ,
name CHAR(10),
sex CHAR(10))
insert (or append)
INTO TABLE db_infor http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76955/ch05.htm#22746
BDE(Borland Database Engine ) Administrator和Datapump将数据从
asscess库中迁移到Oracle数据库中。