10 Replies Latest reply on Sep 26, 2018 5:32 AM by Madhav Pise

    getting error"java.sql.SQLSyntaxErrorException: ORA-00918: column ambiguously defined" InternalServerException [statusCode=500

    Madhav Pise

      Hi All,

       

      I am getting one error  as "java.sql.SQLSyntaxErrorException: ORA-00918: column ambiguously defined"

       

      summary:: I am trying to run the script

      "

      select a.application_id,b.application_id,b.profile_option_id,b.profile_option_name,b.last_update_date,b.last_updated_by,b.creation_date,

      b.created_by,b.last_update_login,b.write_allowed_flag,b.read_allowed_flag,b.user_changeable_flag,b.user_visible_flag,b.site_enabled_flag,b.site_update_allowed_flag,b.app_enabled_flag,

      b.app_update_allowed_flag,b.resp_enabled_flag,b.resp_update_allowed_flag,b.user_enabled_flag,b.user_update_allowed_flag,b.start_date_active,null,b.end_date_active,

      null,null,null,null,null,c.user_profile_option_name,c.description,c.language,d.profile_option_value

      from fnd_application a, fnd_profile_options b,fnd_profile_options_tl c, fnd_profile_option_values d

      where(a.application_id =d.level_value

      and b.application_id=d.application_id

      and d.profile_option_id=b.profile_option_id

      and b.profile_option_name=c.profile_option_name)"

       

      this script works fine when executing using oracle "sqldeveloper"  but it is giving me error when I give GET call for this query as API using ORDS(Oracle Rest Data Services). ( the above query belong to object present in Oracle APPS)

       

      kindly provide solution to this if anyone is working on ORDS.

       

      below is the complete error stack which I am facing::

       

      Sep 25, 2018 1:39:21 AM 

      SEVERE: java.sql.SQLSyntaxErrorException: ORA-00918: column ambiguously defined

       

       

      InternalServerException [statusCode=500, reasons=[]]

      at oracle.dbtools.http.errors.jdbc.JDBCServletResponseExceptions.handle(JDBCServletResponseExceptions.java:54)

      at oracle.dbtools.rest.resource.generator.json.JSONResourceGeneratorBase.service(JSONResourceGeneratorBase.java:75)

      at oracle.dbtools.rest.resource.generator.json.JSONCollectionResourceGenerator.service(JSONCollectionResourceGenerator.java:61)

      at oracle.dbtools.rest.resource.generator.ResourceGeneratorsProvider.generate(ResourceGeneratorsProvider.java:99)

      at oracle.dbtools.rest.resource.modules.ModulesDispatcherDelegate.service(ModulesDispatcherDelegate.java:132)

      at oracle.dbtools.rest.resource.modules.ModulesDispatcherDelegate.access$100(ModulesDispatcherDelegate.java:26)

      at oracle.dbtools.rest.resource.modules.ModulesDispatcherDelegate$GetDispatcher.doGet(ModulesDispatcherDelegate.java:174)

      at oracle.dbtools.http.etags.EntityTagsProvider.etag(EntityTagsProvider.java:114)

      at oracle.dbtools.rest.resource.modules.ModulesDispatcherDelegate.service(ModulesDispatcherDelegate.java:58)

      at oracle.dbtools.rest.resource.jdbc.JDBCResourceDispatcher.service(JDBCResourceDispatcher.java:77)

      at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)

      at oracle.dbtools.http.servlet.DispatchableServletBase.service(DispatchableServletBase.java:58)

      at oracle.dbtools.rest.resource.cache.CachedResourceDispatcher.service(CachedResourceDispatcher.java:59)

      at oracle.dbtools.http.entrypoint.Dispatcher.dispatch(Dispatcher.java:125)

      at oracle.dbtools.http.entrypoint.EntryPoint$FilteredServlet.service(EntryPoint.java:239)

      at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:73)

      at oracle.dbtools.http.forwarding.QueryFilteringRewrite.doFilter(QueryFilteringRewrite.java:90)

      at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)

      at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)

      at oracle.dbtools.http.forwarding.ForwardingFilter.doFilter(ForwardingFilter.java:68)

      at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)

      at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)

      at oracle.dbtools.http.cors.CORSPreflightFilter.doFilter(CORSPreflightFilter.java:66)

      at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)

      at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)

      at oracle.dbtools.http.cookies.auth.CookieSessionCSRFFilter.doFilter(CookieSessionCSRFFilter.java:71)

      at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)

      at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)

      at oracle.dbtools.http.auth.AuthenticationFilter.authenticate(AuthenticationFilter.java:100)

      at oracle.dbtools.http.auth.AuthenticationFilter.doFilter(AuthenticationFilter.java:64)

      at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)

      at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)

      at oracle.dbtools.url.mapping.RequestMapperImpl.doFilter(RequestMapperImpl.java:161)

      at oracle.dbtools.url.mapping.URLMappingBase.doFilter(URLMappingBase.java:96)

      at oracle.dbtools.url.mapping.filter.URLMappingFilter.doFilter(URLMappingFilter.java:127)

      at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)

      at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)

      at oracle.dbtools.http.auth.external.ExternalSessionFilter.doFilter(ExternalSessionFilter.java:59)

      at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)

      at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)

      at oracle.dbtools.http.cors.CORSResponseFilter.doFilter(CORSResponseFilter.java:83)

      at oracle.dbtools.http.filters.HttpResponseFilter.doFilter(HttpResponseFilter.java:45)

      at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)

      at oracle.dbtools.http.filters.AbsoluteLocationFilter.doFilter(AbsoluteLocationFilter.java:65)

      at oracle.dbtools.http.filters.HttpResponseFilter.doFilter(HttpResponseFilter.java:45)

      at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)

      at oracle.dbtools.http.errors.ErrorPageFilter.doFilter(ErrorPageFilter.java:85)

      at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)

      at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)

      at oracle.dbtools.http.secure.ForceHttpsFilter.doFilter(ForceHttpsFilter.java:72)

      at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)

      at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)

      at oracle.dbtools.http.auth.ForceAuthFilter.doFilter(ForceAuthFilter.java:44)

      at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)

      at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)

      at oracle.dbtools.http.filters.Filters.filter(Filters.java:65)

      at oracle.dbtools.http.entrypoint.EntryPoint.service(EntryPoint.java:81)

      at oracle.dbtools.http.entrypoint.EntryPointServlet.service(EntryPointServlet.java:102)

      at oracle.dbtools.entrypoint.WebApplicationRequestEntryPoint.service(WebApplicationRequestEntryPoint.java:50)

      at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)

      at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:860)

      at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:535)

      at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:188)

      at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1595)

      at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:188)

      at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1253)

      at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:168)

      at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:473)

      at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1564)

      at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:166)

      at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1155)

      at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)

      at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:219)

      at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:126)

      at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)

      at org.eclipse.jetty.server.Server.handle(Server.java:530)

      at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:347)

      at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:256)

      at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:279)

      at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:102)

      at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:124)

      at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:247)

      at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.produce(EatWhatYouKill.java:140)

      at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:131)

      at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:382)

      at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:708)

      at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:626)

      at java.lang.Thread.run(Thread.java:748)

      Caused by: java.sql.SQLSyntaxErrorException: ORA-00918: column ambiguously defined

       

       

      at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)

      at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)

      at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054)

      at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623)

      at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)

      at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)

      at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:223)

      at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:56)

      at oracle.jdbc.driver.T4CCallableStatement.executeForDescribe(T4CCallableStatement.java:744)

      at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:904)

      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1082)

      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3780)

      at oracle.jdbc.driver.T4CCallableStatement.executeInternal(T4CCallableStatement.java:1300)

      at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3822)

      at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1165)

      at sun.reflect.GeneratedMethodAccessor18.invoke(Unknown Source)

      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

      at java.lang.reflect.Method.invoke(Method.java:498)

      at oracle.ucp.jdbc.proxy.StatementProxyFactory.invoke(StatementProxyFactory.java:367)

      at com.sun.proxy.$Proxy43.executeQuery(Unknown Source)

      at oracle.dbtools.common.stmt.jdbc.BindableQueryImpl.executeQuery(BindableQueryImpl.java:32)

      at oracle.dbtools.rest.resource.generator.json.JSONResourceGeneratorBase.service(JSONResourceGeneratorBase.java:66)

      ... 86 more

      Caused by: Error : 918, Position : 26, Sql = select * from (

      select q_.* , row_number() over (order by 1) RN___ from (

      select a.application_id,b.application_id,b.profile_option_id,b.profile_option_name,b.last_update_date,b.last_updated_by,b.creation_date,

      b.created_by,b.last_update_login,b.write_allowed_flag,b.read_allowed_flag,b.user_changeable_flag,b.user_visible_flag,b.site_enabled_flag,b.site_update_allowed_flag,b.app_enabled_flag,

      b.app_update_allowed_flag,b.resp_enabled_flag,b.resp_update_allowed_flag,b.user_enabled_flag,b.user_update_allowed_flag,b.start_date_active,null,b.end_date_active,

      null,null,null,null,null,c.user_profile_option_name,c.description,c.language,d.profile_option_value

      from fnd_application a, fnd_profile_options b,fnd_profile_options_tl c, fnd_profile_option_values d

      where(a.application_id =d.level_value

      and b.application_id=d.application_id

      and d.profile_option_id=b.profile_option_id

      and b.profile_option_name=c.profile_option_name)

      ) q_

      )

      where RN___ between :1  and :2 , OriginalSql = select * from (

      select q_.* , row_number() over (order by 1) RN___ from (

      select a.application_id,b.application_id,b.profile_option_id,b.profile_option_name,b.last_update_date,b.last_updated_by,b.creation_date,

      b.created_by,b.last_update_login,b.write_allowed_flag,b.read_allowed_flag,b.user_changeable_flag,b.user_visible_flag,b.site_enabled_flag,b.site_update_allowed_flag,b.app_enabled_flag,

      b.app_update_allowed_flag,b.resp_enabled_flag,b.resp_update_allowed_flag,b.user_enabled_flag,b.user_update_allowed_flag,b.start_date_active,null,b.end_date_active,

      null,null,null,null,null,c.user_profile_option_name,c.description,c.language,d.profile_option_value

      from fnd_application a, fnd_profile_options b,fnd_profile_options_tl c, fnd_profile_option_values d

      where(a.application_id =d.level_value

      and b.application_id=d.application_id

      and d.profile_option_id=b.profile_option_id

      and b.profile_option_name=c.profile_option_name)

      ) q_

      )

      where RN___ between ? and ?, Error Msg = ORA-00918: column ambiguously defined

       

       

      at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)

      ... 107 more