This discussion is archived
10 Replies Latest reply: Jun 17, 2013 2:49 PM by JanN RSS

Downloading binary files with RESTful web service = HTTP 500 ?

JanN Newbie
Currently Being Moderated
I would like to enable access to photos (BLOBs) stored in database via an APEX RESTful service.

I have existing PL/SQL API based on wpg_docload.download_file working fine when called directly over URL (e.g. granted to APEX_PUBLIC_USER). I would like to re-use this API as it does lot of other things including security and cache control.

The code looks like:
===
procedure photo(id in number, v in varchar2) is
l_blob blob;
-- ...
begin
-- security checks
-- fetch data into l_blob ...
-- check if document has been modified (304)
-- ...
owa_util.mime_header(l_mime_type, false);
htp.p('Content-length: ' || dbms_lob.getlength(l_blob));
htp.p('ETag: ' || l_etag);
htp.p('Cache-Control: public');
owa_util.http_header_close;
wpg_docload.download_file(l_blob);
end;
===

However when I call this API with APEX 4.2.1 Restful web service (GET resource handler - source type PL/SQL) I'm getting "500 - Internal server error" back from the APEX Listener (2.0.0.354.17.08 - both running standalone or with Tomcat 7) - console log attached.

Similar approach - APEX RESTful web service calling PL/SQL API - works fine for STRING/CLOB content. So it must be something to do with the wpg_docload.download_file

Any idea how to workaround this bug/feature or suggestion for an alternative approach will be appreciated.

Jan

-----

Feb 28, 2013 9:39:03 PM oracle.dbtools.rt.web.WebErrorResponse internalError
SEVERE: ORA-22922: nonexistent LOB value

