Steps:
Installed oracle xe 18c on windows 64 bit
Oracle developer suite 10g
Oracle Wallet Manager
Website Refer:
http://jsonplaceholder.typicode.com/posts/1
https://jsonplaceholder.typicode.com/posts/1
Refer: https://oracle-base.com/articles/misc/utl_http-and-ssl
--step01 from sys
create user web_user IDENTIFIED BY web_user;
grant execute on utl_http to web_user;
grant create procedure to web_user;
grant create session to web_user;
step02 create acl
--run from sys
--acl => web_user_all_open.xml
BEGIN
dbms_network_acl_admin.create_acl(
acl => 'web_user_all_open.xml'
,description => 'web user acl blanket access'
,principal => 'WEB_USER'
,is_grant => true
,privilege => 'connect'
,start_date => systimestamp
,end_date => NULL
);
commit;
END;
-----
BEGIN
dbms_network_acl_admin.assign_acl(acl => 'web_user_all_open.xml',host => '*',lower_port => 1,upper_port => 9999);
COMMIT;
END;
--
SELECT * FROM dba_network_acls;
SELECT * FROM dba_network_acl_privileges;
step03-- create a procedure
CREATE OR REPLACE PROCEDURE show_html_from_url (
p_url IN VARCHAR2,
p_username IN VARCHAR2 DEFAULT NULL,
p_password IN VARCHAR2 DEFAULT NULL
) AS
l_http_request utl_http.req;
l_http_response utl_http.resp;
l_text VARCHAR2(32767);
BEGIN
-- Make a HTTP request and get the response.
l_http_request := utl_http.begin_request(p_url);
-- Use basic authentication if required.
IF
p_username IS NOT NULL AND p_password IS NOT NULL
THEN
utl_http.set_authentication(l_http_request,p_username,p_password);
END IF;
l_http_response := utl_http.get_response(l_http_request);
-- Loop through the response.
BEGIN
LOOP
utl_http.read_text(l_http_response,l_text,32766);
dbms_output.put_line(l_text);
END LOOP;
EXCEPTION
WHEN utl_http.end_of_body THEN
utl_http.end_response(l_http_response);
END;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('exception'||sqlerrm);
utl_http.end_response(l_http_response);
RAISE;
END show_html_from_url;
/
step04
SET SERVEROUTPUT ON;
BEGIN
show_html_from_url('http://jsonplaceholder.typicode.com/posts/1');
END;
--{
-- "userId": 1,
-- "id": 1,
-- "title": "sunt aut facere repellat provident occaecati excepturi optio reprehenderit",
-- "body": "quia et suscipit\nsuscipit recusandae consequuntur expedita et cum\nreprehenderit molestiae ut ut quas totam\nnostrum rerum est autem sunt rem eveniet architecto"
--}
--
--PL/SQL procedure successfully completed.
step05
SET SERVEROUTPUT ON;
BEGIN
show_html_from_url('https://jsonplaceholder.typicode.com/posts/1');
END;
/
--exceptionORA-29273: HTTP request failed
--
--
--Error starting at line : 3 in command -
--BEGIN
-- show_html_from_url('https://jsonplaceholder.typicode.com/posts/1');
--END;
--Error report -
--ORA-29273: HTTP request failed
--ORA-06512: at "WEB_USER.SHOW_HTML_FROM_URL", line 37
--ORA-29024: Certificate validation failure
--ORA-06512: at "SYS.UTL_HTTP", line 380
--ORA-06512: at "SYS.UTL_HTTP", line 1148
--ORA-06512: at "WEB_USER.SHOW_HTML_FROM_URL", line 11
--ORA-06512: at line 2
--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.
-- we need to do the oracle wallet manager:
--https://appsoracle-abhi.blogspot.com/2020/01/create-oracle-wallet-in-windows.html
--create wallet
--F:\DevSuiteHome_1\BIN>mkstore -wrl "C:\Users\abhishek\tmp_w" -create
--Enter password:
--add cerification in wallet
--F:\DevSuiteHome_1\BIN>orapki wallet add -wallet "C:\Users\abhishek\tmp_w" -trusted_cert -cert "F:\git-hub-repos\learnings\adv sql\plsql_steven feuerstein\ACL_HTTP_CHTTP\AddTrustExternalCARoot.crt"
--Enter wallet password:
--
refer links above for how to download the AddTrustExternalCARoot.crt file for a url
07: calling a https service or http over ssl
SET SERVEROUTPUT ON;
BEGIN
UTL_HTTP.set_wallet('file:C:\Users\abhishek\tmp_w', 'abc@123');
show_html_from_url('https://jsonplaceholder.typicode.com/posts/1');
END;
/
--ORA-29273: HTTP request failed
--ORA-06512: at "WEB_USER.SHOW_HTML_FROM_URL", line 37
--ORA-28759: failure to open file--ORA-06512: at "SYS.UTL_HTTP", line 380
--ORA-06512: at "SYS.UTL_HTTP", line 1148
--ORA-06512: at "WEB_USER.SHOW_HTML_FROM_URL", line 11
--ORA-06512: at line 3
--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.
--resolution
--Wallet Manager set permissions on the files, so they can only be read by the user who created them. Either modify the permissions or copy the files to another location.
--And then in your "set_wallet" reference the new location.
--I copied the wallet file from above location to another location, and in windows right click and unchecked read only on folders containting the the ewallet.p12 file.
SET SERVEROUTPUT ON;
BEGIN
UTL_HTTP.set_wallet('file:F:\wallet\tmp_w', 'abc@123');
show_html_from_url('https://jsonplaceholder.typicode.com/posts/1');
-- exception when others then
-- dbms_output.put_line('err'||sqlerrm);
END;
/
--
--{
-- "userId": 1,
-- "id": 1,
-- "title": "sunt aut facere repellat provident occaecati excepturi optio reprehenderit",
-- "body": "quia et suscipit\nsuscipit recusandae consequuntur expedita et cum\nreprehenderit molestiae ut ut quas totam\nnostrum rerum est autem sunt rem eveniet architecto"
--}
--
--
--PL/SQL procedure successfully completed.
REFER:
1. https://oracle-base.com/articles/11g/fine-grained-access-to-network-services-11gr1
2. https://oracle-base.com/articles/misc/utl_http-and-ssl
3.
https://technology.amis.nl/2015/05/11/invoke-a-rest-service-from-plsql-make-an-http-post-request-using-utl_http-in-oracle-database-11g-xe/4. https://jsonplaceholder.typicode.com/
5.https://www.nyquest.com/showpage/making-api-calls-from-oracle
6.https://www.dbasolutions.in/2017/02/understanding-dbmsnetworkacladmin-with.html
7.https://anjo.pt/wp/keyword-oracle/2019/09/
