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

Home » Articles » Misc » Here

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.

Related articles.

Assumptions and Comments

This article assumes the following.

Create a Test Database User

We need a new database user for our testing.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;

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

Create and populate a copy of the EMP table.

CONN testuser2/testuser2@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;

Configure ORDS and Enable Schema

There are a number of steps that are necessary to enable ORDS and the AutoREST functionality.

Installing ORDS configures a default database connection called "apex", available from the base URL "/ords/". We could use this existing database connection, but instead we will create a new database connection. If you already have a connection you are happy to use ignore the following database connection and URL mapping ORDS commands.

$ cd /u01/ords
$ $JAVA_HOME/bin/java -jar ords.war setup --database pdb1
$ $JAVA_HOME/bin/java -jar ords.war setup --database pdb1
Enter the name of the database server [ol7-122.localdomain]:
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:
Enter the database service name [pdb1]:
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:2
Jun 24, 2017 11:59:54 AM
INFO: Updated configurations: pdb1_pu
Jun 24, 2017 11:59:54 AM oracle.dbtools.rt.config.setup.SchemaSetup install
INFO: Oracle REST Data Services schema version 3.0.10.165.06.53 is installed.
$

We create a URL mapping to the new database connection.

$ cd /u01/ords
$ $JAVA_HOME/bin/java -jar ords.war map-url --type base-path /pdb1 pdb1
Jun 24, 2017 12:04:22 PM
INFO: Creating new mapping from: [base-path,/pdb1] to map to: [pdb1,,]
$

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

http://ol7-122.localdomain:8080/ords/pdb1/

Enable REST web services for the test schema. 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 testuser2/testuser2@pdb1

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

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

http://ol7-122.localdomain:8080/ords/pdb1/testuser2/

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       => 'TESTUSER2',
    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.

The individual elements of the URIs are described below.

Some more things to consider include the following.

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 testuser2/testuser2@pdb1

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


BEGIN
  ORDS.enable_object (
    p_enabled      => TRUE,
    p_schema       => 'TESTUSER2',
    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://ol7-122.localdomain:8080/ords/pdb1/testuser2/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://ol7-122.localdomain:8080/ords/pdb1/testuser2/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 testuser2/testuser2@pdb1

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


BEGIN
  ORDS.enable_object (
    p_enabled      => TRUE,
    p_schema       => 'TESTUSER2',
    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://ol7-122.localdomain:8080/ords/pdb1/testuser2/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://ol7-122.localdomain:8080/ords/pdb1/testuser2/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 testuser2/testuser2@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       => 'TESTUSER2',
    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://ol7-122.localdomain:8080/ords/pdb1/testuser2/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://ol7-122.localdomain:8080/ords/pdb1/testuser2/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 testuser2/testuser2@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       => 'TESTUSER2',
    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://ol7-122.localdomain:8080/ords/pdb1/testuser2/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://ol7-122.localdomain:8080/ords/pdb1/testuser2/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 testuser2/testuser2@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       => 'TESTUSER2',
    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://ol7-122.localdomain:8080/ords/pdb1/testuser2/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://ol7-122.localdomain:8080/ords/pdb1/testuser2/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://ol7-122.localdomain:8080/ords/pdb1/testuser2/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 testuser2/testuser2@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       => 'TESTUSER2',
    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://ol7-122.localdomain:8080/ords/pdb1/testuser2/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://ol7-122.localdomain:8080/ords/pdb1/testuser2/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 testuser2/testuser2@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       => 'TESTUSER2',
    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://ol7-122.localdomain:8080/ords/pdb1/testuser2/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 testuser2/testuser2@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       => 'TESTUSER2',
    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://ol7-122.localdomain:8080/ords/pdb1/testuser2/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 testuser2/testuser2@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       => 'TESTUSER2',
    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://ol7-122.localdomain:8080/ords/pdb1/testuser2/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://ol7-122.localdomain:8080/ords/pdb1/testuser2/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 testuser2/testuser2@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       => 'TESTUSER2',
    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://ol7-122.localdomain:8080/ords/pdb1/testuser2/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://ol7-122.localdomain:8080/ords/pdb1/testuser2/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
-------------------- -------------------- -------------------- -------------------- ----------
TESTUSER2	     FUNC1		  func1 	       FUNCTION 	    ENABLED
TESTUSER2	     MY_TEST_PACKAGE	  my_test_package      PACKAGE		    ENABLED
TESTUSER2	     PROC1		  proc1 	       PROCEDURE	    ENABLED
TESTUSER2	     PROC2		  proc2 	       PROCEDURE	    ENABLED
TESTUSER2	     PROC3		  proc3 	       PROCEDURE	    ENABLED
TESTUSER2	     PROC4		  proc4 	       PROCEDURE	    ENABLED
TESTUSER2	     PROC5		  proc5 	       PROCEDURE	    ENABLED

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.