Calling ORDS from INSIDE the Database

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.

Having the database make a HTTP call from PL/SQL

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)

One Does Not Simply Meme | ONE DOES NOT SIMPLY; MAKE AN HTTP CALL OUT FROM THE DATABASE | image tagged in memes,one does not simply | made w/ Imgflip meme maker

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

The Code

Hat tip to Tim, I basically borrowed a copy of his example on the UTL_HTTP package.

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 –

Please, please, please don’t use my passwords. And avoid Basic Auth if you can.

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 19.4.0.351.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!