Re: Ambiguous column name error after installing new server

On Jan 28, 12:07 pm, Michael O <cleveridea...._at_gmail.com> wrote:
> On Jan 28, 8:02 am, Nicolas Avila <nicolasav..._at_gmail.com> wrote:
>
>
>
> > 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?
>
> 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 Ohttp://blog.crisatunity.com

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 || ';';

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 Received on Mon Jan 28 2008 - 11:11:28 CST