Access Google Login Page

DECLARE

req utl_http.req;

l_response utl_http.resp;

BEGIN

utl_http.set_response_error_check(enable => true);

-- setup the wallet

utl_http.set_wallet('file:&WALLPATH', '&password');

req := utl_http.begin_request(

url => 'https://www.google.com/accounts/Login',

method => 'GET');

l_response := utl_http.get_response(req);

utl_http.end_request(req);

dbms_output.put_line('Code: ' || l_response.status_code);

dbms_output.put_line('Code: ' || l_response.reason_phrase);

END;

Troubleshooting

Issue:

Error report -

ORA-29273: HTTP request failed

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

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

ORA-06512: at line 10

29273. 00000 - "HTTP request failed"

*Cause: The UTL_HTTP package failed to execute the HTTP request.

*Action: Use get_detailed_sqlerrm to check the detailed error message.

Fix the error and retry the HTTP request.

Fix:

DECLARE

ACL_PATH VARCHAR2(4000);

ACL_ID RAW(16);

BEGIN

-- Look for the ACL currently assigned to '*' and give FLOWS_030000

-- the "connect" privilege if FLOWS_030000 does not have the privilege yet.

SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS

WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

-- Before checking the privilege, make sure that the ACL is valid

-- (for example, does not contain stale references to dropped users).

-- If it does, the following exception will be raised:

--

-- ORA-44416: Invalid ACL: Unresolved principal 'FLOWS_030000'

-- ORA-06512: at "XDB.DBMS_XDBZ", line ...

--

SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID

FROM XDB.XDB$ACL A, PATH_VIEW P

WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND

EQUALS_PATH(P.RES, ACL_PATH) = 1;

DBMS_XDBZ.ValidateACL(ACL_ID);

IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, '&&USER', 'connect')

IS NULL THEN

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,

'&&USER', TRUE, 'connect');

END IF;

EXCEPTION

-- When no ACL has been assigned to '*'.

WHEN NO_DATA_FOUND THEN

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',

'ACL that lets power users to connect to everywhere',

'&&USER', TRUE, 'connect');

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');

END;

/

COMMIT;

Issue:

Error report -

ORA-29273: HTTP request failed

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

ORA-29024: Certificate validation failure

ORA-06512: at line 10

29273. 00000 - "HTTP request failed"

*Cause: The UTL_HTTP package failed to execute the HTTP request.

*Action: Use get_detailed_sqlerrm to check the detailed error message.

Fix the error and retry the HTTP request.

Fix:

orapki wallet add -wallet ${WALLPATH} -trusted_cert -cert "/u01/dba/ssl/GeoTrustGlobalCA1.cer" -pwd password

orapki wallet add -wallet ${WALLPATH} -trusted_cert -cert "/u01/dba/ssl/GeoTrustGlobalCA2.cer" -pwd password

Bibliography