📋Steps in this guide1/14

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

oracle miscconfigurationintermediate

by OracleDba

19 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, CREATE PROCEDURE 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. 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. Web services from the schema can now be referenced using the following base URLs. 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 => 'hr',
    p_auto_rest_auth      => FALSE
  );
    
  COMMIT;
END;
/

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

Enabling Objects

PL/SQL code is enabled for Auto PL/SQL using the procedure, the same procedure used by the AutoREST functionality for tables and views. 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 procedure with the parameter set to FALSE. - The mapping remains after an object is dropped, so remember to disable an object before dropping it.

Code/Command (click line numbers to comment):

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

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. 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/empty-payload.json", the following "curl" command will run the web service. Notice we passed in an empty payload and we were returned an empty response.

Code/Command (click line numbers to comment):

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

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

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

Minimal Function

Create a basic function with no parameters and enable it. 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/empty-payload.json", the following "curl" command will run the web service. Notice we passed in an empty payload and we were returned a response containing a return value of "null".

Code/Command (click line numbers to comment):

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

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

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

Minimal Package

Create a basic package with a single procedure with no parameters and enable it. 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/empty-payload.json", the following "curl" command will run the web service. 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.

Code/Command (click line numbers to comment):

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

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

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

Basic Parameters

Create a procedure that uses basic parameters and enable it. The information below describes the call we must make to access the PL/SQL object. Notice the payload includes an entry for every mandatory and parameter defined in the procedure. If the payload is placed in a file called "/tmp/basic-payload.json", the following "curl" command will run the web service. The response contains an entry for every and parameter.

Code/Command (click line numbers to comment):

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

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" }

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

REF CURSORs

Create a procedure that passes out a . The example procedure returns a single row if the parameter is specified, or all rows if it is not specified, or if it is specified with a NULL value. 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/empno-payload.json", the following "curl" command will run the web service. The response contains a single entry for the 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.

Code/Command (click line numbers to comment):

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

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

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

$
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). 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/empty-payload.json", the following "curl" command will run the web service. The response contains a single entry for the 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. Using the same command as before results in a 500 internal server error. Both nested tables and varrays result in internal server errors also, even when the are single column collections. Using the same command as before results in a 500 internal server error.

Code/Command (click line numbers to comment):

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

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

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

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

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

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

$
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. 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/empty-payload.json", the following "curl" command will run the web service. We can see that calls to the PL/SQL web toolkit result in 500 internal server errors.

Code/Command (click line numbers to comment):

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

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

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

Procedures Using Implicit Statement Results

Create a procedure that uses implicit statement results and enable it. 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/empty-payload.json", the following "curl" command will run the web service. Notice the procedure has run successfully, but there is no sign of the implicit statement results.

Code/Command (click line numbers to comment):

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

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

$
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

{}
$

Display Enabled Objects

The view displays enabled objects. For more information see: - Auto PL/SQL - Oracle REST Data Services (ORDS) : All Articles - Oracle REST Data Services (ORDS) : Installation on Tomcat - Oracle REST Data Services (ORDS) : Configure Multiple Databases - Oracle REST Data Services (ORDS) : AutoREST - Oracle REST Data Services (ORDS) : Create Basic RESTful Web Services Using PL/SQL - Oracle REST Data Services (ORDS) : Authentication Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!