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 ?