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

Oracle REST Data Services (ORDS) : Auto PL/SQL

This article gives an overview of the Auto PL/SQL functionality of Oracle REST Data Services (ORDS), which allows you to easily expose PL/SQL code as RPC over HTTP(S).

Auto PL/SQL is a useful convenience feature. It should not be considered a replacement for defining RESTful web services using PL/SQL, as described here.

Assumptions and Comments
Create a Test Database User
Enable ORDS
Enabling Objects
Examples
Display Enabled Objects

Assumptions and Comments

This article assumes the following.

You using ORDS 3.0.10 or later.
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. I used "curl" and the "Advanced REST client" extension for Chrome.

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, CREATE PROCEDURE 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. We use any unique and legal URL mapping pattern for the schema, so we don't expose the schema name. In this case we use "hr" as the schema alias.

CONN testuser1/testuser1@pdb1

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

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

http://localhost:8080/ords/hr/
https://localhost:8443/ords/hr/

We are now ready to start.

Enabling Objects

PL/SQL code is enabled for Auto PL/SQL using the ORDS.ENABLE_OBJECT procedure, the same procedure used by the AutoREST functionality for tables and views.

BEGIN
  ORDS.enable_object (
    p_enabled      => TRUE, -- Default first { TRUE | FALSE }
    p_schema       => 'TESTUSER1',
    p_object       => 'MY_PROC',
    p_object_type  => 'PROCEDURE', -- Default first { TABLE | VIEW | PROCEDURE | FUNCTION | PACKAGE }
    p_object_alias => 'my_proc_alias'
  );
    
  COMMIT;
END;
/

Enabling a PL/SQL object results in the follow style URIs.

Procedures and Functions : http://localhost:8080/ords/pdb1/<schema-alias>/<object-alias>/
Packaged Procedures and Functions : http://localhost:8080/ords/pdb1/<schema-alias>/<object-alias>/<ROUTINE-NAME>

The individual elements of the URIs are described below.

schema-alias : The alias specified when enabling the schema for ORDS.
object-alias : The alias specified when enabling the PL/SQL object.
ROUTINE-NAME : The procedure or function name. This is automatically generated by ORDS and must be referenced in upper case.
Trailing / : All URIs for procedures and functions end in a trailing "/". There is no trailing "/" on URIs for packaged procedures and functions.

Some more things to consider include the following.

Auto PL/SQL only supports the POST method, so it doesn't enable PL/SQL objects as RESTful web services. Instead it enables them as Remote Procedure Call (RPC) over HTTP(S).
Auto PL/SQL doesn't support overloaded routines. Any overloaded routines in a package will not have a URI generated for them.
All calls require the "Content-Type" header of "application/json".
All calls require a JSON payload, even if that means an empty JSON document when there are no input parameters.
All calls return a JSON response, even if that means an empty JSON document when there are no output parameters.
Function calls return a JSON response containing a "~ret" key with the associated return value.
To disable an object simply make a similar call to the ORDS.ENABLE_OBJECT procedure with the P_ENABLED parameter set to FALSE.
The mapping remains after an object is dropped, so remember to disable an object before dropping it.

Examples

The examples below will give you an idea of what Auto PL/SQL is capable of.

Minimal Procedure

Create a basic procedure with no parameters and enable it.

CONN testuser1/testuser1@pdb1

CREATE OR REPLACE PROCEDURE proc1 AS
BEGIN
  NULL;
END;
/


BEGIN
  ORDS.enable_object (
    p_enabled      => TRUE,
    p_schema       => 'TESTUSER1',
    p_object       => 'PROC1',
    p_object_type  => 'PROCEDURE',
    p_object_alias => 'proc1'
  );
    
  COMMIT;
END;
/

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

URI        : http://localhost:8080/ords/hr/proc1/
Method     : POST
Header     : Content-Type: application/json
Raw Payload: { }

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

$ curl -i -X POST --data-binary @/tmp/empty-payload.json \
       -H "Content-Type: application/json" \
       http://localhost:8080/ords/hr/proc1/
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Content-Type: application/json
Transfer-Encoding: chunked
Date: Sat, 24 Jun 2017 12:58:50 GMT

{}
$

Notice we passed in an empty payload and we were returned an empty response.

Minimal Function

Create a basic function with no parameters and enable it.

CONN testuser1/testuser1@pdb1

CREATE OR REPLACE FUNCTION func1 RETURN NUMBER AS
BEGIN
  RETURN NULL;
END;
/


