This discussion is archived
2 Replies Latest reply: Jul 25, 2013 7:59 PM by MannyJay RSS

Native Web Services 4000 character limit for JSON Data

MannyJay Newbie
Currently Being Moderated

Dear Experts,

I have a requirement to have the JSON data as out type or PlSql Procedure which has varchar2 type.

When my JSON converted data is being sent by this variable, still after reaching 4000 characters, I could see the output in Toad console.

But when I try from WSDL URL, it fails with below error:

 

<OracleError>

<ErrorNumber>ORA-19202</ErrorNumber>

<Message>Error occurred in XML processing</Message>

</OracleError>

<OracleError>

<ErrorNumber>ORA-06502</ErrorNumber>

<Message>PL/SQL: numeric or value error</Message>

</OracleError>

 

Hence I thought I could use XMLTYPE, but it's supported only for XML formatted file. As per my requirement I have to send the JSON data thru' plsql which is more than 4000+. Please help on this.

 

Thanks,

Mani

  • 1. Re: Native Web Services 4000 character limit for JSON Data
    odie_63 Guru
    Currently Being Moderated

    Seems like CLOB is not supported either since the out parameter is actually bound to VARCHAR2(4000) at runtime.

     

    The only workaround I can think of is to use an XMLType output containing only a text() node. It's still a valid XMLType but there's no tag, so it might suit your need.

    e.g.

    create or replace procedure p1(

      p_result out xmltype

    )

    is

    begin

     

      select xmlquery('//text()'

               passing xmlelement("test", rpad(to_clob('X'),8000,'X'))

               returning content

             )

      into p_result

      from dual;

     

    end;

    /

     

    When accessed via NDWS, the returned response is then like this :

    <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">

       <soap:Body>

          <P1Output xmlns="http://xmlns.oracle.com/orawsv/DEV/P1">

             <P_RESULT>XXXXXXXXXXXXXXXXXXXXXX ... XXXXXXXXXX</P_RESULT>

          </P1Output>

       </soap:Body>

    </soap:Envelope>

    with P_RESULT length = 8000.

  • 2. Re: Native Web Services 4000 character limit for JSON Data
    MannyJay Newbie
    Currently Being Moderated

    Hi,

    This is really good. I got this kind of idea: Is it possible to send the whole JSON data as one XML Tag value in this approach! That way, I can ask the destination system to parse accordingly!

     

    Thanks for your help.

Legend

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