10 Replies Latest reply: Jun 17, 2013 4:49 PM by Jan N RSS

    Downloading binary files with RESTful web service = HTTP 500 ?

    Jan N
      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
          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 ?
            Jan N
            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 ?
              Jan N
              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-Oracle
                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 ?
                  Jan N
                  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-Oracle
                    Thanks for the feedback Jan, I'll look into it further
                    • 7. Re: Downloading binary files with RESTful web service = HTTP 500 ?
                      patfmnd
                      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
                        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 ?
                          Jan N
                          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 ?
                            Jan N

                            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