I get asked this question all the time…can ORDS help me do a HTTP call from the database?
And the simple answer is, ‘No, not really.’
ORDS is responding to HTTP requests directed to the database, vs making HTTP requests.
But…if you had Oracle Database that needed to get data from another database where there was no DB_LINK available, then ORDS and HTTP might be a solution.
Making the extremely obvious disclaimer
It doesn’t make any sense to do this, if the ORDS you are talking to…is servicing the same database. Having the DB make a GET or POST call to ORDS that then uses the JDBC connection pool to get back into your database to do the request…that makes no sense. You would simply use SQL and PL/SQL to get what you needed vs adding the network and HTTP to the equation.
This scenario is for when you need to get to ANOTHER database and there is no DB_LINK at your disposal.
Access Control Lists (ACLs)
Pronounced ‘ackles’ – you’re going to need one of these if you’re in a 12c or higher versioned instance of Oracle. These rules say who can do what when it comes to the database reaching out to the network.
I need to give my user permission to to reach out to ORDS on the network. Note that NULLs on these calls act as wild cards, so the more generic you get, the more power you are granting to the user. Use these with extreme discretion, and ALWAYS work with your DBA.
BEGIN DBMS_NETWORK_ACL_ADMIN.create_acl ( acl => 'get_ords', description => 'let me reach out to ords', principal => 'HR', is_grant => TRUE, privilege => 'connect', start_date => SYSTIMESTAMP, end_date => NULL); END; / BEGIN DBMS_NETWORK_ACL_ADMIN.assign_acl ( acl => 'get_ords', host => '10.39.205.48', lower_port => 8080, upper_port => NULL); END; /
If I try to run my program without these rules in place, I get told ‘no-no-no!’
ERROR at line 1: ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1029 ORA-24247: network access denied by access control list (ACL) ORA-06512: at line 7
We’re just going to do a simple GET to a secured ORDS REST Service, and then print the response using DBMS_OUTPUT.
CREATE OR REPLACE PROCEDURE show_json_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 UTL_HTTP.end_response(l_http_response); RAISE; END show_json_from_url;
And then time to invoke it –
The ORDS back end code
Again, very simple, so use your imagination, but if I wanted to know the local time and timezone for a remote database, then I could use this.
-- Generated by Oracle SQL Developer REST Data Services 22.214.171.1241.1906 -- Exported REST Definitions from ORDS Schema Version 20.1.0.r0801154 -- Schema: HR Date: Mon May 18 16:03:30 EDT 2020 -- DECLARE l_roles OWA.VC_ARR; l_modules OWA.VC_ARR; l_patterns OWA.VC_ARR; BEGIN ORDS.DEFINE_MODULE( p_module_name => 'date', p_base_path => '/date/', p_items_per_page => 25, p_status => 'PUBLISHED', p_comments => NULL); ORDS.DEFINE_TEMPLATE( p_module_name => 'date', p_pattern => 'right_now', p_priority => 0, p_etag_type => 'HASH', p_etag_query => NULL, p_comments => NULL); ORDS.DEFINE_HANDLER( p_module_name => 'date', p_pattern => 'right_now', p_method => 'GET', p_source_type => 'json/item', p_items_per_page => 25, p_mimes_allowed => '', p_comments => NULL, p_source => 'select systimestamp, sysdate from dual' ); l_roles(1) := 'SQL Developer'; l_modules(1) := 'date'; ORDS.DEFINE_PRIVILEGE( p_privilege_name => 'show_date_time', p_roles => l_roles, p_patterns => l_patterns, p_modules => l_modules, p_label => '', p_description => '', p_comments => NULL); COMMIT; END;
What about HTTPS and Certs and…?
Go right back to Tim’s post. He has lovely examples there.
A much more interesting example
With REST Enabled SQL, what if you had a VIEW that when you selected from it, it actually got the data from a remote database via ORDS and the REST Enabled SQL feature?
My teammate Dermot wrote this exact scenario. It’s definitely work a look-see-try!