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 🙂
thatjeffsmith
Author

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

18 Comments

  1. Hello Jeff,

    My Oracle stored procedure is being integrated with java backend service using ORDS.
    The problem I am facing is, I have some set of input parameters for my stored procedure which has traditional naming convention like
    p_firstname
    p_lastname
    p_postcode
    etc.

    But the Java service is having them as(as they don’t follow these naming conventions)
    forename
    surname
    postcode

    Due to this I am facing problem as I am not able to capture the input from them due to different parameter names.
    And ofcourse, we cannot use positioned notation instead of named notation because input is in json.
    So it has to be key-value thing. So it has to be like
    “forename”:”Dennis”
    “surname”:”Roche”
    “postcode”:”BD12BN”

    Can you suggest anything to handle this without breaking the input parameter naming convention at my end?

    • thatjeffsmith

      I don’t understand the problem I guess. Isn’t there code that interfaces your java app with the REST api? Can that code not handle going from p_firstname to forename?

      So it has to be key-value thing. So it has to be like
      “forename”:”Dennis”
      “surname”:”Roche”
      “postcode”:”BD12BN”

      WHAT has to be that way? The response from ORDS or the POST body on a execute?

    • Thanks Jeff.

      “WHAT has to be that way? The response from ORDS or the POST body on a execute?”

      The POST body on a execute has to be that way.

      “forename”:”Dennis”
      “surname”:”Roche”
      “postcode”:”BD12BN”.. This is the POST body and my Oracle SP has IN parameters like p_firstname, p_lastname, p_postcode…

      “ Isn’t there code that interfaces your java app with the REST api? Can that code not handle going from p_firstname to forename?”
      Sorry, I couldn’t understand this part. Is this something that you are thinking that should be there at my end(Oracle) or Java end?
      I can possibly create a package or a wrapper procedure but that would run into the same issue as my wrapper procedure would be like:

      CREATE OR REPLACE PROCEDURE prc_test_ords_wrapper (
      forename VARCHAR2,
      surname VARCHAR2,
      postcode VARCHAR2
      ) AS
      BEGIN
      prc_get_ords_org(p_first_name => forename, p_surname => surname, p_postcode => postcode);
      END;

      So it is like somewhere I am bound to use their parameter name(due to key-value thing in POST body for my proc) which would break my naming convention.
      That’s my question all about.

  2. vikas sharma Reply

    How to use PLSQL table with ORDS. when using 11gR2 database. I tried creating a REST service PL/SQL block based and wrote a package procedure with PL/SQL table (array) one parameter.when i call it in REST handler i get error wrong type argument.

    Following is the code

    create or replace PACKAGE PKG1 AS
    TYPE vc_arr is table of varchar2(30) index by BINARY_INTEGER;

    PROCEDURE INS_C
    (
    P_NAME IN vc_arr

    );
    /* TODO enter package declarations (types, exceptions, methods etc) here */

    END PKG1;

    create or replace PACKAGE body PKG1 AS

    PROCEDURE INS_C
    (
    P_NAME IN vc_arr

    ) AS
    BEGIN
    FOR I IN 1..p_name.count LOOP

    INSERT INTO contact (
    name,
    phone,
    column2
    ) VALUES (
    p_name(i),
    null
    ,null

    );

    end loop;

    END INS_C;

    end;

    REST handler code :

    begin

    pkg1.ins_c(:p_name);

    end;

  3. 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

  4. 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

  5. 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.

  6. 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

    • thatjeffsmith

      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.

    • 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

  7. 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

    • 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

Write A Comment