8 Replies Latest reply on Mar 22, 2013 8:26 PM by 998655

    APEX Listener EA2 Standalone CLOB error

      I'm testing the APEX Listener EA2 release in Standalone mode on CentOS against Oracle XE.

      My RESTful service calls use the Media Resource type to return a CLOB that I format myself inside a function:

      select 'application/json', my_function_that_returns_json_in_a_clob from dual

      In the EA2 release, this is generating an error if the function returns more than 4000 bytes (works with 4000, fails with 4001). It is as though the function is getting cast as a varchar2 somewhere. I verified that the function will correctly return a large result to other sources (dbms_output, for instance).

      The APEX Listener Log reports the error as:

      oracle.dbtools.rt.web.HttpEndpointBase restfulServices
      SEVERE: ORA-22922: nonexistent LOB value

      Here's a Test Case:

      create or replace function test_size(p_cnt in integer) return clob
      l_clob clob;
      for i in 1..p_cnt loop
      end loop;
      return l_clob;

      Then create a RESTful service call:
      Method: "GET"
      Source Type: "Media Resource"
      Source: select 'application/json', test_size(4001) from dual

        • 1. Re: APEX Listener EA2 Standalone CLOB error
          Hi Tim,

          I do not use 2.0 EA but since your query looked interesting I tried out your scenario on 1.1.4 on GlassFish and encountered the same error that you have reported.
          It appears that you hit a ceiling of 4000 for VARCHAR2 as well as CLOB with Media Resource.
          The good news is that "So what is the battle is lost, all is not lost..".

          Here is a work around that , at least in my environment , seems to break through the barrier. I tried it till a value of 200000 successfully. Stopped at this point as it started taking too long to run it !

          I changed the function to return BLOB as follows
          create or replace function test_size(p_cnt in integer) return BLOB
          l_blob BLOB ;
          l_ret  BLOB ;
          l_clob CLOB;
          l_warning  integer;
          l_size integer := DBMS_LOB.LOBMAXSIZE;
          l_dest_offset  integer := 1;
          l_src_offset   integer := 1;
          l_blob_csid    number := dbms_lob.default_csid;
          l_lang_context integer := dbms_lob.default_lang_ctx;
            dbms_lob.createtemporary(l_blob, true);
            for i in 1..p_cnt loop
              l_clob := l_clob||'*';
            end loop;
             l_src_offset , 
             l_ret := l_blob;
            return l_ret;

          PS: BTW, if you intend to put this into Production then it just might be worth considering the QUERY type. It may mean creating a few queries to be able to construct the required SELECT statement but most probably be worth the extra effort for the following reasons:
          a. No risk of ending up with a malformed JSON
          b. SELECT will consume less resources than an equivalent PL/SQL block , presumably with Explicit CURSORs
          c. Will be faster as well.

          Edited by: Prabodh on Oct 12, 2012 5:23 PM
          • 2. Re: APEX Listener EA2 Standalone CLOB error
            Thanks for your thorough reply!

            Unfortunately, your workaround doesn't fix the problem with EA2. It encounters the 4000 character limit as well.

            I understand your advice regarding using the QUERY type. I chose the Media Resource strategy for two main reasons:

            1. These services calls are for our mobile developers. Because they use some predefined frameworks for managing data that comes to the mobile app, they wanted the json to distinguish between data sets (rather than every data set being called "items"). I am able to format the json in such a way that it doesn't confuse their code. Is there another way to customize the format of the default json created by the QUERY type?

            2. I need to conduct some other business as part of the call. For instance, the service passes in a user's credentials which I use to authenticate and then retrieve the appropriate results based on who they are. Currently, I have stored procedures that authenticate APEX users or LDAP users.

            Perhaps old habits die hard, but I find that if I just do all of this myself in a stored function, I have all of the control I need. If you have other recommended strategies, I'm all ears.


            • 3. Re: APEX Listener EA2 Standalone CLOB error

              That it does not work with 2.0 EA is really odd. Or, maybe it has to do with the fact that I use GF as the J2EE Container.

              Will try with 2.0 EA Standalone in a day or two and check out what happens.

              • 4. Re: APEX Listener EA2 Standalone CLOB error
                Colm Divilly-Oracle
                Thanks for your detailed report, I'll look into it.

                • 5. Re: APEX Listener EA2 Standalone CLOB error
                  Jan N
                  I'm experiencing the same issue (4000 limit) with both CLOB and BLOB approach on APEX 4.2.1 with Listener - tested on Tomcat 7 and standalone.

                  However I found this workaround to raise the limit to 32k:
                  1) Switch Handler's Source type = "Media Resource" to "PL/SQL"
                  2) Replace SELECT with PL/SQL code to send CLOB using the sys.htp.p procedure

                  However once the CLOB is over 32k you will get the same "java.sql.SQLException: ORA-22922: nonexistent LOB value"

                  • 6. Re: APEX Listener EA2 Standalone CLOB error
                    Colm Divilly-Oracle
                    the Media Resource issue is fixed in 2.0.1 and it should also address the HTP.PRN > 32Kb issue

                    • 7. Re: APEX Listener 2.0.1 Standalone CLOB error
                      Jan N
                      Hi Colm,

                      I'm happy to report the Media Resource works fine for both CLOBs and BLOBs - thanks!

                      However I have no luck with the HTP.PRN > 32Kb. At least the error is different...



                      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)
                      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)
                      • 8. Re: APEX Listener 2.0.1 Standalone CLOB error
                        Confirmed here, too. With APEX Listener 2.0.1, HTP.PRN > 32Kb produces:

                        [#|2013-03-22T15:23:43.046-0400|SEVERE|glassfish3.1.2|null|_ThreadID=77;_ThreadName=Thread-2;|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