8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Oracle REST Data Services (ORDS) : Remote Procedure Call (RPC) and Output Parameters

This article demonstrates how to handle output parameters from procedures when using Oracle REST Data Services (ORDS) for remote procedure calls (RPC).

Assumptions and Comments
Create a Test Database User
Enable ORDS
Basic Output Parameters
REF CURSORS

Assumptions and Comments

This article assumes the following.

You already have a functioning installation of ORDS.
The paths for the ORDS configuration match those from the ORDS installation article listed above.
You have an Oracle database available. In this article I will be using a 12c database, but it works just the same with an 11g database.
You have a way to call the web services. Some GET web services can be called from a browser, but the other methods require some coding, or a REST client. I used "curl" and the "Advanced REST client" extension for Chrome.
You understand this example is not a RESTful web service. It's Remote Procedure Call (RPC) over HTTP. There is noting wrong with that. Just don't call it REST.

Create a Test Database User

We need a new database user for our testing.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;

DROP USER testuser1 CASCADE;
CREATE USER testuser1 IDENTIFIED BY testuser1
  DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
  
GRANT CREATE SESSION, CREATE TABLE TO testuser1;

Create and populate a copy of the EMP table.

CONN testuser1/testuser1@pdb1

CREATE TABLE EMP (
  EMPNO NUMBER(4,0), 
  ENAME VARCHAR2(10 BYTE), 
  JOB VARCHAR2(9 BYTE), 
  MGR NUMBER(4,0), 
  HIREDATE DATE, 
  SAL NUMBER(7,2), 
  COMM NUMBER(7,2), 
  DEPTNO NUMBER(2,0), 
  CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)
  );
  
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,to_date('01-MAY-81','DD-MON-RR'),2850,null,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,to_date('09-JUN-81','DD-MON-RR'),2450,null,10);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,to_date('19-APR-87','DD-MON-RR'),3000,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,to_date('17-NOV-81','DD-MON-RR'),5000,null,10);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,to_date('08-SEP-81','DD-MON-RR'),1500,0,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,to_date('23-MAY-87','DD-MON-RR'),1100,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,to_date('03-DEC-81','DD-MON-RR'),950,null,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,to_date('03-DEC-81','DD-MON-RR'),3000,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_date('23-JAN-82','DD-MON-RR'),1300,null,10);
commit;

Enable ORDS

Enable REST web services for the test schema itself. We could use any unique and legal URL mapping pattern for the schema, so it is not necessary to expose the schema name as we have done here.

CONN testuser1/testuser1@pdb1

BEGIN
  ORDS.enable_schema(
    p_enabled             => TRUE,
    p_schema              => 'TESTUSER1',
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => 'testuser1',
    p_auto_rest_auth      => FALSE
  );
    
  COMMIT;
END;
/

Web services from the schema can now be referenced using the following base URL.

http://ol7-121.localdomain:8080/ords/pdb1/testuser1/

We are now ready to start.

Basic Output Parameters

The following procedure accepts an employee number and returns it along with the associated employee name and their department number.

CONN testuser1/testuser1@pdb1

CREATE OR REPLACE PROCEDURE get_employee_details (
  p_empno   IN OUT  emp.empno%TYPE,
  p_ename      OUT  emp.ename%TYPE,
  p_deptno     OUT  emp.deptno%TYPE
)
AS
BEGIN
  SELECT ename, deptno
  INTO   p_ename, p_deptno
  FROM   emp
  WHERE  empno = p_empno;
EXCEPTION
  WHEN OTHERS THEN
    p_ename  := NULL;
    p_deptno := NULL;
END;
/

The following code defines a post service that uses the stored procedure. It also defines ORDS parameters for the IN OUT and OUT parameters, so ORDS can process the outputs and convert them to JSON.

