CLOB datatype exceeding the limit-CodePudding

I have below procedure to create input for my SOAP web service call. But it throws error.

Cause: The provided JavaScript Object Notation (JSON) operator generated a result which exceeds the maximum length specified in the RETURN clause.

Action: Increase the maximum size of the data type in the RETURNING clause or use a CLOB or BLOB in the RETURNING clause.

create or replace procedure soap is
reqtxt VARCHAR2(30000 CHAR);  
requrl VARCHAR2(1000 CHAR);
doc clob;
doc1 varchar2(1000 char);
--doc2 varchar2(9000 char);
doc2 clob;
doc3 varchar2(1000 char);
BEGIN
  requrl := 'http://searchv1-dev.tcc.etn.com:8080/EatonSearchApp/EatonSearchWS';
 doc1 := '<?xml version="1.0" encoding="utf-8"?>
  <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ws="http://ws.generic.eaton.com/">
  <soapenv:Header/>
  <soapenv:Body>
   <ws:doProcess>
     <arg0>';
 doc3 := '</arg0>
   </ws:doProcess>
  </soapenv:Body>
  </soapenv:Envelope>';
  
 SELECT json_object('searchApplication'    VALUE 'etndisinv', 
'searchApplicationKey' VALUE 'abc123',
 'function' VALUE 'search',
 'searchTerms' VALUE 'ignore',
 'language' VALUE 'en_US',
 'startingRecordNumber' VALUE '0',
 'numberOfRecordsToReturn' VALUE '6',
          'filters' VALUE json_arrayagg(json_object('filterName'  VALUE part_id_qualifier,
      'filterValue' VALUE json_arrayagg(part_id
           )))) into doc2
 FROM dist_inv_trans
 GROUP BY part_id_qualifier;
 dbms_output.put_line('doc2 is ' || doc2); 
 reqtxt := doc1 || doc2 || doc3;
 dbms_output.put_line('reqtxt is' || reqtxt);
END;

Error:
Error report -
ORA-40478: output value too large (maximum: 4000)
ORA-06512: at "DISTINV.SOAP", line 24
ORA-06512: at line 1
40478. 00000 - "output value too large (maximum: %s)"
*Cause:  The provided JavaScript Object Notation (JSON) operator generated a
      result which exceeds the maximum length specified in the RETURN
      clause.
*Action:  Increase the maximum size of the data type in the RETURNING clause
      or use a CLOB or BLOB in the RETURNING clause.

CodePudding user response：

In Oracle 11g, you can't create a VARCHAR2 that is 30,000 characters

So, redefine VARCHAR2(30000 CHAR);

--> VARCHAR2(4000 CHAR); or

--> CLOB

CodePudding user response：

You can achieve this, by something like this:

 select  json_object(
       'searchApplication' value 'etndisinv'
      , 'searchApplicationKey' value 'abc123'
      , 'function' value 'search'
      , 'searchTerms' value 'ignore'
      , 'language' value 'en_US'
      , 'startingRecordNumber' value '0'
      , 'numberOfRecordsToReturn' value '6'
      , 'filters' value
        json_arrayagg(
         json_object(
          'filterName' value part_id_qualifier
         , 'filterValue' value json_arrayagg(part_id returning clob)
          returning clob
         )
         returning clob
        )
       returning clob  
      )
 into   doc2
 from   dist_inv_trans
 group by part_id_qualifier