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;


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 Senior Principal Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

Write A Comment