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:
<Message>Error occurred in XML processing</Message>
<Message>PL/SQL: numeric or value error</Message>
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.
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.
create or replace procedure p1(
p_result out xmltype
passing xmlelement("test", rpad(to_clob('X'),8000,'X'))
When accessed via NDWS, the returned response is then like this :
<P_RESULT>XXXXXXXXXXXXXXXXXXXXXX ... XXXXXXXXXX</P_RESULT>
with P_RESULT length = 8000.