APEX

Here are the schemas that are involved (that I know of):

1. APEX040200 - The owner of the APEX installation
2. APEX_PRS_MAPS - The parsing schema of the application
3. APEX_MAPS - The owner of the PL/SQL function to be called from APEX
4. APEX_PUBLIC_USER - Included as a desperate attempt to get this to work.

I have a small PL/SQL function wrapper (owned by APEX_MAPS) that calls the APEX_WEB_SERVICE.MAKE_REST_REQUEST method.

CREATE OR REPLACE FUNCTION APEX_MAPS.NOTIFY_CLIENTS( as_MessageType IN VARCHAR2,

                                                     as_MessageTitle IN VARCHAR2,

                                                     as_Message IN VARCHAR2 )

RETURN CLOB

AUTHID DEFINER

IS

    lclb_WebResponse CLOB;

    CURSOR lcsr_GetWebSocketPort IS

        SELECT PARAMETER_VALUE AS WEB_SOCKET_PORT

        FROM MAPS_PARAMETER

        WHERE PARAMETER_NAME = 'WEB_SOCKET_PORT';

    ls_WebSocketPort    MAPS_PARAMETER.PARAMETER_VALUE%TYPE;

    WEB_SOCKET_HOST CONSTANT VARCHAR2(64) := SYS_CONTEXT( 'USERENV', 'SERVER_HOST' ) ;

BEGIN

    OPEN lcsr_GetWebSocketPort;

    FETCH lcsr_GetWebSocketPort INTO ls_WebSocketPort;

    CLOSE lcsr_GetWebSocketPort;

    lclb_WebResponse := APEX_WEB_SERVICE.MAKE_REST_REQUEST

                        ( p_url         => WEB_SOCKET_HOST || ':' || ls_WebSocketPort,

                          p_http_method => 'POST',

                          p_body        => '{"message":"'|| as_MessageType ||

                                           '|' || as_MessageTitle ||

                                           '|' || as_Message || '"}' );

    RETURN lclb_WebResponse;

END;

/

I can successfully execute this as all of the above users from within TOAD,SQL*Plus, etc.

However, *only* when I try to call this same function from APEX, I get the following error stack every time:

sqlerrm:ORA-29273: HTTP request failed

ORA-06512: at "SYS.UTL_HTTP", line 1130

ORA-24247: network access denied by access control list (ACL)

Here are some sanity check query results:

SELECT * FROM DBA_NETWORK_ACLS;

SELECT * FROM DBA_NETWORK_ACL_PRIVILEGES;

What am I missing?

-Joe