8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | 24ai | 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

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.