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

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.

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.