how use procedure in oracle rest data services

Question

I have a oracle stored procedure like below:

CREATE OR REPLACE PROCEDURE SABAPAY.omid (ttime OUT VARCHAR2,tYear OUT INTEGER, tMonth OUT INTEGER)
    IS
        year1 NUMBER;
        month1 VARCHAR2 (20);
        month2 NUMBER;
    BEGIN
        SELECT SPT.FISCAL_YEAR, SPT.PAY_MONTH
          INTO year1, month2
          FROM SABAPAY.PAY_TIMEBUCKET spt
         WHERE SPT.IS_CURRENT = '1';
        tyear := year1;
        tmonth := month2;
        ttime := year1 || '  ' || month2;
    END;

I want to create web service with oracle rest data services. how can I create module for this? I do it with below script

BEGIN
    ORDS.define_service (p_module_name      => 'testmodule1',
                         p_base_path        => 'testmodule1/',
                         p_pattern          => 'tt/',
                         p_source_type      => 'plsql/block',
                         p_source           => 'DECLARE
    ttime    VARCHAR2 (200);
    tyear    INTEGER;
    tmonth   INTEGER;
BEGIN
    SABAPAY.omid (ttime, tYear, tMonth);

END;',
                         p_items_per_page   => 0);

    COMMIT;
END;

but when I run the url I'm getting nothing my db and oracle rest data services server is separated

Answer 1

You have to define a handler, not just a module

-- Generated by Oracle SQL Developer REST Data Services 19.2.1.247.2212
-- Exported REST Definitions from ORDS Schema Version 19.3.0.b2541456
-- Schema: HR   Date: Tue Oct 01 08:32:57 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    => 'so_plsql',
      p_base_path      => '/so_plsql/',
      p_items_per_page =>  25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);      
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'so_plsql',
      p_pattern        => 'do_nothing',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'so_plsql',
      p_pattern        => 'do_nothing',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_items_per_page =>  0,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      -- your SABAPAY.omid (ttime, tYear, tMonth); codes goes here
      p_source         => 
'begin
 do_nothing();
end;'
      );


  COMMIT; 
END;

I implemented this as a POST handler for my HTTP call, because I'm not sure what your stored procedure does - if it makes any changes to your system, it probably shouldn't be a GET.

If it's not a GET, you'll need to use cURL or POSTMAN (GUI) to make the POST call when you're testing.

I have more examples here.