OraCle外部表数据奇葩问题
朋友们好!，今天在工作中使用外部表遇到了一个奇葩问题，处理思路到最后也很有意思，值得一看，希望这篇文章能帮助到大家。
环境描述：
服务器版本： Red Hat Enterprise Linux Server release 6.5 (Santiago) 64位
数据库版本： oracle Release 11.2.0.3.0版本
测试库实例名：testdb ip :192.168.0.5
开发需求描述：
提供一个execl表的数据，最终目的为：使用此文件中的数据和数据库中进行比对，把生产库的数据更正。
思路：
1 根据要求，在测试库中利用外部表方式，进行构建表（外部表只能读 不能修改内容）。
2 把创建好的外部表在测试库中 create table as 外部表。 创建出一个临时表，可提进行修改。
3 在生产库中创建一个db link 在生产库执行sql 读取测试库的临时表，达成目的。
遇到的问题；
在最后一步中 无法提过外部表的数据进行生产库的修改。
执行步骤：
execl 数据列为：
|
|
唯一号 | 姓名 | 身份证号 | 商社编号 | 辅助编号 | 业务部号 | 业务部 | 业务员号 | 业务员 | 关系转接类型 | 所属人 | 转入日期 | 转出日期 | 转接方式 | 备注 | 缴费人员类别 |
a1 a2 a3 a4 a5 a6 a7 a8 a9 a10 a11 a12 a14 a15 a16
考虑到列名不能为中文，所以把他们代替为：a1,a2,a3......
1 创建外部表
创建逻辑目录并授权:
conn / as sysdba
CREATE DIRECTORY TESTDIR AS '/home/oracle';
GRANT READ,WRITE ON DIRECTORY TESTDIR TO testdb;
conn / as sysdba
CREATE DIRECTORY TESTDIR AS '/home/oracle';
GRANT READ,WRITE ON DIRECTORY TESTDIR TO testdb;
CREATE TABLE testdb.test_date
( a1 number(10),
a2 varchar2(40),
a3 varchar2(20) ,
a4 varchar2(20),
a5 varchar2(20),
a6 varchar2(20),
a7 varchar2(400),
a8 varchar2(20),
a9 varchar2(40),
a10 number(30),
a11 varchar2(40),
a12 varchar2(20),
a13 varchar2(20),
a14 number(20),
a15 varchar2(40),
a16number(40))
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY TESTDIR
ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
(a1 char,
a2 char,
a3 char,
a4 char,
a5 char,
a6 char,
a7 char,
a8 char,
a9 char,
a10 char,
a11 char,
a12 char ,
a13 char ,
a14 char,
a15 char,
a16 char))
LOCATION('test_date.csv')) --我将execl文件传入到服务器的/home/oracle/ 里改名为：test_date.csv
reject limit 40000;
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY TESTDIR
ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
(a1 char,
a2 char,
a3 char,
a4 char,
a5 char,
a6 char,
a7 char,
a8 char,
a9 char,
a10 char,
a11 char,
a12 char ,
a13 char ,
a14 char,
a15 char,
a16 char))
LOCATION('test_date.csv')) --我将execl文件传入到服务器的/home/oracle/ 里改名为：test_date.csv
reject limit 40000;
然后在创建出临时表；
create table test_date1 as select * from test_date
临时表创建OK：
2 创建db link
这里的db link 生产库已经有了 我在这里就不创建了把语法给大家。
CREATE PUBLIC DATABASE LINK link名字 CONNECT TO 用户名 IDENTIFIED BY 密码 USING '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.10)(PORT = 1521)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME =ORCL)))';
3在生产库执行业务sql；
MERGE INTO CS_ORDER ORD
USING (SELECT DISTINCT REC.ORDER_ID, T.A16
FROM ES_INS_REC REC
JOIN TESTDB.TEST_DATE@DB_TESTDB T
ON REC.UNIQ_NO = TO_NUMBER(T.A1)
AND DECODE(REC.PRODUCT_ID, 8, 2, 1) = T.A10
AND T.A14 = 1
WHERE REC.FAILURE_STATE = 1
AND REC.EXECUTE_MON IN (201406, 201407)
AND (REC.EMP_TYPE IS NULL OR REC.EMP_TYPE = -1)) T
ON (ORD.ORDER_ID = T.ORDER_ID)
WHEN MATCHED THEN
UPDATE
SET ORD.PAY_FEES_PER_TYPE = T.A16
WHERE (ORD.PAY_FEES_PER_TYPE IS NULL OR ORD.PAY_FEES_PER_TYPE = -1);
USING (SELECT DISTINCT REC.ORDER_ID, T.A16
FROM ES_INS_REC REC
JOIN TESTDB.TEST_DATE@DB_TESTDB T
ON REC.UNIQ_NO = TO_NUMBER(T.A1)
AND DECODE(REC.PRODUCT_ID, 8, 2, 1) = T.A10
AND T.A14 = 1
WHERE REC.FAILURE_STATE = 1
AND REC.EXECUTE_MON IN (201406, 201407)
AND (REC.EMP_TYPE IS NULL OR REC.EMP_TYPE = -1)) T
ON (ORD.ORDER_ID = T.ORDER_ID)
WHEN MATCHED THEN
UPDATE
SET ORD.PAY_FEES_PER_TYPE = T.A16
WHERE (ORD.PAY_FEES_PER_TYPE IS NULL OR ORD.PAY_FEES_PER_TYPE = -1);
结果一直报错，报无效的数字， SET ORD.PAY_FEES_PER_TYPE = T.A16 这句有问题，我当时猜想，应该是有空数值吧。
然后我这样操作的
查看了一下 test_date1 表的a16列
把16列trim去除空格。
4 然后继续在生产库上执行业务sql：
update test_date1 set a16=trim(a16);
MERGE INTO CS_ORDER ORD
USING (SELECT DISTINCT REC.ORDER_ID, T.A16
FROM ES_INS_REC REC
JOIN TESTDB.TEST_DATE@DB_TESTDB
ON REC.UNIQ_NO = TO_NUMBER(T.A1)
AND DECODE(REC.PRODUCT_ID, 8, 2, 1) = T.A10
AND T.A14 = 1
WHERE REC.FAILURE_STATE = 1
AND REC.EXECUTE_MON IN (201406, 201407)
AND (REC.EMP_TYPE IS NULL OR REC.EMP_TYPE = -1)) T
ON (ORD.ORDER_ID = T.ORDER_ID)
WHEN MATCHED THEN
UPDATE
SET ORD.PAY_FEES_PER_TYPE = T.A16
WHERE (ORD.PAY_FEES_PER_TYPE IS NULL OR ORD.PAY_FEES_PER_TYPE = -1);
USING (SELECT DISTINCT REC.ORDER_ID, T.A16
FROM ES_INS_REC REC
JOIN TESTDB.TEST_DATE@DB_TESTDB
ON REC.UNIQ_NO = TO_NUMBER(T.A1)
AND DECODE(REC.PRODUCT_ID, 8, 2, 1) = T.A10
AND T.A14 = 1
WHERE REC.FAILURE_STATE = 1
AND REC.EXECUTE_MON IN (201406, 201407)
AND (REC.EMP_TYPE IS NULL OR REC.EMP_TYPE = -1)) T
ON (ORD.ORDER_ID = T.ORDER_ID)
WHEN MATCHED THEN
UPDATE
SET ORD.PAY_FEES_PER_TYPE = T.A16
WHERE (ORD.PAY_FEES_PER_TYPE IS NULL OR ORD.PAY_FEES_PER_TYPE = -1);
还是无效数字。----这里当时没有截图所以模拟了一个这个报错贴在这里的，不影响说明问题。
不是空格问题，是什么,当时无奈了，结果同事偶然间一个操作发现了问题所在！！
仔细看！
我们把数字复制贴在了 框框内，之前没有注意 不仅仅有空格 还有一个回车。。光标在第二行呢，原来这里问题，为了这个问题纠结蒸腾了一天。
知道原因了，剩下的就好处理了， 不能直接把1 update为 1 那样没有办法匹配到 因为后面有个空格，
所以需要 这样查找到 1 ， update test_date1 set a16='1' where a16 like '1%';
后来想了想这样执行也不行，你想，这样执行 会把 1 12 10 都改为1的，只能麻烦一些了，，，
先把12该为a 10 改为b
12=a
10=b
update test_date1 set a16='a' where a16 like '12%';
update test_date1 set a16='b' where a16 like '10%';
update test_date1 set a16='b' where a16 like '10%';
然后就可以更新1了。
update test_date1 set a16='7' where a16 like '7%';
update test_date1 set a16='8' where a16 like '8%';
update test_date1 set a16='9' where a16 like '9%';
update test_date1 set a16='1' where a16 like '1%';
update test_date1 set a16='4' where a16 like '4%';update test_date1 set a16='7' where a16 like '7%';
update test_date1 set a16='8' where a16 like '8%';
update test_date1 set a16='9' where a16 like '9%';
最后在把 12 和10 修改回来，虽然有些麻烦是不是很有意思。哈哈
update test_date1 set a16='12' where a16 like 'a%';
update test_date1 set a16='10' where a16 like 'b%';
update test_date1 set a16='10' where a16 like 'b%';
总结一下：
其实说了这么多 就是想说明一点，这个问题是开发提供的execl表内容不工整造成的悲剧，
其实说了这么多 就是想说明一点，这个问题是开发提供的execl表内容不工整造成的悲剧，
这个提示使用外部表的朋友们注意着一点，也为遇到和我同样情况的朋友给一点点小提示和思路，好啦今天就到这里，休息休息~~~
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29479238/viewspace-1226379/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- liunx删除文件控件不释放【lsof | grep delete 不管用 可以看看】
- oracle查询远程视图进行insert语句报错：ORA-00600: 错误 [qerrmOFBu1], [24347], []
- sql查询之【找到是分区表但未使用分区条件的sql】
- 将execl文件数据导入到数据库中（代替外部表的办法）
- linux系统：rm-rf执行以后，怎么办？我来教你恢复文件。
- 在linux系统中，自己制作一个回收站！
- ORACLE网络配置介绍
- 解决【ORA-00265: instance recovery required, cannot set ARCHIVELOG mode】
- oralce的sql语句图案
