You have two options:

  • Automatic – you ‘enable’ the function, we do the rest…you don’t have to write any code, but you also don’t really have any say in how we execute it or display the results
  • RESTFul Service – you write the code, you handle the inputs and outputs, you have all the say in pretty much everything

Previously I had demonstrated the AUTO PL/SQL feature (introduced in version 3.0.10 of ORDS) with package.procedures.

Today I want to look at a FUNCTION call.

A most-basic PL/SQL Function – note, that for production use, you’d probably want to log an error or raise an exception if the ID is not valid….bad-Jeff!

 top_sal_for_department NUMBER(7,2);
 SELECT MAX(salary) INTO top_sal_for_department FROM employees
 WHERE department_id = dept_id;
 RETURN top_sal_for_department;
END highest_sal;

Pssst! Looking for help building a REST API that deals with PL/SQL REFCURSORS? Check out this post, I give you 4 different options with example code!


Note that we’re not calling this feature ‘AUTO REST enabling PL/SQL’, as we do for TABLEs and VIEWs. The REST paradigm doesn’t really apply to what we’re doing here – which is really a remote procedure call (RPC) via HTTP.

What ORDS does allow us to do is publish an endpoint for our function very easily, and it handles the inputs and outputs from and to {JSON} automatically, so that’s nice.

Here’s the ORDS package call to enable the FUNCTION –

    ORDS.ENABLE_OBJECT(p_enabled => TRUE,
                       p_schema => 'HR',
                       p_object => 'HIGHEST_SAL',
                       p_object_type => 'FUNCTION',
                       p_object_alias => 'highest_sal',
                       p_auto_rest_auth => FALSE);

Of course you could always just right-click on the function in SQL Developer and say ORDS Enable…

And now let’s call it.

httpie is AWESOME – I can easily send a JSON POST body on the request as you can see above.

RESTful Service

No problem-o. I’m going to publish a GET handler on /emp/highest_sal/:department_id, and my SOURCE TYPE will be ‘query one row’ – no need to worry about paging the results here.

Here’s what that looks like…

-- Generated by Oracle SQL Developer REST Data Services
-- Exported REST Definitions from ORDS Schema Version 18.4.0.r3531846
-- Schema: HR   Date: Tue Mar 19 08:56:53 EDT 2019
      p_enabled             => TRUE,
      p_schema              => 'HR',
      p_url_mapping_type    => 'BASE_PATH',
      p_url_mapping_pattern => 'hr',
      p_auto_rest_auth      => FALSE);    
      p_module_name    => 'emps',
      p_base_path      => '/emp/',
      p_items_per_page =>  25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);      
      p_module_name    => 'emps',
      p_pattern        => 'max_sal/:department_id',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
      p_module_name    => 'emps',
      p_pattern        => 'max_sal/:department_id',
      p_method         => 'GET',
      p_source_type    => 'json/query;type=single',
      p_items_per_page =>  0,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'SELECT highest_sal(:department_id) sal
  FROM dual'

And now let’s call it, again using my nifty httpie CLI tool.

Since I know my FUNCTION is idempotent, I have no problems using a GET handler on it.

If you scroll up through the RESTful Service code handler, you can see the query behind this is

SELECT highest_sal(:department_id) sal FROM dual;

The column alias ‘sal’ is then used by ORDS to generate the JSON response, so I can name that JSON doc attribute anything I want via the SQL.

In summary, lean on AUTO, or roll your own.

I particularly like the AUTO feature for it’s ability to pick up the OUTs, RETURNS for me – REF CURSORS and custom types, and even ‘weird’ things like INTERVALs.


I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.


  1. I see. I guess because I’m on 18.2, I only have ORDS.DROP_REST_FOR_SCHEMA
    Also, the ORDS.ENABLE_OBJECT resulted in 405, method not permitted.

    I guess an upgrade is due.


    BTW, is it planned to be possible to simple use “highest_sal(:department_id)” – without selecting it from DUAL? (safe harbour and all..) it would make a lot of sense for all pl/sql developers, to simply run a function, and not a select – this is a little 10g like 🙂

  2. Suppose I do this:
    ORDS.ENABLE_OBJECT(p_enabled => TRUE,
    p_schema => ‘HR’,
    p_object => ‘HIGHEST_SAL’,
    p_object_type => ‘FUNCTION’,
    p_object_alias => ‘highest_sal’,
    p_auto_rest_auth => FALSE);

    or this:

    p_module_name => ’emps’,
    p_base_path => ‘/emp/’,
    p_items_per_page => 25,
    p_status => ‘PUBLISHED’,
    p_comments => NULL);

    That must create some metadata.

    I understand from the API, that p_enabled => false disables the module.

    But how do I literally remove metadata or objects related to this enabling, when I don’t want them anymore? Or say, if I’ve given them the wrong name?
    I cannot find documentation nor examples of how to remove, delete or throw to recycle bin.

Write A Comment