All geek questions in one place

Difference in choice for upgrade ... in Oracle Database 10g and 11g

I found out that Oracle Database 10g and 11g handle the following PL / SQL block differently (for convenience I use the scott schema):

DECLARE v_ename bonus.ename%TYPE; BEGIN SELECT b.ename INTO v_ename FROM bonus b JOIN emp e ON b.ename = e.ename JOIN dept d ON d.deptno = e.deptno WHERE b.ename = 'Scott' FOR UPDATE OF b.ename; END; /

While in 10g (10.2) this code succeeds (the NO_DATA_FOUND exception is well eliminated, but this is expected), in 11g (11.2) it throws a "column ambiguously defined" exception. And it is definitely not expected. It seems that it does not take into account the alias of the table, because I found that when I change the column in FOR UPDATE format e.empno (also does not work) to e.mgr (which is unique), it starts to work. So is this a bug in 11g? Any thoughts?

+4
source share
2 answers

An error was detected in Oracle 10g that was fixed in 11g, where the ORA-00918 column, ambiguously defined, did not rise when it should, I am not sure if this applies to yours, though, because you specified all the aliases.

Is there a foreign key relation acting on bonus.ename → emp.ename?

Have you tried using join syntax without ANSI, for example:

 SELECT b.ename INTO v_ename FROM bonus b, emp e, dept d WHERE b.ename = 'Scott' AND b.ename = e.ename AND d.deptno = e.deptno FOR UPDATE OF b.ename;
0
source

This is a bug in Oracle 11G. It has been fixed in version 11.2.0.2. It was discussed in this thread: https://forums.oracle.com/forums/thread.jspa?threadID=2314477

+2
source

Source: https://habr.com/ru/post/1308340/

More articles:


All Articles