Mostly Making oracle easy, mostly:
Search for:
In ORDS

Calling a PL/SQL Function via ORDS

3 Mins Read

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!

CREATE OR REPLACE FUNCTION highest_sal (dept_id IN INTEGER) RETURN NUMBER AS
 top_sal_for_department NUMBER(7,2);
BEGIN
 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!

AUTO PL/SQL

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 –

BEGIN
 
    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);
    COMMIT;
END;

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 18.4.0.376.1900
-- Exported REST Definitions from ORDS Schema Version 18.4.0.r3531846
-- Schema: HR   Date: Tue Mar 19 08:56:53 EDT 2019
--
BEGIN
  ORDS.ENABLE_SCHEMA(
      p_enabled             => TRUE,
      p_schema              => 'HR',
      p_url_mapping_type    => 'BASE_PATH',
      p_url_mapping_pattern => 'hr',
      p_auto_rest_auth      => FALSE);    
 
  ORDS.DEFINE_MODULE(
      p_module_name    => 'emps',
      p_base_path      => '/emp/',
      p_items_per_page =>  25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);      
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'emps',
      p_pattern        => 'max_sal/:department_id',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
  ORDS.DEFINE_HANDLER(
      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'
      );
  COMMIT; 
END;

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.

Author

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

Related Posts

3 Comments

  1. Abraham Olsen Reply

    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.

    Thanks!

    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. Abraham Olsen Reply

    Suppose I do this:
    BEGIN
    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);
    COMMIT;
    END;

    or this:

    ORDS.DEFINE_MODULE(
    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