Parameters and Binds for your RESTful Services: RESULTSETs

thatjeffsmith ORDS 0 Comments

Tell Others About This Story:

Yesterday I talked about how to grab the output returned from your stored procedure when executing via Oracle REST Data Services.

The example was:
INSERT a new record into a table. Return the new employee ID and the total number of employees in the table.

So we just needed 2 parameters for our handler to catch the two integers returned, easy-peasy.

But, what if we were going to return the entire record?

So let’s write the stored procedure first, again not using my PL/SQL as anything other than the most basic of examples…

CREATE OR REPLACE PROCEDURE ADD_EMP2 (
   P_EMP_NAME    IN  SIMPLE_EMP.name%TYPE,
   P_EMP_SALARY  IN  SIMPLE_EMP.SALARY%TYPE,
   P_OUT_EMP     OUT sys_refcursor,
   P_OUT_TOTAL   OUT INTEGER
) AS
 NEW_ID INTEGER;
BEGIN
   INSERT INTO SIMPLE_EMP (name, SALARY)
   VALUES (P_EMP_NAME, P_EMP_SALARY)
   RETURN id INTO NEW_ID;
 
   OPEN P_OUT_EMP FOR
    SELECT *
    FROM   SIMPLE_EMP
    WHERE  ID = NEW_ID;
 
   SELECT COUNT(id) INTO P_OUT_TOTAL FROM SIMPLE_EMP;
 
EXCEPTION
   WHEN OTHERS
   THEN HTP.print(SQLERRM);
END;

Now we need to create our RESTful Services Template (AddGetEmp) and Handler (POST).

So the URI Module is ‘/rpc’ and the URI Pattern is ‘AddGetEmp’

The code behind the RESTful Service is very basic, same as before, except now we need to ‘catch’ the ID and refcursor.

BEGIN
    ADD_EMP2(P_EMP_NAME    => :P_EMP_NAME,
            P_EMP_SALARY   => :P_EMP_SALARY,
            P_OUT_EMP      => :NEW_RECORD,
            P_OUT_TOTAL    => :TOTAL);
COMMIT;
END;

And now we need to define the parameters, one for :NEW_RECORD, and one for :TOTAL.

oracle rest data services module parameter resultset

Ding-ding-ding!

Now we SAVE the definition – don’t forget this step or your testing will result in some very nice and frustrating 404’s (also not forgetting to check the ‘publish’ box when creating the module to begin with!)

REST client POST to ORDS to insert new record and get back employee record

My total number of employees and the employee I just created.

Now I’ll right click to export my Module to PL/SQL and share the entire thing with you:

sql developer export rest module

Generates PL/SQL block to create this module. Good thing to tie into your source code repository for sure.

-- Generated by Oracle SQL Developer REST Data Services 4.2.0.17.065.2202
-- Exported REST Definitions from ORDS Schema Version 3.0.9.348.07.16
-- Schema: HR   Date: Fri Mar 10 09:37:59 EST 2017
--
BEGIN
  ORDS.ENABLE_SCHEMA(
      p_enabled             => TRUE,
      p_schema              => 'HR',
      p_url_mapping_type    => 'BASE_PATH',
      p_url_mapping_pattern => 'peeps',
      p_auto_rest_auth      => FALSE);    
 
  ORDS.DEFINE_MODULE(
      p_module_name    => 'rpc',
      p_base_path      => '/rpc/',
      p_items_per_page =>  25,
      p_status         => 'PUBLISHED');      
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'rpc',
      p_pattern        => 'AddEmp',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL);            
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'rpc',
      p_pattern        => 'AddEmp',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_items_per_page =>  0,
      p_mimes_allowed  => 'application/json',
      p_source         => 
'declare
   -- new_empID NUMBER;
BEGIN
    ADD_EMP(P_EMP_NAME     => :P_EMP_NAME,
            P_EMP_SALARY   => :P_EMP_SALARY,
            P_OUT_ID       => :NEW_EMPID,
            P_OUT_TOTAL    => :TOTAL);
commit;
END;'
      );
  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'rpc',
      p_pattern            => 'AddEmp',
      p_method             => 'POST',
      p_name               => 'ID',
      p_bind_variable_name => 'new_empID',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'INT',
      p_access_method      => 'OUT');      
  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'rpc',
      p_pattern            => 'AddEmp',
      p_method             => 'POST',
      p_name               => 'NumofEmps',
      p_bind_variable_name => 'TOTAL',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'INT',
      p_access_method      => 'OUT');      
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'rpc',
      p_pattern        => 'AddGetEmp',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL);            
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'rpc',
      p_pattern        => 'AddGetEmp',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_items_per_page =>  0,
      p_mimes_allowed  => 'application/json',
      p_source         => 
'begin
    ADD_EMP2(P_EMP_NAME    => :P_EMP_NAME,
            P_EMP_SALARY   => :P_EMP_SALARY,
            P_OUT_EMP      => :NEW_RECORD,
            P_OUT_TOTAL    => :TOTAL);
commit;
END;'
      );
  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'rpc',
      p_pattern            => 'AddGetEmp',
      p_method             => 'POST',
      p_name               => 'EMP',
      p_bind_variable_name => 'NEW_RECORD',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'RESULTSET',
      p_access_method      => 'OUT');      
  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'rpc',
      p_pattern            => 'AddGetEmp',
      p_method             => 'POST',
      p_name               => 'NumOfEmps',
      p_bind_variable_name => 'TOTAL',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'INT',
      p_access_method      => 'OUT');      
 
 
  COMMIT; 
END;
Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Leave a Reply

Your email address will not be published. Required fields are marked *