Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

UTL_HTTP call inside trigger leads to ORA-06502 when inserting from select

Jeff'Jan 21 2021

In order to allow a developer to make a SOAP call from an Oracle 11gR1 (11.1.0.7 PSU 24) instance using UTL_HTTP.
He created a stored procedure :
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;

And a BEFORE INSERT trigger as follows:
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;

Everything works well for an INSERT query with a single value, but fails whenever he calls:

INSERT INTO ... VALUES ...
COMMIT;
INSERT INTO ... VALUES ...
COMMIT;

He gets an ORA-06502.
I suggested him to investigate using SQLDeveloper debugger, but we are not sure that it will lead to something...

What could cause that ?

Comments
Post Details
Added on Jan 21 2021
2 comments
533 views