從選擇插入時，觸發器內的 UTL_HTTP 呼叫導致 ORA-06502

為了允許開發人員使用 UTL_HTTP 從 Oracle 11gR1 (11.1.0.7 PSU 24) 實例進行 SOAP 呼叫。

他創建了一個儲存過程：

CREATE OR REPLACE PROCEDURE CALL_WS (URL IN VARCHAR2, REQUEST IN VARCHAR2, 
                                    RESPONSE OUT VARCHAR2, ERRLOG OUT VARCHAR2) IS

  L_HTTP_REQ      UTL_HTTP.REQ;
  L_HTTP_RESP     UTL_HTTP.RESP;

  L_HTTP_URL_V    VARCHAR2 (32767);
  IP_INPUTDATA_V  VARCHAR2 (32767);
  L_OUTPUTDATA_V  VARCHAR2 (32767);

BEGIN

  L_HTTP_URL_V := URL;
  IP_INPUTDATA_V := REQUEST;
  ERRLOG := NULL;

  -- Configuration
  UTL_HTTP.SET_DETAILED_EXCP_SUPPORT (TRUE);
  L_HTTP_REQ := UTL_HTTP.BEGIN_REQUEST (L_HTTP_URL_V, 'POST', 'HTTP/1.1');

  -- Authentification (not necessary at the moment)
  --UTL_HTTP.SET_AUTHENTICATION (L_HTTP_REQ, L_HTTP_USERNAME_V, L_HTTP_PASSWORD_V);
  UTL_HTTP.SET_PERSISTENT_CONN_SUPPORT (L_HTTP_REQ, FALSE);

  -- Headers
  UTL_HTTP.SET_HEADER (L_HTTP_REQ, 'Content-Type', 'text/xml');
  UTL_HTTP.SET_HEADER (L_HTTP_REQ, 'charset', 'UTF-8');
  UTL_HTTP.SET_HEADER (L_HTTP_REQ, 'Content-Length', LENGTH (IP_INPUTDATA_V));

  --WRITES SOME TEXT DATA IN THE HTTP REQUEST BODY
  UTL_HTTP.WRITE_TEXT (L_HTTP_REQ, IP_INPUTDATA_V);

  -- GET HTTP RESPONSE
  L_HTTP_RESP := UTL_HTTP.GET_RESPONSE (L_HTTP_REQ);

  -- GET THE RESPONSE TEXT VALUE
  UTL_HTTP.READ_TEXT (L_HTTP_RESP, L_OUTPUTDATA_V);

  -- Check HTTP status code
  IF (L_HTTP_RESP.STATUS_CODE <> 200) THEN               
      ERRLOG := 'HTTP REQUEST CALL FAILED. STATUS CODE IS ' || L_HTTP_RESP.STATUS_CODE;
  END IF;

  UTL_HTTP.END_RESPONSE (L_HTTP_RESP);
  -- Set Output parameter

  RESPONSE := L_OUTPUTDATA_V;

EXCEPTION
  WHEN OTHERS THEN
      RESPONSE := NULL;
      ERRLOG := SQLERRM;
END;

和一個 BEFORE INSERT 觸發器如下：

CREATE OR REPLACE TRIGGER TR_kkk_AFTERIN
   BEFORE INSERT OR UPDATE ON CHU_kkk
   REFERENCING NEW AS N OLD AS O
FOR EACH ROW

DECLARE
  -- Variable declarations
  l_URL              VARCHAR2 (32767);
  l_REQUEST          VARCHAR2 (32767);
  l_RESPONSE         VARCHAR2 (32767);
  l_ERRLOG           VARCHAR2 (32767);

  v_def  definitions.compterendu%TYPE;
BEGIN

  IF :n.traite = 0 THEN
      -- Variable initializations
      l_URL := 'http://zz/uu/services.asmx';
      v_def := NULL;

      -- Get request SOAP model in a Omnipro definition
      SELECT VALUE INTO v_def
      FROM definitions
      WHERE cle LIKE 'kkk';

      IF v_def IS NOT NULL THEN
          -- Replace values in the request SOAP
          l_REQUEST := REPLACE(v_def, '@@DPTID@@', :n.dptid);
          l_REQUEST := REPLACE(l_REQUEST, '@@USERID@@', :n.userid);
          l_REQUEST := REPLACE(l_REQUEST, '@@APPTID@@', :n.apptid);
          l_REQUEST := REPLACE(l_REQUEST, '@@REFID@@', :n.refid);
          l_REQUEST := REPLACE(l_REQUEST, '@@REFAPP@@', :n.refapp);
          l_REQUEST := REPLACE(l_REQUEST, '@@DTEDEB@@', TO_CHAR(:n.dtedeb, 'yyyy-mm-dd'));
          l_REQUEST := REPLACE(l_REQUEST, '@@DTEFIN@@', TO_CHAR(:n.dtefin, 'yyyy-mm-dd'));
          l_REQUEST := REPLACE(l_REQUEST, '@@NUMNAT@@', :n.numnat);
          l_REQUEST := REPLACE(l_REQUEST, '@@NUMREF@@', :n.numref);

          -- Call
          CALL_WEB_SERVICE (URL       => l_URL,
                          REQUEST   => l_REQUEST,
                          RESPONSE  => l_RESPONSE,
                          ERRLOG    => l_ERRLOG);
          IF l_ERRLOG IS NOT NULL THEN
              :n.traite := 9;
              :n.reflog := l_ERRLOG || ' : ' || SUBSTR (l_RESPONSE, 0, 1900);
          ELSE
              :n.traite := 1;
          END IF;
          :n.dtetrt := SYSDATE;
      END IF;
  END IF;

