8 Replies Latest reply: Jun 13, 2012 1:23 PM by damorgan RSS

    UTL_HTTP Works fine from standalone procedure, not working from cursor

    Georgey
      I have a procedure which sends error (a message) over a URL. The ACL has been defined and the procedure works fine, But when I try to call this procedure from another procedure which is inside the same package, it throws me the following error.

      ORA-29273: HTTP request failed
      ORA-06512: at "SYS.UTL_HTTP", line 1369
      ORA-29263: HTTP protocol error

      The procedure is
      PROCEDURE SEND_ERROR(P_MESSAGE_ID VARCHAR2, P_ERROR_MESSAGE VARCHAR2)
      AS
      L_REQ UTL_HTTP.REQ;
      L_RESP UTL_HTTP.RESP;
      L_STATUS_CODE VARCHAR2(100);
      L_REQ_STRING VARCHAR2(4000);

      BEGIN

      L_REQ_STRING := 'http://127.61.9.4/TestUrlParamsEcho?messageId=='||P_MESSAGE_ID||'&errorMsg=='||P_ERROR_MESSAGE;

      --Making sure than the spaces in URL are replaced with %20
      L_REQ_STRING := REPLACE(L_REQ_STRING,' ','%20');
      --Making sure that the URL string is less than 2000 characters
      L_REQ_STRING := SUBSTR(L_REQ_STRING,1,1990);
      -- Trim the end of the string if it has %20
      L_REQ_STRING := RTRIM(L_REQ_STRING,'%20');

      --Begin Request
      L_REQ := UTL_HTTP.BEGIN_REQUEST(L_REQ_STRING);
      --Get Response
      L_RESP := UTL_HTTP.GET_RESPONSE(L_REQ);
      L_STATUS_CODE := L_RESP.STATUS_CODE;

      INSERT INTO STATUS_CODE_RECD (STATUS_CODE, RECD_TIME)VALUES(L_STATUS_CODE,SYSTIMESTAMP);

      END SEND_ERROR;
      ---------------------------------------------------------------------------------------------------------------------
      This procedure runs fine when I pass parameters.
      ---------------------------------------------------------------------------------------------------------------------



      Then I tried to call it inside the following cursor

           OPEN C_ERROR_DATA;
           LOOP
           FETCH C_ERROR_DATA INTO L_MESSAGE_ID, L_ERROR_MESSAGE;
           EXIT WHEN C_ERROR_DATA%NOTFOUND;
      SEND_VALIDATION_ERROR(L_MESSAGE_ID, L_ERROR_MESSAGE);
           END LOOP;     
           CLOSE C_ERROR_DATA;
      ---------------------------------------------------------------------------------------------------------------------

      ORA-29273: HTTP request failed
      ORA-06512: at "SYS.UTL_HTTP", line 1369
      ORA-29263: HTTP protocol error

      Thanks in advance.