我有以下程序來為我的 SOAP Web 服務呼叫創建輸入。但它拋出錯誤。
原因： 提供的 JavaScript 物件表示法 (JSON) 運算子生成的結果超出了 RETURN 子句中指定的最大長度。
行動： 增加回傳子句中資料型別的最大大小或在回傳子句中使用CLOB或BLOB。
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.
uj5u.com熱心網友回復：
在 Oracle 11g 中，您無法創建 30,000 個字符的 VARCHAR2
所以，重新定義 VARCHAR2(30000 CHAR);
-->VARCHAR2(4000 CHAR);
或
--> CLOB
uj5u.com熱心網友回復：
您可以通過以下方式實作這一點：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/338676.html