java.io.IOException: ORA-22922: nonexistent LOB value
at oracle.jdbc.driver.OracleBlobInputStream.needBytes(OracleBlobInputStream.java:204)
at oracle.jdbc.driver.OracleBufferedStream.readInternal(OracleBufferedStream.java:169)
at oracle.jdbc.driver.OracleBufferedStream.read(OracleBufferedStream.java:143)
at java.io.FilterInputStream.read(Unknown Source)
at java.nio.channels.Channels$ReadableByteChannelImpl.read(Unknown Source)
at oracle.dbtools.common.util.StreamCopy.drain(StreamCopy.java:75)
at oracle.dbtools.common.util.StreamCopy.drain(StreamCopy.java:52)
at oracle.dbtools.rt.web.ServletResponses.sendResponse(ServletResponses.java:85)
at oracle.dbtools.rt.web.ServletResponses.sendResponse(ServletResponses.java:130)
at oracle.dbtools.rt.web.HttpEndpointBase.restfulServices(HttpEndpointBase.java:343)
at oracle.dbtools.rt.web.HttpEndpointBase.service(HttpEndpointBase.java:125)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at com.sun.grizzly.http.servlet.ServletAdapter$FilterChainImpl.doFilter(ServletAdapter.java:1059)
at com.sun.grizzly.http.servlet.ServletAdapter$FilterChainImpl.invokeFilterChain(ServletAdapter.java:999)
at com.sun.grizzly.http.servlet.ServletAdapter.doService(ServletAdapter.java:434)
at oracle.dbtools.standalone.SecureServletAdapter.doService(SecureServletAdapter.java:65)
at com.sun.grizzly.http.servlet.ServletAdapter.service(ServletAdapter.java:379)
at com.sun.grizzly.tcp.http11.GrizzlyAdapter.service(GrizzlyAdapter.java:179)
at com.sun.grizzly.tcp.http11.GrizzlyAdapterChain.service(GrizzlyAdapterChain.java:196)
at com.sun.grizzly.tcp.http11.GrizzlyAdapter.service(GrizzlyAdapter.java:179)
at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:849)
at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:746)
at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1045)
at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:228)
at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137)
at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104)
at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90)
at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79)
at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54)
at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59)
at com.sun.grizzly.ContextTask.run(ContextTask.java:71)
at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532)
at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: ORA-22922: nonexistent LOB value

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:389)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:382)
at oracle.jdbc.driver.T4C8TTILob.processError(T4C8TTILob.java:792)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:505)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:223)
at oracle.jdbc.driver.T4C8TTILob.read(T4C8TTILob.java:146)
at oracle.jdbc.driver.T4CConnection.getBytes(T4CConnection.java:2435)
at oracle.sql.BLOB.getBytes(BLOB.java:348)
at oracle.jdbc.driver.OracleBlobInputStream.needBytes(OracleBlobInputStream.java:181)
... 33 more
  • 1. Re: Downloading binary files with RESTful web service = HTTP 500 ?
    Udo Guru
    Currently Being Moderated
    Hi,

    there have been reports on similar issues, e. g. {thread:id=2449844} and {thread:id=2486606}, but as far as I know, these have been addressed by the current production release you are using (BTW: Is there a small typo in tge version number you've posted?). Any chance you could try to reproduce it on GlassFish or even on apex.oracle.com?

    -Udo
  • 2. Re: Downloading binary files with RESTful web service = HTTP 500 ?
    JanN Newbie
    Currently Being Moderated
    Hi Udo,

    thanks for your suggestion.

    Those issues are related to handlers set with Source Type = "Media Resource" - the 4000 bytes limit doesn't seem to be resolved in the production release of the Listener yet.

    However I'm trying to use Source Type = "PL/SQL" - it works up to 32k when using sys.htp.p but it doesn't work at all for wpg_docload.download_file regardless the length (e.g. even for 200 bytes).

    Jan
  • 3. Re: Downloading binary files with RESTful web service = HTTP 500 ?
    JanN Newbie
    Currently Being Moderated
    Test cases:

    1) RESTful web service with Source Type = "PL/SQL" using sys.htp.p to generate output

    http://apex.oracle.com/pls/apex/campusit/resttest/plsql-htp/4000 = 4000 bytes - WORKS
    http://apex.oracle.com/pls/apex/campusit/resttest/plsql-htp/32000 = 32k - WORKS
    http://apex.oracle.com/pls/apex/campusit/resttest/plsql-htp/33000 = 33k - FAILS "500 - Internal Server Error"

    2) RESTful web service with Source Type = "PL/SQL" using wpg_docload.download_file to generate output

    http://apex.oracle.com/pls/apex/campusit/resttest/plsql-wpg/4000 = 4000 bytes - FAILS

    Jan
  • 4. Re: Downloading binary files with RESTful web service = HTTP 500 ?
    Colm Divilly Journeyer
    Currently Being Moderated
    Hi Jan,
    can you try 2.0.1: http://www.oracle.com/technetwork/developer-tools/apex-listener/downloads/index.html, the fix that was implemented for the similar Media Resources problem should address this case as well, let me know if it works

    Thanks,
    Colm
  • 5. Re: Downloading binary files with RESTful web service = HTTP 500 ?
    JanN Newbie
    Currently Being Moderated
    Hi Colm,

    I'm afraid 2.0.1 made no difference to those issues.

    Jan

    1) RESTful web service with Source Type = "PL/SQL" using sys.htp.p to generate output > 32Kb fails with:
    -----
    Mar 06, 2013 8:51:44 PM oracle.dbtools.rt.web.WebErrorResponse internalError
    SEVERE: oracle.jdbc.driver.OracleClobReader cannot be cast to java.io.InputStream
    java.lang.ClassCastException: oracle.jdbc.driver.OracleClobReader cannot be cast to java.io.InputStream
    at oracle.dbtools.rt.plsql.OracleWebAccess.response(OracleWebAccess.java:112)
    at oracle.dbtools.rt.plsql.AnonymousBlockGenerator.generate(AnonymousBlockGenerator.java:186)
    at oracle.dbtools.rt.resource.templates.v2.ResourceTemplatesDispatcher$HttpResourceGenerator.response(ResourceTemplatesDispatcher.java:309)
    at oracle.dbtools.rt.web.RequestDispatchers.dispatch(RequestDispatchers.java:88)
    at oracle.dbtools.rt.web.HttpEndpointBase.restfulServices(HttpEndpointBase.java:412)
    at oracle.dbtools.rt.web.HttpEndpointBase.service(HttpEndpointBase.java:162)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
    ...


    2) RESTful web service with Source Type = "PL/SQL" using wpg_docload.download_file to generate output fails regardles the size:
    -----
    Mar 06, 2013 9:13:43 PM oracle.dbtools.rt.web.WebErrorResponse internalError
    SEVERE: ORA-22922: nonexistent LOB value

    java.io.IOException: ORA-22922: nonexistent LOB value

         at oracle.jdbc.driver.OracleBlobInputStream.needBytes(OracleBlobInputStream.java:204)
         at oracle.jdbc.driver.OracleBufferedStream.readInternal(OracleBufferedStream.java:169)
         at oracle.jdbc.driver.OracleBufferedStream.read(OracleBufferedStream.java:143)
         at java.io.FilterInputStream.read(FilterInputStream.java:133)
         at java.nio.channels.Channels$ReadableByteChannelImpl.read(Channels.java:385)
         at oracle.dbtools.common.util.StreamCopy.drain(StreamCopy.java:84)
         at oracle.dbtools.common.util.StreamCopy.drain(StreamCopy.java:39)
         at oracle.dbtools.rt.web.ServletResponses.sendResponse(ServletResponses.java:85)
         at oracle.dbtools.rt.web.ServletResponses.sendResponse(ServletResponses.java:130)
         at oracle.dbtools.rt.web.HttpEndpointBase.restfulServices(HttpEndpointBase.java:419)
         at oracle.dbtools.rt.web.HttpEndpointBase.service(HttpEndpointBase.java:162)
         at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
         at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
         at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
         at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
         at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
         at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
         at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
         at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
         at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:936)
         at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
         at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
         at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1004)
         at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
         at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
         at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
         at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
         at java.lang.Thread.run(Thread.java:722)
    Caused by: java.sql.SQLException: ORA-22922: nonexistent LOB value

         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:389)
         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:382)
         at oracle.jdbc.driver.T4C8TTILob.processError(T4C8TTILob.java:792)
         at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:505)
         at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:223)
         at oracle.jdbc.driver.T4C8TTILob.read(T4C8TTILob.java:146)
         at oracle.jdbc.driver.T4CConnection.getBytes(T4CConnection.java:2435)
         at oracle.sql.BLOB.getBytes(BLOB.java:348)
         at oracle.jdbc.driver.OracleBlobInputStream.needBytes(OracleBlobInputStream.java:181)
         ... 27 more
  • 6. Re: Downloading binary files with RESTful web service = HTTP 500 ?
    Colm Divilly Journeyer
    Currently Being Moderated
    Thanks for the feedback Jan, I'll look into it further
  • 7. Re: Downloading binary files with RESTful web service = HTTP 500 ?
    patfmnd Newbie
    Currently Being Moderated
    Hi,
    Any update with this. I am getting same error trying to access binary file from RESTful Service Module using source type 'Media Resource'.

    Pat
  • 8. Re: Downloading binary files with RESTful web service = HTTP 500 ?
    jb000308 Newbie
    Currently Being Moderated
    Hi Same issue in trying to upload the FRM test file in the developer day VM with the most recent version of the APEX listener
  • 9. Re: Downloading binary files with RESTful web service = HTTP 500 ?
    JanN Newbie
    Currently Being Moderated
    Hi Pat,

    issue with "Media resource" was fixed in APEX Listener 2.0.1

    See Re: APEX Listener 2.0.1 Standalone CLOB error

    Jan
  • 10. Re: Downloading binary files with RESTful web service = HTTP 500 ?
    JanN Newbie
    Currently Being Moderated

    Hi Colm,

     

    I'm happy to report the original issue was now fixed in APEX Listener 2.0.2

     

    However I'm having similar issue on the Oracle Database Cloud. APEX admin console reports Oracle DB Cloud is using APEX Listener 3.0.0 (?).

    Can you please confirm if this version includes fixes introduced in 2.0.2 ?

     

    Thanks,

     

    Jan

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points