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

Home » Articles » Misc » Here

Oracle REST Data Services (ORDS) : Presenting PL/SQL Web Toolkit Applications

PL/SQL Web Toolkit applications can be presented using Oracle REST Data Services (ORDS) rather than mod_plsql. This allows people to migrate away from old Oracle HTTP Server (OHS) installations.

Related articles.

Create Test Schemas

The example in this article relies on two schemas. The first is the schema owner, which will hold the PL/SQL Web Toolkit application.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;

--DROP USER schema_owner CASCADE;

CREATE USER schema_owner IDENTIFIED BY schema_owner
  QUOTA UNLIMITED ON users;

GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE TO schema_owner;

Create a test table in the new schema.

CONN schema_owner/schema_owner@pdb1

--DROP TABLE emp PURGE;

CREATE TABLE emp (
  empno    NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;

Create a package to represent our PL/SQL Web Toolkit application.

CREATE OR REPLACE PACKAGE employee_api AS

PROCEDURE display;

END;
/
SHOW ERRORS



CREATE OR REPLACE PACKAGE BODY employee_api AS

-- ----------------------------------------------------------------------------
PROCEDURE display AS
-- ----------------------------------------------------------------------------
  l_more  BOOLEAN;
BEGIN

  l_more := OWA_UTIL.tablePrint('schema_owner.emp', 'BORDER');

EXCEPTION
  WHEN OTHERS THEN
    HTP.print('ERROR: ' || SQLERRM);  
END display;
-- ----------------------------------------------------------------------------

END;
/
SHOW ERRORS

Create a schema which ORDS can use to access the API. Since all the work happens in the schema owner, we use a login trigger to set the CURRENT_SCHEMA setting for the session.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;

--DROP USER app_user CASCADE;

CREATE USER app_user IDENTIFIED BY app_user
  QUOTA UNLIMITED ON users;

GRANT CREATE SESSION TO app_user;


CREATE OR REPLACE TRIGGER app_user.after_logon_trg
AFTER LOGON ON app_user.SCHEMA
BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema=schema_owner';
END;
/

We need to make sure the login user has access to the API, and it will also need direct access to the documents table.

GRANT EXECUTE ON schema_owner.employee_api TO app_user;

ORDS Setup

If it is present, remove the following from the "/u01/ords/conf/ords/defaults.xml" file. It controls the PL/SQL whitelist which limits which procedures can be called. That works fine for APEX, but it's going to stop this example running.

<entry key="security.requestValidationFunction">wwv_flow_epg_include_modules.authorize</entry>

Setup a new database connection, so we can connect to the application user and change the parameters without affecting the main APEX pool. Notice how we use the APP_USER user for the connection details, and we ignore APEX RESTful Services configuration.

cd /u01/ords
$JAVA_HOME/bin/java -jar ords.war setup --database app_pdb1
Enter the name of the database server [localhost.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]:2
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]:
Enter the PL/SQL Gateway database user name [APEX_PUBLIC_USER]:app_user
Enter the database password for app_user:
Confirm password:
Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]:2
Dec 08, 2018 9:26:33 PM
...
...
Additional output removed for brevity...

We need to associate a base URL path with the new pool, so we can run the application.

$JAVA_HOME/bin/java -jar ords.war map-url --type base-path /app app_pdb1

You need to restart ORDS for the changes to be visible. The following shows how you might restart ORDS deployed with Tomcat.

$CATALINA_HOME/bin/shutdown.sh
$CATALINA_HOME/bin/startup.sh

Test It

The test page is available from the following URL.

# Format
http://<server-name>:<port>/ords/app/employee_api.display

# Example
http://localhost:8080/ords/app/employee_api.display

For more information see:

Hope this helps. Regards Tim...

Back to the Top.