ORA-24247 when using utl_http.read_text. Only appear after a delay of about 60 seconds

Question

I am using the following code to read the response of an http request:

      FUNCTION readClob (ww_reponse IN OUT NOCOPY UTL_HTTP.resp)
        RETURN CLOB
      IS
        l_clob   CLOB;
        l_text   VARCHAR2 (32767);
      BEGIN
        DBMS_LOB.createtemporary (l_clob, FALSE);
        BEGIN
          LOOP
=>          UTL_HTTP.read_text (ww_reponse, l_text, 32767);
            DBMS_LOB.writeappend (l_clob, LENGTH (l_text), l_text);
          END LOOP;
        EXCEPTION
          WHEN UTL_HTTP.end_of_body
          THEN
            UTL_HTTP.end_response (ww_reponse);
        END;

This is working fine for small request, but for longer request (about >60 sec), I get the ORA-24247 error (network access denied by access control list (ACL)) on the read_text instruction, but to my surprise not on the first call but on some random next iteration, about 60 seconds later. My ACL are ok, otherwise nothing would ever work. I also have set a larger timeout. Obviously the error is not the 'real' error. Anyone have the same problem and solution ? Thank you.

Environment: Oracle Database 11g Enterprise Edition on Windows Server 2008(64bits)

Answer 1

I finally went to Oracle support and found this: Drop and recreate the ACL as shown below

BEGIN 
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL( 
  acl => 'wallet-acl.xml', 
  description => 'Wallet ACL', 
  principal => 'APPS', 
  is_grant => TRUE, 
  privilege => 'use-client-certificates'); 

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE( 
  acl => 'wallet-acl.xml', 
  principal => 'APPS', 
  is_grant => TRUE, 
  privilege => 'use-passwords'); 

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL( 
  acl => 'wallet-acl.xml', 
  wallet_path => 'file:'); 
 END; 
 /

BUT then the message I get is corrupted (misses some characters).

I have added the sleep instruction in the loop and now everything is fine. Not a real solution but a workaround at best.

      FUNCTION readClob (ww_reponse IN OUT NOCOPY UTL_HTTP.resp)
        RETURN CLOB
      IS
        l_clob   CLOB;
        l_text   VARCHAR2 (32767);
      BEGIN
        DBMS_LOB.createtemporary (l_clob, FALSE);
        BEGIN
          LOOP
            UTL_HTTP.read_text (ww_reponse, l_text, 32767);
=>          **dbms_lock.sleep(0.1);**
            DBMS_LOB.writeappend (l_clob, LENGTH (l_text), l_text);
          END LOOP;
        EXCEPTION
          WHEN UTL_HTTP.end_of_body
          THEN
            UTL_HTTP.end_response (ww_reponse);
        END;