EXCEPTION
  WHEN OTHERS THEN
      -- Consider logging the error and then re-raise
      :n.traite := 9;
      :n.reflog := SQLERRM;
      :n.dtetrt := SYSDATE;
      --RAISE;
END;

一切都適用於具有單個值的 INSERT 查詢，但每當他呼叫時都會失敗：

INSERT INTO ... VALUES ...

COMMIT;

INSERT INTO ... VALUES ...

COMMIT;

他得到一個 ORA-06502。

如果他分別呼叫每個插入，則沒有錯誤…

我建議他使用 SQLDeveloper 調試器進行調查，但我們不確定它會導致什麼問題……

是什麼原因造成的？

您應該使用正確的數據類型，即CLOB. 對於 SOAP 請求，我會推薦XMLTYPE。

我有一個類似的案例，我使用此程式碼生成請求：

CREATE OR REPLACE TYPE VARCHAR_TABLE_TYPE AS TABLE OF VARCHAR2(1000);

FUNCTION GenerateRequest(nodes IN VARCHAR_TABLE_TYPE) RETURN XMLTYPE IS
   res XMLTYPE;
BEGIN
   
   SELECT  
       XMLELEMENT("soapenv:Envelope", 
           XMLATTRIBUTES(
               'http://schemas.xmlsoap.org/soap/envelope/' AS "xmlns:soapenv", 
               'http://xxx.yyy.zzz/' AS "xmlns:ser"),
           XMLELEMENT("soapenv:Header"),
           XMLELEMENT("soapenv:Body", 
               XMLELEMENT("ser:getAffectedServices",
                   XMLELEMENT("arg0",
                       XMLELEMENT("appName", 'com'),
                       XMLELEMENT("asmTicket", 'dummy'),
                       XMLELEMENT("principal", 'dummy')
                   ),
                   XMLELEMENT("arg1", 1),
                   XMLAGG(XMLELEMENT("arg2", COLUMN_VALUE)),                   
                   XMLELEMENT("arg3", 'basic')
               )
           )
       )
   INTO res
   FROM TABLE(nodes);
   
   RETURN res;

END GenerateRequest;

這就是 HTTP 呼叫。請求和響應消息都定義為CLOB/XMLTYPE：

PROCEDURE GetServices(node IN VARCHAR_TABLE_TYPE) IS

  l_http_request    UTL_HTTP.REQ;
  l_http_response   UTL_HTTP.RESP;
  BUFFER VARCHAR2(32767);
  l_response_clob   CLOB;
   p_soap_msg CLOB;
   msg_length INTEGER;
   p_url VARCHAR2(4000) := 'http://zz/uu/services.asmx'

   amount INTEGER := 10000;
   OFFSET PLS_INTEGER := 1;

BEGIN
   
   p_soap_msg := GenerateRequest(node).GetClobVal();
   msg_length := DBMS_LOB.GETLENGTH(p_soap_msg);
   
  l_http_request := UTL_HTTP.BEGIN_REQUEST(p_url, 'POST', UTL_HTTP.HTTP_VERSION_1_1);
  UTL_HTTP.SET_HEADER(l_http_request, 'Content-Type', 'text/xml; charset="utf-8"');
  UTL_HTTP.SET_HEADER(l_http_request, 'Content-Length', msg_length);

   LOOP
       EXIT WHEN OFFSET > msg_length;
       DBMS_LOB.READ(p_soap_msg, amount, OFFSET, BUFFER);
       UTL_HTTP.WRITE_TEXT(l_http_request, BUFFER);
       OFFSET := OFFSET + amount;
   END LOOP;
  l_http_response := UTL_HTTP.GET_RESPONSE(l_http_request);

  DBMS_LOB.CREATETEMPORARY(l_response_clob, TRUE, DBMS_LOB.CALL);
  DBMS_LOB.OPEN(l_response_clob, DBMS_LOB.LOB_READWRITE);
 
   BEGIN
   LOOP
       UTL_HTTP.READ_TEXT(l_http_response, BUFFER, 8192);
       DBMS_LOB.WRITEAPPEND(l_response_clob, LENGTH(BUFFER), BUFFER);
   END LOOP;
   EXCEPTION
       WHEN UTL_HTTP.END_OF_BODY THEN
           UTL_HTTP.END_RESPONSE(l_http_response);
   END;
  DBMS_LOB.CLOSE(l_response_clob);

   INSERT INTO T_TEMP_SOAP_RES VALUES (XMLTYPE(l_response_clob));
   COMMIT;
  DBMS_LOB.FREETEMPORARY(l_response_clob);

EXCEPTION
   WHEN OTHERS THEN
       Logging.put('Error while getting Service', ErrCode => SQLCODE, ErrMessage => SQLERRM ||CHR(13)|| SUBSTR(l_response_clob, 1, 900));
       RAISE;
END GetServices;

引用自：https://dba.stackexchange.com/questions/283708