📋Steps in this guide1/4

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.

oracle miscconfigurationintermediate

by OracleDba

24 views

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. Create a test table in the new schema. Create a package to represent our PL/SQL Web Toolkit application. 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 setting for the session. We need to make sure the login user has access to the API, and it will also need direct access to the documents table.

Code/Command (click line numbers to comment):

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;

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

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

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. If it is present, blank or remove the 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. We need to restart ORDS for the changes to be visible. The following shows how you might restart ORDS deployed with Tomcat.

Code/Command (click line numbers to comment):

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

# 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

$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. 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 user for the connection details, and we ignore APEX RESTful Services configuration. We need to associate a base URL path with the new pool, so we can run the application. You need to restart ORDS for the changes to be visible. The following shows how you might restart ORDS deployed with Tomcat.

Code/Command (click line numbers to comment):

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

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

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

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

Test It

The test page is available from the following URL. 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...

Code/Command (click line numbers to comment):

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

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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!