BEGIN
  ORDS.enable_object (
    p_enabled      => TRUE,
    p_schema       => 'TESTUSER1',
    p_object       => 'FUNC1',
    p_object_type  => 'FUNCTION',
    p_object_alias => 'func1'
  );
    
  COMMIT;
END;
/

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

URI        : http://localhost:8080/ords/hr/func1/
Method     : POST
Header     : Content-Type: application/json
Raw Payload: { }

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

$ curl -i -X POST --data-binary @/tmp/empty-payload.json \
       -H "Content-Type: application/json" \
       http://localhost:8080/ords/hr/func1/
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Content-Type: application/json
Transfer-Encoding: chunked
Date: Sat, 24 Jun 2017 13:04:46 GMT

{"~ret":null}
$

Notice we passed in an empty payload and we were returned a response containing a return value of "null".

Minimal Package

Create a basic package with a single procedure with no parameters and enable it.

CONN testuser1/testuser1@pdb1

CREATE OR REPLACE PACKAGE my_test_package AS

  PROCEDURE proc1;
                   
END my_test_package;
/


CREATE OR REPLACE PACKAGE BODY my_test_package AS

  PROCEDURE proc1 AS
  BEGIN
    NULL;
  END proc1;

END my_test_package;
/


BEGIN
  ORDS.enable_object (
    p_enabled      => TRUE,
    p_schema       => 'TESTUSER1',
    p_object       => 'MY_TEST_PACKAGE',
    p_object_type  => 'PACKAGE',
    p_object_alias => 'my_test_package'
  );
    
  COMMIT;
END;
/

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

URI        : http://localhost:8080/ords/hr/my_test_package/PROC1
Method     : POST
Header     : Content-Type: application/json
Raw Payload: { }

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

$ curl -i -X POST --data-binary @/tmp/empty-payload.json \
       -H "Content-Type: application/json" \
       http://localhost:8080/ords/hr/my_test_package/PROC1
TTP/1.1 200 OK
Server: Apache-Coyote/1.1
Content-Type: application/json
Transfer-Encoding: chunked
Date: Sat, 24 Jun 2017 13:11:38 GMT

{}
$

With the exception of the URI, the packaged procedure reacts the same as the standalone procedure. This would be true if we compared a package function and a standalone function also.

Basic Parameters

Create a procedure that uses basic parameters and enable it.

CONN testuser1/testuser1@pdb1

CREATE OR REPLACE PROCEDURE proc2 (p_in_number    IN     NUMBER,
                                   p_in_text      IN     VARCHAR2,
                                   p_in_date      IN     DATE,
                                   p_inout_number IN OUT NUMBER,
                                   p_inout_text   IN OUT VARCHAR2,
                                   p_inout_date   IN OUT DATE,
                                   p_out_number   OUT    NUMBER,
                                   p_out_text     OUT    VARCHAR2,
                                   p_out_date     OUT    DATE)
AS
BEGIN
  p_inout_number := p_in_number + p_inout_number;
  p_inout_text   := p_in_text || ' + ' || p_inout_text;
  p_inout_date   := p_inout_date + 1;
  p_out_number   := p_in_number + p_inout_number;
  p_out_text     := p_in_text || ' + ' || p_inout_text;
  p_out_date     := p_in_date + 2;
END;
/


BEGIN
  ORDS.enable_object (
    p_enabled      => TRUE,
    p_schema       => 'TESTUSER1',
    p_object       => 'PROC2',
    p_object_type  => 'PROCEDURE',
    p_object_alias => 'proc2'
  );
    
  COMMIT;
END;
/

The information below describes the call we must make to access the PL/SQL object. Notice the payload includes an entry for every mandatory IN and IN OUT parameter defined in the procedure.

URI        : http://localhost:8080/ords/hr/proc2/
Method     : POST
Header     : Content-Type: application/json
Raw Payload: { "p_in_number": 1000, "p_in_text": "TEXT1", "p_in_date": "2017-06-24T08:00:00Z", "p_inout_number": 2000, "p_inout_text": "TEXT2", "p_inout_date": "2017-06-24T09:00:00Z" }

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

$ curl -i -X POST --data-binary @/tmp/basic-payload.json \
       -H "Content-Type: application/json" \
       http://localhost:8080/ords/hr/proc2/
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Content-Type: application/json
Transfer-Encoding: chunked
Date: Sat, 24 Jun 2017 14:03:47 GMT

{"p_inout_text":"TEXT1 + TEXT2","p_out_date":"2017-06-26T09:00:00Z","p_inout_number":3000,"p_out_number":4000,"p_out_text":"TEXT1 + TEXT1 + TEXT2","p_inout_date":"2017-06-25T10:00:00Z"}
$

