getting error"java.sql.SQLSyntaxErrorException: ORA-00918: column ambiguously defined" InternalServerException [statusCode=500
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
