Ambiguous column name error after installing new server
Nicolas Avila
Hello
I was using an Orcale 9i server on one of my applications and it
worked fine.
Now I installed a new server and got installed an Oracle 11g
I am getting errors in the application which I didn't get before on my
old server.
The rarest one is a 'column ambiguosly defined' error in some (actualy
many) stored procedures.
Due to many stored procedures do dinamiclly generate the where filter
i can not just fix the ones that didn't compile.
In the 'old' server I DID get many times this error, so I guess (?)
that this error was not happening IF the query was written using ANSI
joins...
Now the questions:
- Was this really so at oracle 9? Is this change reported? I mean to
do NOT support ambiguous column-names in ANSI joins at ORACLE 11.
- Is this maybe configurable? Can I configure the new server to work
as my old one did?
Michael O
Are the column names in your dynamic SQL *actually* ambiguous? If
yes, either improve your stored procedures or keep using what
previously worked. If no, then open an SR with Oracle. Seems simple
enough to me. Of course without knowing more, I am suspecting your
application is what has really changed and is now rejecting what
Oracle 11g is sending it even though it is the same as what 9i was
sending it.
Michael O
http://blog.crisatunity.com
Nicolas Avila
mmmmmmmmm...
I will try to make it easier by using an example
I have some store procedures with something like
(....)
open pCursor for
select tableA_id,
tableA_desc,
tableB_desc
from tableA inner join tableB on (tableA.tableB_id =
tableB.tableB_id)
where tableB_id=5;
(...)
This stored procedure compiled and worked fine on my Oracle 9i, but it
does not even compile on my 11g
So... I fixed all those proceures by just adding the table name on the
filters.
BUT... there are other stored procedures which, in order to use
dynamicly generated filters (based on column names), do something like
-
pFilters is a VARCHAR2 parameter
vSelectStr is a local string variable
-
(...)
vSelectStr:=' select tableA_id, ' ||
' tableA_desc,' ||
' tableB_desc' ||
' from tableA inner join tableB on
(tableA.tableB_id = tableB.tableB_id)' ||
' where ' || pFilters || ';';
open pCursor for vSelectStr;
(...)
And I can not know in which of the thousands of grids will this
automatic filtering actualy raise this ambiguous column issue in any
other way than running a complete regression test. What would take
really long.
So... if
Mark D Powell
>
> - Show quoted text -
There was a bug on some versions of 9i that allowed bad code to run as
the optimizer just assumed that the ambiguous column came from a
specific table for a specific type of ANSI syntax join.
I do not have the bug number but I do have sample code that reproduces
the problem.
UT1 >
UT1 > drop table parent1 ;
Table dropped.
UT1 >
UT1 > create table parent1 ( key number(4) );
Table created.
UT1 >
UT1 > insert into parent1 values ( 1 );
1 row created.
UT1 > insert into parent1 values ( 2 );
1 row created.
UT1 > insert into parent1 values ( 3 );
1 row created.
UT1 > insert into parent1 values ( 4 );
1 row created.
UT1 >
UT1 > drop table child1 ;
Table dropped.
UT1 >
UT1 > create table child1 ( key number(4), ambiguous_column
varchar(1) );
Table created.
UT1 >
UT1 > insert into child1 values ( 1, 'A' );
1 row created.
UT1 > insert into child1 values ( 2, 'B' );
1 row created.
UT1 > insert into child1 values ( 3, null );
1 row created.
UT1 >
UT1 > drop table child2 ;
Table dropped.
UT1 >
UT1 > create table child2 ( ambiguous_column varchar(1), value
number(4) );
Table created.
UT1 >
UT1 > insert into child2 values ( 'A', 1 );
1 row created.
UT1 > insert into child2 values ( 'Z', 26 );
1 row created.
UT1 >
UT1 > select * from parent1 ;
KEY
----------
1
2
3
4
UT1 > select * from child1 ;
KEY A
---------- -
1 A
2 B
3
UT1 > select * from child2 ;
A VALUE
- ----------
A 1
Z 26
UT1 >
UT1 > select key, ambiguous_column from parent1 left outer join child1
using (key)
2 order by key ;
KEY A
---------- -
1 A
2 B
3
4
UT1 >
UT1 > -- This is the query of interest
UT1 > select key, ambiguous_column from parent1 P1 left outer join
child1 C1
2 using (key) left outer join child2 C2
3 on ( C1.ambiguous_column = C2.ambiguous_column ) order by key ;
KEY A
---------- -
1 A
2
3
4
UT1 >
UT1 > select key, C1.ambiguous_column from parent1 P1 left outer join
child1 C1
2 using (key) left outer join child2 C2
3 on ( C1.ambiguous_column = C2.ambiguous_column ) order by key;
KEY A
---------- -
1 A
2 B
3
4
UT1 >
UT1 > spool off
Failure to properly identify column source tables can lead to
incorrect query results depending on the data. I posted this to
metalink back in 2004 but I do not remember if support responded with
a bug number. I failed to record it if they did.
HTH -- Mark D Powell --
Michael O
> mmmmmmmmm...
> I will try to make it easier by using an example
Your original post was clear enough without an example. And my first
post is still the blueprint for your only rational course of action.
Clearly if you are:
#1 unwilling or unable to perform regression testing
#2 certain the application hasn't change
#3 introducing a significant change such as upgrading two releases of
your database server
#4 certain it is breaking your application
Then you must abandon your upgrade to 11g or modify your application
to accommodate 11g. There really are no alternatives. In fact, the
latter isn't really a choice given #1.
Michael O
http://blog.crisatunity.com
Nicolas Avila
ok michael and mark
thanks for your answers
I think I will load an SR and I will schedule a full system test in
the new server
It will take longer than I wanted, but we will anyway upgrade to 11g
Thanks again and best regards
Nicolas Avila
Mark D Powell
>
> - Show quoted text -
I do not know why you want to log an SR. 11g is working correctly.
The bug is in 9i where no error is returned. The potentially wrong
result is however returned since you do not have control over which
table the result is being pulled from.