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

    Forum Stats

  • 3,714,556 Users
  • 2,242,576 Discussions
  • 7,844,931 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

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

Jeff'
Jeff' Member Posts: 4 Red Ribbon

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, '@@[email protected]@', :n.dptid);

           l_REQUEST := REPLACE(l_REQUEST, '@@[email protected]@', :n.userid);

           l_REQUEST := REPLACE(l_REQUEST, '@@[email protected]@', :n.apptid);

           l_REQUEST := REPLACE(l_REQUEST, '@@[email protected]@', :n.refid);

           l_REQUEST := REPLACE(l_REQUEST, '@@[email protected]@', :n.refapp);

           l_REQUEST := REPLACE(l_REQUEST, '@@[email protected]@', TO_CHAR(:n.dtedeb, 'yyyy-mm-dd'));

           l_REQUEST := REPLACE(l_REQUEST, '@@[email protected]@', TO_CHAR(:n.dtefin, 'yyyy-mm-dd'));

           l_REQUEST := REPLACE(l_REQUEST, '@@[email protected]@', :n.numnat);

           l_REQUEST := REPLACE(l_REQUEST, '@@[email protected]@', :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 ?

Answers

  • Billy Verreynne
    Billy Verreynne Member Posts: 28,065 Red Diamond

    2 major mistakes.

    Violating the PL/SQL language's exception management, by suppressing actual exceptions and transforming this into output parameters.

    Violating the integrity of business transactions by executing an action (calling a web service) that cannot be rolled when the transaction is rolled back.

    Don't. And don't.

    Schedule the action for processing or execution in the trigger, that adheres to transaction boundaries. On rollback, action is not executed. On commit, the action is executed. This can be done via Advance Queues (AQ) or database jobs (DBMS_JOB).

  • Jeff'
    Jeff' Member Posts: 4 Red Ribbon

    Hi Billy,

    I forwarded your comments to my colleague. Thanks for him.

    He found the issues and will consider them.

Sign In or Register to comment.