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 Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

29 Comments

  1. I am using
    SQL Developer : Version 4.1.4.21 .
    Extension: Oracle SQL Developer – RESTful Services Administration oracle.sqldeveloper.rest 12.2.0.21.21

    Unable to export all source code using right click on modules.
    what update should I do ?

  2. This site was… how do I say it? Relevant!! Finally
    I’ve found something which helped me. Thank you!

  3. Hi Jeff,

    Thanks for wonderful articles you have.

    I have a situation where we need to use User defined Type and I tried your method and it won’t work unless Auto REST is enabled. We don’t want to enable Auto REST in our case.

    We have following Types created.

    create or replace type emp_det_obj
    as
    object
    (
    empno number(4),
    ename varchar2(10 byte),
    job varchar2(9 byte),
    sal number(7,2)
    );
    /

    create or replace type emp_det_tab is table of emp_det_obj;
    /

    create or replace function emp_details return emp_det_tab is
    l_emp_det_tab emp_det_tab;

    And Procedure as

    create or replace procedure proc_emp_details ( p_emp_det out emp_det_tab )is
    –l_emp_det_tab emp_det_tab;

    begin
    select
    emp_det_obj(
    empno => e.empno,
    ename => e.ename,
    job => e.job,
    sal => e.sal
    )
    bulk collect
    into p_emp_det
    from
    emp e;
    –return l_emp_det_tab; — when used as function
    end proc_emp_details;
    /

    BEGIN
    ords_admin.enable_schema(
    p_enabled => TRUE,
    p_schema => ‘my_schema’,
    p_url_mapping_type => ‘BASE_PATH’,
    p_url_mapping_pattern => ‘abc3’,
    p_auto_rest_auth => FALSE );
    ORDS_admin.define_module(
    p_schema => ‘my_schema’,
    p_module_name => ‘testprcudt’,
    p_base_path => ‘testprcudt/’,
    p_items_per_page => 0);

    ORDS_admin.define_template(
    p_schema => ‘my_schema’,
    p_module_name => ‘testprcudt’,
    p_pattern => ‘proc_emp_details’);

    ORDS_admin.define_handler(
    p_schema => ‘my_schema’,
    p_module_name => ‘testprcudt’,
    p_pattern => ‘proc_emp_details’,
    p_method => ‘POST’,
    p_source_type => ORDS.source_type_plsql,
    p_source => ‘
    BEGIN
    proc_emp_details(:l_emp_det_tab);
    END;’,
    p_items_per_page => 0);

    ORDS_admin.define_parameter(
    p_schema => ‘MA_REVIEW_STG’,
    p_module_name => ‘testprcudt’,
    p_pattern => ‘proc_emp_details’,
    p_method => ‘POST’,
    p_name => ‘l_emp_det_tab’,
    p_bind_variable_name => ‘l_emp_det_tab’,
    p_source_type => ‘RESPONSE’,
    p_param_type => ‘RESULTSET’,
    p_access_method => ‘OUT’
    );

    COMMIT;
    END;
    /

    This does not work. How can we have function/procedure that output UDT to be shown using ORDS? Also if there are nested UDT will it be different?

    Thanks,
    Rajan

  4. walid kharrat Reply

    is there a way to use resultset to return an array of type. I have a piplined functions and in high concurrency, there’e a win in cpu with static cursors. i can’t do simple select with cursor i have to do pl/sql code to check parameters…

    • thatjeffsmith

      Can you give me any kind of example of what you’re looking to post up and the kind of response you want, as well as some sample pl/sql code you’ll be using?

    • walid kharrat

      Hi Jeff,
      unfortanly i can’t call a pipelined function from pl/sql bloc. So my get webservice can’t be pl/sql one. If i do the check of parameters in pipelined function like this
      CREATE or replace FUNCTION employees_piped (in_deptno NUMBER)
      RETURN employees_ntt PIPELINED AS
      custom_exception EXCEPTION;
      BEGIN
      if(in_deptno > 100) THEN
      RAISE custom_exception;

      — return;
      END IF;
      FOR r IN (SELECT * FROM employees where department_id = in_deptno) LOOP

      PIPE ROW (employees_ot(
      r.employee_id, r.first_name, r.last_name,
      r.email, r.phone_number, r.hire_date,
      r.job_id, r.salary, r.commission_pct,
      r.manager_id, r.department_id));

      END LOOP;
      RETURN;
      Exception
      WHEN custom_exception then
      owa_util.status_line (400, ‘invalid parameters’, false);
      owa_util.http_header_close;
      htp.p(‘invalid parameter’);
      END employees_piped;
      /
      this code raise error 500
      the get service type is sql with select …table(function)…
      so how to do check parameters if i call pipelined function
      if i do with sys_refcursor i can modify the type of the get to pl/sql code, do the check and call a function who return sys_refcursor…
      is there a real gain of using pipelined over then sys_refcursor with relatively small dataset returned by webservices. (static cursor vs dynamic) i have a webservices that can have a 2000 simultanious call .

    • thatjeffsmith

      Sorry, you’ve lost me…I’m not sure what you’re trying to achieve with your service. Start there.

      Also when you say you get a 500..that only means that ORDS has encountered a ORA or PLS error, check your ords output log or turn on debug mode, so you can see what’s going wrong and fix it.

  5. Fantastic blog! Do you have any tips and hints for aspiring writers?
    I’m hoping to start my own site soon but I’m a little lost on everything.
    Would you propose starting with a free platform like WordPress or
    go for a paid option? There are so many options out there
    that I’m completely confused .. Any ideas? Many thanks!

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

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

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

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

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

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

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