This discussion is archived
8 Replies Latest reply: Mar 22, 2013 1:26 PM by 998655 RSS

APEX Listener EA2 Standalone CLOB error

951209 Newbie
Currently Being Moderated
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
is
l_clob clob;
begin
for i in 1..p_cnt loop
l_clob:=l_clob||'a';
end loop;
return l_clob;
end;‚Äč

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

Thanks,
Tim
  • 1. Re: APEX Listener EA2 Standalone CLOB error
    Prabodh Guru
    Currently Being Moderated
    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
    as
    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;
    
    begin
      dbms_lob.createtemporary(l_blob, true);
      for i in 1..p_cnt loop
        l_clob := l_clob||'*';
      end loop;
    
      DBMS_LOB.CONVERTTOBLOB(
       l_blob,
       l_clob,
       l_size,
       l_dest_offset,
       l_src_offset , 
       l_blob_csid,
       l_lang_context,
       l_warning
      );
       l_ret := l_blob;
       dbms_lob.freetemporary(l_blob);
      return l_ret;
    end;
    Cheers,

    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
    951209 Newbie
    Currently Being Moderated
    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.

    Thanks!

    _Tim                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
  • 3. Re: APEX Listener EA2 Standalone CLOB error
    Prabodh Guru
    Currently Being Moderated
    Hmmm.

    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.

    Cheers,
  • 4. Re: APEX Listener EA2 Standalone CLOB error
    Colm Divilly Journeyer
    Currently Being Moderated
    Thanks for your detailed report, I'll look into it.

    Colm
  • 5. Re: APEX Listener EA2 Standalone CLOB error
    JanN Newbie
    Currently Being Moderated
    I'm experiencing the same issue (4000 limit) with both CLOB and BLOB approach on APEX 4.2.1 with Listener 2.0.0.354 - 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"

    Jan
  • 6. Re: APEX Listener EA2 Standalone CLOB error
    Colm Divilly Journeyer
    Currently Being Moderated
    Jan,
    the Media Resource issue is fixed in 2.0.1 and it should also address the HTP.PRN > 32Kb issue

    http://www.oracle.com/technetwork/developer-tools/apex-listener/downloads/index.html
  • 7. Re: APEX Listener 2.0.1 Standalone CLOB error
    JanN Newbie
    Currently Being Moderated
    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...

    Jan

    -----

    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
    998655 Newbie
    Currently Being Moderated
    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

Legend

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