BEGIN
  ORDS.define_module(
    p_module_name    => 'testmodule6a',
    p_base_path      => 'testmodule6a/',
    p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'testmodule6a',
   p_pattern        => 'get-employee-details');

  ORDS.define_handler(
    p_module_name    => 'testmodule6a',
    p_pattern        => 'get-employee-details',
    p_method         => 'POST',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN
                           get_employee_details(
                             p_empno    => :empno,
                             p_ename    => :ename,
                             p_deptno   => :deptno);
                         END;',
    p_items_per_page => 0);

  ORDS.define_parameter(
    p_module_name        => 'testmodule6a',
    p_pattern            => 'get-employee-details',
    p_method             => 'POST',
    p_name               => 'employee_number',
    p_bind_variable_name => 'empno',
    p_source_type        => 'RESPONSE',
    p_param_type         => 'INT',
    p_access_method      => 'INOUT'
  );

  ORDS.define_parameter(
    p_module_name        => 'testmodule6a',
    p_pattern            => 'get-employee-details',
    p_method             => 'POST',
    p_name               => 'employee_name',
    p_bind_variable_name => 'ename',
    p_source_type        => 'RESPONSE',
    p_param_type         => 'STRING',
    p_access_method      => 'OUT'
  );

  ORDS.define_parameter(
    p_module_name        => 'testmodule6a',
    p_pattern            => 'get-employee-details',
    p_method             => 'POST',
    p_name               => 'department_number',
    p_bind_variable_name => 'deptno',
    p_source_type        => 'RESPONSE',
    p_param_type         => 'INT',
    p_access_method      => 'OUT'
  );

  COMMIT;
END;
/

The information below describes the call we must make to access the PL/SQL object.

URL        : http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule6a/get-employee-details
Method     : POST
Header     : Content-Type: application/json
Raw Payload: { "empno": 7499 }

If the payload is placed in a file called "/tmp/out-param-payload.json", the following "curl" command will run the web service.

$ curl -i -X POST --data-binary @/tmp/out-param-payload.json \
       -H "Content-Type: application/json" \
       http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule6a/get-employee-details
HTTP/1.1 200
Content-Type: application/json
Transfer-Encoding: chunked
Date: Wed, 02 Aug 2017 22:28:43 GMT

{"employee_name":"ALLEN","department_number":30,"employee_number":7499}
$

REF CURSORS

In addition to basic output parameters, ORDS can also deal with REF CURSORS out parameters. The following procedure accepts an employee number and returns a result set containing the details of the specified employee.

CONN testuser1/testuser1@pdb1

CREATE OR REPLACE PROCEDURE get_employee_details (
  p_empno     IN  emp.empno%TYPE,
  p_employee  OUT SYS_REFCURSOR
)
AS
BEGIN
  OPEN p_employee FOR
    SELECT *
    FROM   emp
    WHERE  empno = p_empno;
EXCEPTION
  WHEN OTHERS THEN
    p_employee := NULL;
END;
/

The following code defines a post service that uses the stored procedure. It also defines an ORDS parameter for the OUT parameter, so ORDS can process the result set and convert it to JSON.

BEGIN
  ORDS.define_module(
    p_module_name    => 'testmodule6b',
    p_base_path      => 'testmodule6b/',
    p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'testmodule6b',
   p_pattern        => 'get-employee-details');

  ORDS.define_handler(
    p_module_name    => 'testmodule6b',
    p_pattern        => 'get-employee-details',
    p_method         => 'POST',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN
                           get_employee_details(
                             p_empno    => :empno,
                             p_employee => :employee);
                         END;',
    p_items_per_page => 0);

  ORDS.define_parameter(
    p_module_name        => 'testmodule6b',
    p_pattern            => 'get-employee-details',
    p_method             => 'POST',
    p_name               => 'employee',
    p_bind_variable_name => 'employee',
    p_source_type        => 'RESPONSE',
    p_param_type         => 'RESULTSET',
    p_access_method      => 'OUT'
  );

  COMMIT;
END;
/

The information below describes the call we must make to access the PL/SQL object.

URL        : http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule6b/get-employee-details
Method     : POST
Header     : Content-Type: application/json
Raw Payload: { "empno": 7499 }

If the payload is placed in a file called "/tmp/out-param-payload.json", the following "curl" command will run the web service.

$ curl -i -X POST --data-binary @/tmp/out-param-payload.json \
       -H "Content-Type: application/json" \
       http://ol7-122.localdomain:8080/ords/pdb1/testuser1/testmodule6b/get-employee-details
HTTP/1.1 200
Content-Type: application/json
Transfer-Encoding: chunked
Date: Wed, 02 Aug 2017 22:37:51 GMT

{"employee":[{"empno":7499,"ename":"ALLEN","job":"SALESMAN","mgr":7698,"hiredate":"1981-02-20T00:00:00Z","sal":1600,"comm":300,"deptno":30}]}
$

For more information see:

Hope this helps. Regards Tim...

Back to the Top.