The response contains an entry for every IN OUT and OUT parameter.

REF CURSORs

Create a procedure that passes out a REF CURSOR. The example procedure returns a single row if the P_EMPNO parameter is specified, or all rows if it is not specified, or if it is specified with a NULL value.

CONN testuser1/testuser1@pdb1

CREATE OR REPLACE PROCEDURE proc3 (p_empno  IN   emp.empno%TYPE DEFAULT NULL,
                                   p_rows   OUT  SYS_REFCURSOR)
AS
BEGIN
  OPEN p_rows FOR
    SELECT *
    FROM   emp
    WHERE  empno = NVL(p_empno, empno);
END;
/


BEGIN
  ORDS.enable_object (
    p_enabled      => TRUE,
    p_schema       => 'TESTUSER1',
    p_object       => 'PROC3',
    p_object_type  => 'PROCEDURE',
    p_object_alias => 'proc3'
  );
    
  COMMIT;
END;
/

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

URI        : http://localhost:8080/ords/hr/proc3/
Method     : POST
Header     : Content-Type: application/json
Raw Payload: { "p_empno": 7566 }

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

$ curl -i -X POST --data-binary @/tmp/empno-payload.json \
       -H "Content-Type: application/json" \
       http://localhost:8080/ords/hr/proc3/
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Content-Type: application/json
Transfer-Encoding: chunked
Date: Sat, 24 Jun 2017 15:00:10 GMT

{"p_rows":[{"empno":7566,"ename":"JONES","job":"MANAGER","mgr":7839,"hiredate":"1981-04-01T23:00:00Z","sal":2975,"comm":null,"deptno":20}]}
$

The response contains a single entry for the P_ROWS out parameter. The associated value is a JSON array containing a single JSON object representing the required row.

If the call is repeated with an empty payload, "/tmp/empty-payload.json", the JSON array contains all the rows.

$ curl -i -X POST --data-binary @/tmp/empty-payload.json \
       -H "Content-Type: application/json" \
       http://localhost:8080/ords/hr/proc3/
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Content-Type: application/json
Transfer-Encoding: chunked
Date: Sat, 24 Jun 2017 15:08:22 GMT

{"p_rows":[
{"empno":7369,"ename":"SMITH","job":"CLERK","mgr":7902,"hiredate":"1980-12-17T00:00:00Z","sal":800,"comm":null,"deptno":20},
{"empno":7499,"ename":"ALLEN","job":"SALESMAN","mgr":7698,"hiredate":"1981-02-20T00:00:00Z","sal":1600,"comm":300,"deptno":30},
{"empno":7521,"ename":"WARD","job":"SALESMAN","mgr":7698,"hiredate":"1981-02-22T00:00:00Z","sal":1250,"comm":500,"deptno":30},
{"empno":7566,"ename":"JONES","job":"MANAGER","mgr":7839,"hiredate":"1981-04-01T23:00:00Z","sal":2975,"comm":null,"deptno":20},
{"empno":7654,"ename":"MARTIN","job":"SALESMAN","mgr":7698,"hiredate":"1981-09-27T23:00:00Z","sal":1250,"comm":1400,"deptno":30},
{"empno":7698,"ename":"BLAKE","job":"MANAGER","mgr":7839,"hiredate":"1981-04-30T23:00:00Z","sal":2850,"comm":null,"deptno":30},
{"empno":7782,"ename":"CLARK","job":"MANAGER","mgr":7839,"hiredate":"1981-06-08T23:00:00Z","sal":2450,"comm":null,"deptno":10},
{"empno":7788,"ename":"SCOTT","job":"ANALYST","mgr":7566,"hiredate":"1987-04-18T23:00:00Z","sal":3000,"comm":null,"deptno":20},
{"empno":7839,"ename":"KING","job":"PRESIDENT","mgr":null,"hiredate":"1981-11-17T00:00:00Z","sal":5000,"comm":null,"deptno":10},
{"empno":7844,"ename":"TURNER","job":"SALESMAN","mgr":7698,"hiredate":"1981-09-07T23:00:00Z","sal":1500,"comm":0,"deptno":30},
{"empno":7876,"ename":"ADAMS","job":"CLERK","mgr":7788,"hiredate":"1987-05-22T23:00:00Z","sal":1100,"comm":null,"deptno":20},
{"empno":7900,"ename":"JAMES","job":"CLERK","mgr":7698,"hiredate":"1981-12-03T00:00:00Z","sal":950,"comm":null,"deptno":30},
{"empno":7902,"ename":"FORD","job":"ANALYST","mgr":7566,"hiredate":"1981-12-03T00:00:00Z","sal":3000,"comm":null,"deptno":20},
{"empno":7934,"ename":"MILLER","job":"CLERK","mgr":7782,"hiredate":"1982-01-23T00:00:00Z","sal":1300,"comm":null,"deptno":10}
]}
$

