8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | 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
- ORDS Setup (ORDS Version 22.1 Onward)
- ORDS Setup (ORDS Versions 3.0 to 21.4)
- Test It
Related articles.
- Oracle REST Data Services (ORDS) : All Articles
- Oracle REST Data Services (ORDS) : Database Authentication
- Oracle REST Data Services (ORDS) : File Upload Download (mod_plsql Style)
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:
- Oracle REST Data Services (ORDS) : All Articles
- Oracle REST Data Services (ORDS) : Database Authentication
- Oracle REST Data Services (ORDS) : File Upload Download (mod_plsql Style)
Hope this helps. Regards Tim...