Parameters and Binds for your RESTful Services: RESULTSETs

thatjeffsmith ORDS 12 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;

The $LINK Trick Works Here, Too

Remember when we talked about using $notation to indicate to ORDS we were working with links?

In that example, we were using GET Handlers with SQL statements.

Today, someone asked about POST and PL/SQL – could they still use this ‘trick’ to generate a link?

The answer is, ‘Yes.’

One way to go about it is this – have an OUT parameter for your RESTful Service POST Handler that is of type ‘RESULTSET’ – what we’ve been talking about here. And again, we’re going to have a refcursor. But, we’re going to ‘inject’ an extra column into the SQL driving our cursor. Instead of a SELECT * FROM for P_OUT_EMP, we’re going to have…

OPEN P_OUT_EMP FOR
    SELECT id, name, salary, '/stuff/link' "$goto"
    FROM   SIMPLE_EMP
    WHERE  ID = NEW_ID;

Now, when our store procedure output is returned on the POST Response Body, we get –

{
    "EMP": [
        {
            "id": 1,
            "name": "JEFF",
            "salary": 2500,
            "links": [
                {
                    "rel": "goto",
                    "href": "http://localhost:8080/stuff/link"
                }
            ]
        }
    ],
    "NumOfEmps": 1
}

The $LINK trick works here too 🙂

Tell Others About This Story:

Comments 12

  1. Hi Jeff

    Is there any way if we are using htp.p to return formatted JSON to ORDS to use the $link functionality for links?
    From my simple tests it looks like it will not be interpreted on the way out.

    Thanks,
    Adrian

  2. Hi Jeff,
    I forgot to mention that I use PL/Sql to gererate my json Data using GET. I think the correct Question is how to make Pagination using Pl/sql Source Typ for GET method?

    Regards
    Pierre

    1. thatjeffsmith Post
      Author
  3. Hi Jeff,

    Thanks for the useful informations. It help me a lot. Ords is great.

    … Do you know how to make Pagination with Resulset? Is there a Build-in method to paginate my Service, when I use Resulset?

    Regards
    Pierre.

  4. Hi Jeff,
    Thank again. I will check that.

    I have another question is about how to handle null and empty value. When the Service give me something like that
    {
    empno:1,
    ename:null
    }

    How to hide ename when value is null or empty?

    Regards
    Pierre

    1. thatjeffsmith Post
      Author

      You can’t…not unless you code your service to check for ename, and only it it’s not null, to include it in your query..but that’s a lot of work. You can however replace the null using a NVL() function call in your SQL code, or just have your application handle it.

      1. Hi Jeff,

        “You can’t…not unless you code your service to check for ename, and only it it’s not null, to include it in your query.”

        How to make that? I have tried , but not works.

        Regards

        Pierre

      2. thatjeffsmith Post
        Author
  5. Hi Jeff,
    thank for the Useful Information. It help me. I have another Question. How to use Resulset to make nesting? Like so for example

    {
    EMP: [
    {
    id: 1,
    name: 1,
    salary: 900,
    dept: deptnodata: [
    {
    DEPTNO: 1,
    DEPTNAME: ‘hallo’
    },
    {
    DEPTNO: 2,
    DEPTNAME: ‘hallob’
    }

    ]
    },
    {
    id: 832635,
    name: 2,
    salary: 900,
    dept: deptnodata: [
    {
    DEPTNO: 40,
    DEPTNAME: ‘halloc’
    },
    {
    DEPTNO: 144,
    DEPTNAME: ‘hallod’
    }

    ]
    }
    ]
    }

    Thanks

    Pierre

    1. thatjeffsmith Post
      Author
      1. Hi Jeff,

        Thank for the Answers. Is that method better that using APEX_JSON API? Because it is possible to create nested json with apex_json. What is the best method? I ask because we have more than 10000 Data for this Service.

        Thanks
        Pierre

      2. thatjeffsmith Post
        Author

Leave a Reply

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