Collections

Create a procedure that passes out a simple associative array (index-by-table).

CONN testuser1/testuser1@pdb1

CREATE OR REPLACE PACKAGE my_test_package AS

  TYPE t_tab IS TABLE OF VARCHAR2(10)
    INDEX BY BINARY_INTEGER;

  PROCEDURE proc1 (p_rows  OUT  t_tab);
                   
END my_test_package;
/


CREATE OR REPLACE PACKAGE BODY my_test_package AS

  PROCEDURE proc1 (p_rows  OUT  t_tab) AS
  BEGIN
    p_rows(1) := 'ONE';
    p_rows(2) := 'TWO';
    p_rows(3) := 'THREE';
  END proc1;

END my_test_package;
/


BEGIN
  ORDS.enable_object (
    p_enabled      => TRUE,
    p_schema       => 'TESTUSER1',
    p_object       => 'MY_TEST_PACKAGE',
    p_object_type  => 'PACKAGE',
    p_object_alias => 'my_test_package'
  );
    
  COMMIT;
END;
/

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

URI        : http://localhost:8080/ords/hr/my_test_package/PROC1
Method     : POST
Header     : Content-Type: application/json
Raw Payload: { }

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

$ curl -i -X POST --data-binary @/tmp/empty-payload.json \
       -H "Content-Type: application/json" \
       http://localhost:8080/ords/hr/my_test_package/PROC1
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Content-Type: application/json
Transfer-Encoding: chunked
Date: Sat, 24 Jun 2017 16:57:43 GMT

{"p_rows":["ONE","TWO","THREE"]}
$

The response contains a single entry for the P_ROWS out parameter. The associated value is a JSON array containing the values of the associative array.

An attempt to use a more complicated associative array, such as a table of records, results in a 500 internal server error. Create a procedure that passes out an associative array (index-by-table) of records.

CONN testuser1/testuser1@pdb1

CREATE OR REPLACE PACKAGE my_test_package AS

  TYPE t_row IS RECORD (
    id           NUMBER,
    description  VARCHAR2(50)
  );

  TYPE t_tab IS TABLE OF t_row
    INDEX BY BINARY_INTEGER;

  PROCEDURE proc1 (p_rows  OUT  t_tab);
                   
END my_test_package;
/


CREATE OR REPLACE PACKAGE BODY my_test_package AS

  PROCEDURE proc1 (p_rows  OUT  t_tab) AS
  BEGIN
    p_rows(1).id          := 1;
    p_rows(1).description := 'ONE';
    p_rows(2).id          := 2;
    p_rows(2).description := 'TWO';
    p_rows(3).id          := 3;
    p_rows(3).description := 'THREE';
  END proc1;

END my_test_package;
/


BEGIN
  ORDS.enable_object (
    p_enabled      => TRUE,
    p_schema       => 'TESTUSER1',
    p_object       => 'MY_TEST_PACKAGE',
    p_object_type  => 'PACKAGE',
    p_object_alias => 'my_test_package'
  );
    
  COMMIT;
END;
/

Using the same command as before results in a 500 internal server error.

$ curl -i -X POST --data-binary @/tmp/empty-payload.json \
       -H "Content-Type: application/json" \
       http://localhost:8080/ords/hr/my_test_package/PROC1
HTTP/1.1 500 Internal Server Error
Server: Apache-Coyote/1.1
Content-Type: text/html
Content-Length: 11408
Date: Sat, 24 Jun 2017 17:11:30 GMT
Connection: close

... HTML page content removed ...
$

Both nested tables and varrays result in internal server errors also, even when the are single column collections.

CONN testuser1/testuser1@pdb1

CREATE OR REPLACE PACKAGE my_test_package AS

  TYPE t_tab IS TABLE OF VARCHAR2(10);
  --TYPE t_tab IS VARRAY(3) OF VARCHAR2(10);

  PROCEDURE proc1 (p_rows  OUT  t_tab);
                   
END my_test_package;
/


CREATE OR REPLACE PACKAGE BODY my_test_package AS

  PROCEDURE proc1 (p_rows  OUT  t_tab) AS
  BEGIN
    p_rows.extend;
    p_rows(p_rows.last) := 'ONE';
    p_rows.extend;
    p_rows(p_rows.last) := 'TWO';
    p_rows.extend;
    p_rows(p_rows.last) := 'THREE';
  END proc1;

