Calling a PL/SQL Function via ORDS

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;

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.

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569