8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | 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 (ORDS Version 22.1 Onward)

There is a more detailed explanation of this setup here.

Setup a new database connection.

export ORDS_HOME=/u01/ords
export ORDS_CONFIG=/u01/config/ords
export PATH=${ORDS_HOME}/bin:${PATH}
export ORDS_LOGS=${ORDS_CONFIG}/logs

export DB_PORT=1521
export DB_SERVICE=pdb1
export SYSDBA_USER=SYS
export SYSDBA_PASSWORD=SysPassword1
export ORDS_PASSWORD=OraPassword1

ords --config ${ORDS_CONFIG} install \
     --log-folder ${ORDS_LOGS} \
     --db-pool app-pdb1 \
     --admin-user ${SYSDBA_USER} \
     --db-hostname ${HOSTNAME} \
     --db-port ${DB_PORT} \
     --db-servicename ${DB_SERVICE} \
     --gateway-mode proxied \
     --gateway-user APP_USER \
     --proxy-user \
     --password-stdin <<EOF
${SYSDBA_PASSWORD}
${ORDS_PASSWORD}
EOF

If it is present, blank or remove the security.requestValidationFunction attribute. It controls the PL/SQL allowlist which limits which procedures can be called. That works fine for APEX, but it's going to stop this example running.

# Set to blank value.
ords --config ${ORDS_CONFIG} config --db-pool app-pdb1 set security.requestValidationFunction ""

# Remove the setting.
ords --config ${ORDS_CONFIG} config --db-pool app-pdb1 delete security.requestValidationFunction

We 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

ORDS Setup (ORDS Versions 3.0 to 21.4)

If it is present, remove the following from the "/u01/ords/conf/ords/defaults.xml" file. It controls the PL/SQL allowlist 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-pdb1 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-pdb1/employee_api.display

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

For more information see:

Hope this helps. Regards Tim...

Back to the Top.