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)

Take a look at [this article on DBTricks](http://dbtricks.com/?p=159) for info on how to figure out and correct this issue. BTW, the referenced page is the first hit to come up when Googling for ORA-24247. Share and enjoy. — Bob Jarvis - Слава Україні, Nov 29 '13 at 17:25
I started with this article, and my ACL configuration is fine. Either you have right or you dont, it doesn't depend on the duration of the request. That is the really troubling part of this problem, maybe it's better described by this post https://forums.oracle.com/thread/2567975 — Stéphane Gerber, Nov 29 '13 at 19:44

score 1 · Answer 1 · answered Dec 05 '13 at 13:39

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;

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

1 Answers1