Skip to Main Content

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Getting HTTP 500 error when trying to access my GET handler

Mark Sta AnaMay 22 2017 — edited May 23 2017

I've been following Jeff Smith's youtube video on how to create ORDS modules using SQL Developer: https://youtu.be/ZAUJmiW1w2Y?t=9m59s

I've managed to create the "video" module, create the URL pattern "message", and the GET handler.

Capture.PNG

When I try to access my URL http://localhost:8666/ords/MY_SCHEMA/videos/message

I get a 500 error in the browser, and this rather cryptic dump in the ORDS - Log

SEVERE: java.sql.SQLSyntaxErrorException: ORA-00904: "DBMS_LOB"."SUBSTR": invalid identifier

InternalServerException [statusCode=500, reasons=[]]

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

at oracle.dbtools.rest.resource.jdbc.JDBCModuleMetadataLoader.candidates(JDBCModuleMetadataLoader.java:161)

at oracle.dbtools.rest.resource.modules.ModulesChooser.canDispatch(ModulesChooser.java:38)

at oracle.dbtools.rest.resource.jdbc.JDBCResourceDispatcher.canDispatch(JDBCResourceDispatcher.java:62)

at oracle.dbtools.rest.resource.jdbc.JDBCResourceDispatcher.canDispatch(JDBCResourceDispatcher.java:37)

at oracle.dbtools.rest.resource.cache.ResourceCache.load(ResourceCache.java:119)

at oracle.dbtools.rest.resource.cache.ResourceCache.canDispatch(ResourceCache.java:89)

at oracle.dbtools.rest.resource.cache.CachedResourceDispatcher.canDispatch(CachedResourceDispatcher.java:52)

at oracle.dbtools.rest.resource.cache.CachedResourceDispatcher.canDispatch(CachedResourceDispatcher.java:25)

at oracle.dbtools.dispatch.DispatchableChooser.choose(DispatchableChooser.java:36)

at oracle.dbtools.http.entrypoint.Dispatcher.choose(Dispatcher.java:69)

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

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

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:73)

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:87)

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

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:125)

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

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

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.errors.ErrorPageFilter.doFilter(ErrorPageFilter.java:94)

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:47)

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

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

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

at oracle.dbtools.rt.web.HttpEndpointBase.dispatchableServices(HttpEndpointBase.java:116)

at oracle.dbtools.rt.web.HttpEndpointBase.service(HttpEndpointBase.java:81)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:534)

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

at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:536)

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

Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "DBMS_LOB"."SUBSTR": invalid identifier

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)

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

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

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

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

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

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

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

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

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

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

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

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

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

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

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

at oracle.ucp.jdbc.proxy.PreparedStatementProxyFactory.invoke(PreparedStatementProxyFactory.java:160)

at oracle.ucp.jdbc.proxy.CallableStatementProxyFactory.invoke(CallableStatementProxyFactory.java:136)

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

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

at oracle.dbtools.rest.resource.jdbc.JDBCModuleMetadataLoader.candidates(JDBCModuleMetadataLoader.java:91)

... 67 more

Oracle Db (select * from v$version)

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

PL/SQL Release 12.1.0.2.0 - Production

"CORE 12.1.0.2.0 Production"

TNS for 64-bit Windows: Version 12.1.0.2.0 - Production

NLSRTL Version 12.1.0.2.0 - Production

Oracle SQL Developer: 4.2.0.16.356

This post has been answered by Mark Sta Ana on May 23 2017
Jump to Answer

Comments

thatJeffSmith-Oracle May 22 2017 — edited on May 22 2017

What's your service do, what's the code behind your GET?

Are you doing anything with a LOB?

Mark Sta Ana May 23 2017 — edited on May 23 2017

Hi Jeff!

Here the code behind the handler: select 'hello world' from dual   

____

clear screen

set linesize 150

col name format a10

col uri_prefix format a10

col status format a10

col origins_allowed format a15

SELECT

    name, uri_prefix, status, created_on, updated_on, origins_allowed

FROM

    user_ords_modules;

col source_type format a15

col items_per_page format a5

col source format a20

SELECT

    source_type, method, items_per_page, source, created_on, updated_on

FROM

    user_ords_handlers;

   

NAME       URI_PREFIX STATUS     CREATED_O UPDATED_O ORIGINS_ALLOWED

---------- ---------- ---------- --------- --------- ---------------

videos     /videos/   PUBLISHED  23-MAY-17 23-MAY-17               

SOURCE_TYPE     METHOD     ITEMS SOURCE               CREATED_O UPDATED_O

--------------- ---------- ----- -------------------- --------- ---------

json/collection GET           25 select 'hello world' 23-MAY-17 23-MAY-17

                                  from dual   

Mark Sta Ana May 23 2017
Answer

Fixed it and always MetaLink to the rescue, found the answer here: http://localhost:8666/ords/swftrefadmin/videos/message

-- as sys

grant execute on DBMS_LOB to _YOUR_ORDS_ENABLED_SCHEMA_;

After that, it just started to work!

Marked as Answer by Mark Sta Ana · Sep 27 2020
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 20 2017
Added on May 22 2017
3 comments
621 views