Upgrade database in Oracle to 10.2.0.5 encounter ora-00918:column ambiguously defined

A ORA-00918 error occurred after a database was upgraded from 10201 to 10205, and the query MoS found that in previous versions it was bug,oracle as if it had been repaired in 10205, The result is that the previously applied SQL failed to execute correctly. The result of this upgrade is that customers contact the developer at 3 o'clock to update the program urgently. Again: Small system database upgrades need to do, functional testing, spa testing, to ensure that after the upgrade function and performance are normal.

Sql> select * from V$version;

Perform an error ORA-00918

Multiple table join joins, because the column in the select does not specify a table name, and the column is in more than one table, so the ORA-00918 error is reported in 10205, and Oracle considers the bug 5368296:sql not in the previous version generating ORA-918 when USING JOIN. To upgrade to 10.2.0.5, 11.1.0.7 and version 11.2.0.2, you need to be aware of this type of problem. Fixing bugs is fine, but fixing them will cause the system to need to modify SQL to be able to run, which is really very silent

Sql> set Autot Trace
Sql> Set Lines 100
Sql> SELECT yz_id, Item_code, DECODE (YZLX, 0, ' long-term physician's advice ', ' temporary orders ') YZLX,
2 Item_name, GG, SL | | SLDW SL, Zyjs, YF, a.pc, ZBJ, ZBH,
3 To_char (DCL, ' fm9999990.009 ') | | Dcldw DCL, a.bz, LB, ZYH,CH,XM,
4 BQ, CFH, LRYSDM, Lrysxm, Lrrq, HDRDM, Hdrxm, HDRQ, Sender_code,
5 Sender_name, Send_date, TZYSDM, Tzysxm, Tzrq, KSRQ, Zxfy,
6 Lb_yp_yl, Zsq_code
7 from Op.yz a left OUTER JOIN op.pc b
8 on NVL (Trim (UPPER (a.pc)), ') = NVL (Trim (UPPER (b.pc)), ' "
9 left JOIN Op.zy p on a.zyh = P.zyh
Where p.cy= ' in the courtyard ' and p.new_patient= ' 1 '
One and Upper (NVL (p.bj,1)) <> ' Y '
(state = ' reconciled ')
Is_in_bill is NULL
by KSRQ, yz_id;
BQ, CFH, LRYSDM, Lrysxm, Lrrq, HDRDM, Hdrxm, HDRQ, Sender_code,
*
ERROR at line 4:

Ora-00918:column ambiguously defined
 
sql> select Column_name,table_name from Dba_tab_columns where Column_name= ' BQ '
  2  and table_name in (' YZ ', ' ZY ', ' PC ');
 
column_name   & nbsp;                TABLE_NAME
- -----------------------------------------------------------
bq                               ZY
bq                              YZ
10.2.0.1 normal

E:\>sqlplus/as SYSDBA

Sql*plus:release 10.2.0.1.0-production on Saturday January 3 14:09:51 2015

Copyright (c) 1982, +, Oracle. All rights reserved.


Connect to:

Oracle Database 10g Enterprise Edition release 10.2.0.1.0-64bit Production
With the partitioning, OLAP and Data Mining options

Sql> set Autot Trace
Sql> Set Lines 100
Sql> SELECT yz_id, Item_code, DECODE (YZLX, 0, ' long-term physician's advice ', ' temporary orders ') YZLX,
2 Item_name, GG, SL | | SLDW SL, Zyjs, YF, a.pc, ZBJ, ZBH,
3 To_char (DCL, ' fm9999990.009 ') | | Dcldw DCL, a.bz, LB, zyh,ch,xm
,
4 BQ, CFH, LRYSDM, Lrysxm, Lrrq, HDRDM, Hdrxm, HDRQ, Sender_code,
5 Sender_name, Send_date, TZYSDM, Tzysxm, Tzrq, KSRQ, Zxfy,
6 Lb_yp_yl, Zsq_code
7 from Op.yz a left OUTER JOIN op.pc b
8 on NVL (Trim (UPPER (a.pc)), ') = NVL (Trim (UPPER (b.pc)), '
')
9 left JOIN Op.zy p on a.zyh = P.zyh
Where p.cy= ' in the courtyard ' and p.new_patient= ' 1 '
One and Upper (NVL (p.bj,1)) <> ' Y '
(state = ' reconciled ')
Is_in_bill is NULL
by KSRQ, yz_id;

19804 rows have been selected.


Execution plan
----------------------------------------------------------
ERROR:
ORA-00604: Recursive SQL Level 2 error occurred
ORA-16000: Open the database for read-only access


sp2-0612: Error generating Autotrace EXPLAIN report

Statistical information
----------------------------------------------------------
1 Recursive calls
0 db Block gets
41945 consistent gets
0 physical Reads
0 Redo Size
2075973 Bytes sent via sql*net to client
14989 bytes received via sql*net from client
1322 Sql*net roundtrips To/from Client
1 Sorts (memory)
0 Sorts (disk)
19804 rows processed
Add table name prefix to 10.2.0.5 library in the same column to perform OK
1
Sql> set Autot Trace
Sql> Set Lines 100
Sql> SELECT yz_id, Item_code, DECODE (YZLX, 0, ' long-term physician's advice ', ' temporary orders ') YZLX,
2 Item_name, GG, SL | | SLDW SL, Zyjs, YF, a.pc, ZBJ, ZBH,
3 To_char (DCL, ' fm9999990.009 ') | | Dcldw DCL, a.bz, LB,ZYH,CH,XM,
4 A.BQ, CFH, LRYSDM, Lrysxm, Lrrq, HDRDM, Hdrxm, HDRQ, Sender_code,
5 Sender_name, Send_date, TZYSDM, Tzysxm, Tzrq, KSRQ, Zxfy,
6 Lb_yp_yl, Zsq_code
7 from Op.yz a left OUTER JOIN op.pc b
8 on NVL (Trim (UPPER (a.pc)), ') = NVL (Trim (UPPER (b.pc)), ' "
9 left JOIN Op.zy p on a.zyh = P.zyh
Where p.cy= ' in the courtyard ' and p.new_patient= ' 1 '
One and Upper (NVL (p.bj,1)) <> ' Y '
(state = ' reconciled ')
Is_in_bill is NULL
by KSRQ, yz_id;

20629 rows selected.


Execution Plan
----------------------------------------------------------
Plan Hash value:3468887510

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------------------------
| 0 |            SELECT STATEMENT |    |  10 |  2580 | 2968 (2) | 00:00:36 |
|  1 |            SORT ORDER BY |    |  10 |  2580 | 2968 (2) | 00:00:36 |
|* 2 |            HASH JOIN OUTER |    |  10 |  2580 | 2967 (2) | 00:00:36 |
|* 3 | TABLE ACCESS by INDEX rowid|     YZ |   3 |    672 | 42 (0) | 00:00:01 |
|     4 |            NESTED LOOPS |    |  10 |  2390 | 2963 (2) | 00:00:36 |
|* 5 | TABLE ACCESS Full |     ZY |    3 |  45 | 2917 (2) | 00:00:36 |
|* 6 | INDEX RANGE SCAN |   Dzblyz_zyh |       118 |     | 2 (0) | 00:00:01 |
|    9 2 TABLE ACCESS Full |    PC |   33 |     627 | 3 (0) | 00:00:01 |
--------------------------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

2-access (NVL (TRIM (UPPER) ("A".) PC ")," =nvl "(TRIM (UPPER) (" B "). PC "(+))),")
3-filter ("A".) State "= ' has checked ' and" A "." Is_in_bill ' is NULL)
5-filter ("P".) CY "= ' in the Courtyard ' and UPPER (NVL (" P ".) BJ ", ' 1 ')) <> ' Y ' and
"P". " New_patient "= ' 1")
6-access ("A".) ZYH "=" P "." ZYH ")


Statistics
----------------------------------------------------------
0 Recursive calls
0 db Block gets
42121 consistent gets
0 physical Reads
0 Redo Size
2181383 Bytes sent via sql*net to client
15617 bytes received via sql*net from client
1377 sql*net roundtrips To/from Client
1 Sorts (memory)
0 Sorts (disk)
20629 rows processed
Bug 5368296:sql not generating ORA-918 when USING JOIN
Bug 12388159:sql REPORTING ORA00918 after UPGRADE to 10.2.0.5.0

Again: Small system database upgrades need to do, functional testing, spa testing, to ensure that after the upgrade function and performance are normal.