END my_test_package;
/


BEGIN
  ORDS.enable_object (
    p_enabled      => TRUE,
    p_schema       => 'TESTUSER1',
    p_object       => 'MY_TEST_PACKAGE',
    p_object_type  => 'PACKAGE',
    p_object_alias => 'my_test_package'
  );
    
  COMMIT;
END;
/

Using the same command as before results in a 500 internal server error.

$ curl -i -X POST --data-binary @/tmp/empty-payload.json \
       -H "Content-Type: application/json" \
       http://localhost:8080/ords/hr/my_test_package/PROC1
HTTP/1.1 500 Internal Server Error
Server: Apache-Coyote/1.1
Content-Type: text/html
Content-Length: 11408
Date: Sat, 24 Jun 2017 17:22:05 GMT
Connection: close

... HTML page content removed ...
$

Procedures Using the PL/SQL Web Toolkit

Create a procedure that uses the PL/SQL web toolkit and enable it.

CONN testuser1/testuser1@pdb1

CREATE OR REPLACE PROCEDURE proc4 AS
BEGIN
  HTP.print('I wonder if this will work.');
END;
/


BEGIN
  ORDS.enable_object (
    p_enabled      => TRUE,
    p_schema       => 'TESTUSER1',
    p_object       => 'PROC4',
    p_object_type  => 'PROCEDURE',
    p_object_alias => 'proc4'
  );
    
  COMMIT;
END;
/

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

URI        : http://localhost:8080/ords/hr/proc4/
Method     : POST
Header     : Content-Type: application/json
Raw Payload: { }

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

$ curl -i -X POST --data-binary @/tmp/empty-payload.json \
       -H "Content-Type: application/json" \
       http://localhost:8080/ords/hr/proc4/
HTTP/1.1 500 Internal Server Error
Server: Apache-Coyote/1.1
Content-Type: text/html
Content-Length: 11408
Date: Sat, 24 Jun 2017 17:33:24 GMT
Connection: close

... HTML page content removed ...
$

We can see that calls to the PL/SQL web toolkit result in 500 internal server errors.

Procedures Using Implicit Statement Results

Create a procedure that uses implicit statement results and enable it.

CONN testuser1/testuser1@pdb1

CREATE OR REPLACE PROCEDURE proc5 AS
  l_cursor  SYS_REFCURSOR;
BEGIN
  OPEN l_cursor FOR
    SELECT *
    FROM   emp;

  DBMS_SQL.RETURN_RESULT(l_cursor);
END;
/


BEGIN
  ORDS.enable_object (
    p_enabled      => TRUE,
    p_schema       => 'TESTUSER1',
    p_object       => 'PROC5',
    p_object_type  => 'PROCEDURE',
    p_object_alias => 'proc5'
  );
    
  COMMIT;
END;
/

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

URI        : http://localhost:8080/ords/hr/proc5/
Method     : POST
Header     : Content-Type: application/json
Raw Payload: { }

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

$ curl -i -X POST --data-binary @/tmp/empty-payload.json \
       -H "Content-Type: application/json" \
       http://localhost:8080/ords/hr/proc5/
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Content-Type: application/json
Transfer-Encoding: chunked
Date: Sat, 24 Jun 2017 17:41:38 GMT

{}
$

Notice the procedure has run successfully, but there is no sign of the implicit statement results.

Display Enabled Objects

The USER_ORDS_ENABLED_OBJECTS view displays enabled objects.

SET LINESIZE 200
COLUMN parsing_schema FORMAT A20
COLUMN parsing_object FORMAT A20
COLUMN object_alias FORMAT A20
COLUMN type FORMAT A20
COLUMN status FORMAT A10

SELECT parsing_schema,
       parsing_object,
       object_alias,
       type,
       status
FROM   user_ords_enabled_objects
ORDER BY 1, 2;

PARSING_SCHEMA       PARSING_OBJECT       OBJECT_ALIAS         TYPE                 STATUS
-------------------- -------------------- -------------------- -------------------- ----------
TESTUSER1            FUNC1                func1                FUNCTION             ENABLED
TESTUSER1            MY_TEST_PACKAGE      my_test_package      PACKAGE              ENABLED
TESTUSER1            PROC1                proc1                PROCEDURE            ENABLED
TESTUSER1            PROC2                proc2                PROCEDURE            ENABLED
TESTUSER1            PROC3                proc3                PROCEDURE            ENABLED
TESTUSER1            PROC4                proc4                PROCEDURE            ENABLED
TESTUSER1            PROC5                proc5                PROCEDURE            ENABLED

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.