📋Steps in this guide1/5

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

oracle miscconfigurationintermediate

by OracleDba

13 views

Assumptions and Comments

This article assumes the following.

Create a Test Database User

We need a new database user for our testing. Create and populate a copy of the EMP table.

Code/Command (click line numbers to comment):

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;

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. Web services from the schema can now be referenced using the following base URL. We are now ready to start.

Code/Command (click line numbers to comment):

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

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

Basic Output Parameters

The following procedure accepts an employee number and returns it along with the associated employee name and their department number. The following code defines a post service that uses the stored procedure. It also defines ORDS parameters for the and parameters, so ORDS can process the outputs and convert them to JSON. The information below describes the call we must make to access the PL/SQL object. If the payload is placed in a file called "/tmp/out-param-payload.json", the following "curl" command will run the web service.

Code/Command (click line numbers to comment):

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

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

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

$
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. The following code defines a post service that uses the stored procedure. It also defines an ORDS parameter for the parameter, so ORDS can process the result set and convert it to JSON. The information below describes the call we must make to access the PL/SQL object. If the payload is placed in a file called "/tmp/out-param-payload.json", the following "curl" command will run the web service. For more information see: - ORDS.DEFINE_PARAMETER - Oracle REST Data Services (ORDS) : All Articles - Oracle REST Data Services (ORDS) : Auto PL/SQL Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

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

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

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

$
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}]}
$

Comments (0)

Please to add comments

No comments yet. Be the first to comment!