2017-4-6 Oracle复杂 Merge Into | ORA-00001 | no listener
来源:互联网 发布:苹果手机打不开淘宝网 编辑:程序博客网 时间:2024/05/29 17:38
标签: 有道云笔记
MERGE INTO A_DATA T1USING ( SELECT '21212958881122' AS MSG_ID , '北斗星通1' AS COMM_TYPE , '20261' AS USER_ID , '2818101'AS TERMINAL_CODE, '201704051' AS MSG_DATE , '定时回传位置1' AS POS_TYPE , '2017-04-05 20:56:311' AS UTC , '126°33′431″' AS LONGITUDE , '30°11′01″' AS LATITUDE , '6801' AS COURSE , '01' AS TRUEHEADING , '8.91' AS SPEED , '正常1' AS STATUS , '01' AS VDESC , '北斗星通1' AS TERMINAL_TYPE , '2017-04-05 21:21:401' AS SYSTEM_TIME FROM dual) T2ON ( T1.TERMINAL_CODE = T2.TERMINAL_CODE)WHEN MATCHED THEN UPDATE SET T1.MSG_ID = T2.MSG_ID , T1.COMM_TYPE = T2.COMM_TYPE , T1.USER_ID = T2.USER_ID , T1.MSG_DATE = T2.MSG_DATE , T1.POS_TYPE = T2.POS_TYPE , T1.UTC = T2.UTC , T1.LONGITUDE = T2.LONGITUDE , T1.LATITUDE = T2.LATITUDE , T1.COURSE = T2.COURSE , T1.TRUEHEADING = T2.TRUEHEADING , T1.SPEED = T2.SPEED , T1.STATUS = T2.STATUS , T1.VDESC = T2.VDESC , T1.TERMINAL_TYPE = T2.TERMINAL_TYPE , T1.SYSTEM_TIME = T2.SYSTEM_TIME WHEN NOT MATCHED THEN INSERT (MSG_ID ,COMM_TYPE ,USER_ID ,TERMINAL_CODE ,MSG_DATE ,POS_TYPE ,UTC ,LONGITUDE ,LATITUDE ,COURSE ,TRUEHEADING ,SPEED ,STATUS ,VDESC ,TERMINAL_TYPE ,SYSTEM_TIME ) VALUES(T2.MSG_ID ,T2.COMM_TYPE ,T2.USER_ID ,T2.TERMINAL_CODE ,T2.MSG_DATE ,T2.POS_TYPE ,T2.UTC ,T2.LONGITUDE ,T2.LATITUDE ,T2.COURSE ,T2.TRUEHEADING ,T2.SPEED ,T2.STATUS ,T2.VDESC ,T2.TERMINAL_TYPE ,T2.SYSTEM_TIME);
SQL 错误: ORA-00001: 违反唯一约束条件 (SYSTEM.SYS_C0010160)
00001. 00000 - “unique constraint (%s.%s) violated”
原因:在select语句中的AS部分全部是取值于已存在记录的值,在id相同并执行update时,相当于更新一条完全相同的语句,即便是各个值没有违反唯一性约束,此时也会报: ORA-00001错误,不要全都一样就可以了;
在Java中使用Oracle的MERGE INTO语句时,老师报错:sql语句未正常结束,但在Navcat中完全正常
解决:Navcat中执行时语句的最后有个分号;但在Java中prepareStatement构造时,要去掉这个分号!!!!
连接错误:no listener、The Network Adapter could not establish the connection
1.打开SQL Developer查看本地SYSTEM(例子)是否能连接(密码:ttzzlll)
如不行(报adapter错误等等):
查看Oracle服务启动正常否:
如果未启动,则右键属性,转登录栏,使用账号密码重新登录,之后即可启动
上述完成,看是否可以本地连接,如果仍不行:
执行 lsnrctl start + lsnrctl status 看信息正常否
正常,则打开oracle安装目录下:oracle\product\12.1.0\dbhome_1\network\admin\listener.ora
打开listener.ora,找到最下方LINSTENER=(…)
修改HOST = 主机名(主机名可我的电脑右键属性查看)
保存,之后cmd下:
lsnrctl stop
lsnrctl start
lsnrctl status
有一定的延迟,在客户机上连接,此时OK!!!!
- 2017-4-6 Oracle复杂 Merge Into | ORA-00001 | no listener
- oracle merge into 报 ora-02064错误
- ORACLE ORA-00600: 内部错误代码, 参数: [kokbcvb1]与merge into
- Oracle ORA-12541:TNS:no listener错误解决方法
- 【Oracle数据库】ORA-12541: TNS:no listener 的详细解决方案
- 【Oracle数据库】ORA-12541: TNS:no listener 的详细解决方案
- Oracle ORA-12541:TNS:no listener错误解决方法
- ubuntu下oracle问题 ORA-12541: TNS:no listener
- Oracle ORA-12541:TNS:no listener错误解决方法
- Oracle常见故障解析(2):ORA-12541: tns: no listener
- Oracle select --merge into:
- oracle merge into 测试
- oracle merge into
- oracle merge into 实例
- Oracle 之 MERGE INTO
- Oracle:merge into
- Oracle数据库merge into
- oracle merge into
- Linux安装Python环境
- 在window查看端口是否被占用的快捷命令
- Android学习记录:SQLite数据库、res中raw的文件调用
- 2017-4-19 美国是如何一步步成为超级大国的
- ES6——对yield和yield*的理解
- 2017-4-6 Oracle复杂 Merge Into | ORA-00001 | no listener
- 【实战】android获取天气情况(Json来返回数据)
- html转pdf错误wkhtmltopdf
- 启动解决Mysql启动问题
- 机器学习理论篇之SVM(python实现)
- Hybrid App的架构与实现
- 微信技术总监讲大数据高并发系统架构
- vue.js之快速入门
- UVA