Part3: Inventory webpage Setup (low code) using Apex

Objective: Setup Apex for an invetory application development (low code project)

In continuation to the previous blog for inventory webpage setup.
We completed APEX setup in the previous blog.

Now let us setup the webserver... which is ORDS.

Below is the architecture of the Oracle APEX

url: https://docs.oracle.com/en/database/oracle/apex/22.2/htmig/about-apex-architecture.html#GUID-2FF90AE3-87AE-42EB-90C7-A79A0A6AA997

Step 1) Download ORDS binary from

https://www.oracle.com/database/sqldeveloper/technologies/db-actions/download/

Exact link as of this blog was written: 

https://download.oracle.com/otn_software/java/ords/ords-23.1.1.109.1003.zip

Step 2) Copy the downloaded ORDS to the VM
Step 3) Unzip in a path of our choce..
Step 4) View unziped file. After unzip this is how it looked

[oracle@vcentos79-oracle-sa1 ORDSBIN]$ pwd
/u01/app/oracle/ORDSBIN
[oracle@vcentos79-oracle-sa1 ORDSBIN]$ ls -altr
total 187004
drwxr-xr-x.  4 oracle oinstall       38 Mar 27 08:34 scripts
drwxr-xr-x.  3 oracle oinstall       17 Mar 27 08:34 lib
drwxr-xr-x.  2 oracle oinstall       28 Mar 27 08:34 icons
-rw-r--r--.  1 oracle oinstall     5744 Mar 27 08:34 FUTC.txt
drwxr-xr-x.  7 oracle oinstall       93 Mar 27 08:34 examples
-rw-r--r--.  1 oracle oinstall    72069 Apr 19 08:33 index.html
-rw-r--r--.  1 oracle oinstall 94650536 Apr 19 10:16 ords.war
-rw-r--r--.  1 oracle oinstall     5744 Apr 19 10:16 license.txt
drwxr-xr-x.  3 oracle oinstall       86 Apr 19 10:16 linux-support
drwxr-xr-x.  3 oracle oinstall       21 Apr 19 10:16 docs
drwxr-xr-x.  2 oracle oinstall       34 Apr 19 10:16 bin
drwxr-xr-x. 10 oracle oinstall      127 Apr 22 18:46 ..
-rwxr-xr-x.  1 oracle oinstall 96746911 Apr 22 18:47 ords-23.1.1.109.1003.zip
drwxr-xr-x.  9 oracle oinstall      206 Apr 22 18:47 .
[oracle@vcentos79-oracle-sa1 ORDSBIN]$

Step 5) Note down the location of the directory where we unzipped ORDS folder, there is a subdirectory called bin in this unzip folder; which we need it later.
Step 6) Update the .bash_profile with the ORDS_UNZIP_FOLDER/bin; in my case this is how it looked.

[oracle@vcentos79-oracle-sa1 ~]$ diff .bash_profile_BKP_22Apr23 .bash_profile
10c10
< PATH=$PATH:$HOME/.local/bin:$HOME/bin
---
> PATH=$PATH:$HOME/.local/bin:$HOME/bin:/u01/app/oracle/ORDSBIN/bin:/u01/app/oracle/ORDSBIN <<< the ORDS unzip bin and ORDS unzip directory are appended.
[oracle@vcentos79-oracle-sa1 ~]$

Step 7) Logout and login back as oracle user to pick up the bash_profile change

[root@vcentos79-oracle-sa1 ~]# su - oracle
Last login: Sat Apr 22 17:06:16 BST 2023 on pts/1

[oracle@vcentos79-oracle-sa1 ~]$ echo $PATH
/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:/u01/app/oracle/ORDSBIN/bin:/u01/app/oracle/ORDSBIN

[oracle@vcentos79-oracle-sa1 ~]$

Step 8) Download the JAVA version 11

https://www.oracle.com/uk/java/technologies/javase/jdk11-archive-downloads.html

Step 9) Unzip JAVA binary in any of the directory of your choice
In my case  /u01/app/oracle/JAVABIN

Step 10) Update the .bash_profile to include the java path...

PATH=$PATH:$HOME/.local/bin:$HOME/bin:/u01/app/oracle/ORDSBIN/bin:/u01/app/oracle/ORDSBIN:/u01/app/oracle/JAVABIN/jdk-11.0.18:/u01/app/oracle/JAVABIN/jdk-11.0.18/bin

Step 11) Let us create a tablespace for ORDS setup in the pluggable database INVENTORYPDB

create tablespace TBSPC_ORDS_METADATA datafile size 10m autoextend on next 10m;
create tablespace TBSPC_ORDS_PUBLICUSR datafile size 10m autoextend on next 10m;

SQL> create tablespace TBSPC_ORDS_METADATA datafile size 10m autoextend on next 10m
  2  ;
Tablespace created.

SQL> create tablespace TBSPC_ORDS_PUBLICUSR datafile size 10m autoextend on next 10m;
Tablespace created.

SQL>
  1* select tablespace_name,file_name,bytes/1024/1024,autoextensible from dba_data_files order by tablespace_name
SQL> /
TABLESPACE_NAME                FILE_NAME                                                                                     BYTES/1024/1024 AUT
------------------------------ ---------------------------------------------------------------------------------------------------- --------------- ---
SYSAUX                         /oradata/ORA19C/F9B6A2A48B903161E05305C2A8C073B4/datafile/o1_mf_sysaux_l40k2on9_.dbf      350 YES
SYSTEM                         /oradata/ORA19C/F9B6A2A48B903161E05305C2A8C073B4/datafile/o1_mf_system_l40k2on0_.dbf      400 YES
TBSPC_APEX_APEXUSR             /oradata/ORA19C/F9B6A2A48B903161E05305C2A8C073B4/datafile/o1_mf_tbspc_ap_l480xq7y_.dbf    210 YES
TBSPC_APEX_FILEUSR             /oradata/ORA19C/F9B6A2A48B903161E05305C2A8C073B4/datafile/o1_mf_tbspc_ap_l480xwwm_.dbf     10 YES
TBSPC_ORDS_METADATA            /oradata/ORA19C/F9B6A2A48B903161E05305C2A8C073B4/datafile/o1_mf_tbspc_or_l48d11cx_.dbf     10 YES << ORDS_METDATA tablespace
TBSPC_ORDS_PUBLICUSR           /oradata/ORA19C/F9B6A2A48B903161E05305C2A8C073B4/datafile/o1_mf_tbspc_or_l48d1jp9_.dbf     10 YES << ORDS_PUBLIC_USER tablespace
UNDOTBS1                       /oradata/ORA19C/F9B6A2A48B903161E05305C2A8C073B4/datafile/o1_mf_undotbs1_l40k2onb_.dbf    170 YES
7 rows selected.
SQL>

Step 12) Create the ords config folder as per oracle recommendation, we should keep it outside of the unzip bin or install folder of ORDS. So the upgrades can happen without issues later.
in our case:

/etc/ords/config

Created this directory as root and granted ownership to oracle:dba for /etc/ords and forward.

[oracle@vcentos79-oracle-sa1 ~]$ ls -ld /etc/ords/
drwxr-xr-x. 3 oracle dba 20 Apr 22 19:40 /etc/ords/
[oracle@vcentos79-oracle-sa1 ~]$ ls -ld /etc/ords/*
drwxr-xr-x. 2 oracle dba 6 Apr 22 19:40 /etc/ords/config
[oracle@vcentos79-oracle-sa1 ~]$

Step 13) Let us setup the ORDS now using silent install...

Go through https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/22.3/ordig/installing-and-configuring-oracle-rest-data-services.html#GUID-A12A1E26-3AE1-4DB9-901B-2168613D467A

for a brief understanding of the command line options.

options we are skipping along with reasons below:

--bequeath-connect, we are using PDB, so we have to go through service. At this stage we dont know if oracle "alter session set container" support or not for this install. So we skip this.
--config-only, we are going with default option to create pool, create config and create db
--db-only, we are going with default option to create pool, create config and create db
--db-pool, we are going with default option to create pool, create config and create db; this should use the default db connection pool (DEFAULT_CONNECTION_POOL).
--db-sid, we offer service name , so no sid
--db-tns-alias, we offer all connection param manually
--db-tns-dir, we offer all connection param manually
--feature-db-api, we want all db actions feature, skip it
--feature-rest-enabled-sql, we want all db actions feature, skip it
--gateway-mode, leave it as default which is disabled
--gateway-user, leave it as default
--interactive, no we arent using, since we are silent mode
--help, no we arent using, since we are silent mode to perform install
--legacy-config <FOLDER>, no we arent using, fresh setup
--legacy-context <STRING>, no we arent using, fresh setup
---------skipping all below; since they apply to cdb install. we are using a pdb
--pdb-exclude <(PDB...)>
--pdb-open-readwrite-all--pdb-open-readwrite <(PDB...)>
--pdb-skip-readonly
--pdb-skip-closed
----------
--proxy-user-temp-tablespace, leave it to TEMP which is default
--schema-temp-tablespace, leave it to TEMP which is default

options we are using along with values:

--admin-user sys
--config /etc/ords/config
--db-hostname vcentos79-oracle-sa1
--db-port 1521
--db-servicename inventorypdb
--feature-sdw true
--log-folder /u01/app/oracle/ORDSLOG [created this directory]
--password-stdin
--proxy-user
--proxy-user-tablespace TBSPC_ORDS_PUBLICUSR
--schema-tablespace TBSPC_ORDS_METADATA

create a password file with sys password and proxy-user password.

[oracle@vcentos79-oracle-sa1 ORDSLOG]$ cat passwd.txt
<sys_password>
<ORDS_PUBLIC_USER_Passwd>

[oracle@vcentos79-oracle-sa1 ORDSLOG]$

Command:

ords --config /etc/ords/config install \
--admin-user sys \
--proxy-user \
--proxy-user-tablespace TBSPC_ORDS_PUBLICUSR \
--schema-tablespace TBSPC_ORDS_METADATA \
--db-hostname vcentos79-oracle-sa1 \
--db-port 1521 \
--db-servicename inventorypdb \
--log-folder /u01/app/oracle/ORDSLOG \
--feature-sdw true \
--password-stdin < passwd.txt

Output:

[oracle@vcentos79-oracle-sa1 ~]$ cd /u01/app/oracle/ORDSLOG/
[oracle@vcentos79-oracle-sa1 ORDSLOG]$ pwd
/u01/app/oracle/ORDSLOG

[oracle@vcentos79-oracle-sa1 ORDSLOG]$ ls -altr
total 4
drwxr-xr-x. 12 oracle oinstall 157 Apr 22 20:02 ..
-rw-r--r--.  1 oracle oinstall  20 Apr 22 20:11 passwd.txt
drwxr-xr-x.  2 oracle oinstall  24 Apr 22 20:11 .

[oracle@vcentos79-oracle-sa1 ORDSLOG]$ ords --config /etc/ords/config install \
> --admin-user sys \
> --proxy-user \
> --proxy-user-tablespace TBSPC_ORDS_PUBLICUSR \
> --schema-tablespace TBSPC_ORDS_METADATA \
> --db-hostname vcentos79-oracle-sa1 \
> --db-port 1521 \
> --db-servicename inventorypdb \
> --log-folder /u01/app/oracle/ORDSLOG \
> --feature-sdw true \
> --password-stdin < passwd.txt
ORDS: Release 23.1 Production on Sat Apr 22 19:17:48 2023
Copyright (c) 2010, 2023, Oracle.
Configuration:
  /etc/ords/config/
Oracle REST Data Services - Non-Interactive Install
Retrieving information.
The setting named: db.connectionType was set to: basic in configuration: default
The setting named: db.hostname was set to: vcentos79-oracle-sa1 in configuration: default
The setting named: db.port was set to: 1521 in configuration: default
The setting named: db.servicename was set to: inventorypdb in configuration: default
The setting named: plsql.gateway.mode was set to: proxied in configuration: default
The setting named: db.username was set to: ORDS_PUBLIC_USER in configuration: default
The setting named: db.password was set to: ****** in configuration: default
The setting named: feature.sdw was set to: true in configuration: default
The global setting named: database.api.enabled was set to: true
The setting named: restEnabledSql.active was set to: true in configuration: default
The setting named: security.requestValidationFunction was set to: ords_util.authorize_plsql_gateway in configuration: default
2023-04-22T19:17:55.978Z INFO        Installing Oracle REST Data Services version 23.1.1.r1091003 in INVENTORYPDB
2023-04-22T19:18:00.845Z INFO        ... Verified database prerequisites
2023-04-22T19:18:03.915Z INFO        ... Created Oracle REST Data Services proxy user
2023-04-22T19:18:05.823Z INFO        ... Created Oracle REST Data Services schema
2023-04-22T19:18:07.325Z INFO        ... Granted privileges to Oracle REST Data Services
2023-04-22T19:18:13.469Z INFO        ... Created Oracle REST Data Services database objects
2023-04-22T19:18:39.112Z INFO        Completed installation for Oracle REST Data Services version 23.1.1.r1091003. Elapsed time: 00:00:42.991
2023-04-22T19:18:39.295Z INFO        Completed configuring PL/SQL gateway user for Oracle REST Data Services version 23.1.1.r1091003. Elapsed time: 00:00:00.170
2023-04-22T19:18:39.295Z INFO        Log file written to /u01/app/oracle/ORDSLOG/ords_install_2023-04-22_191755_77617.log
2023-04-22T19:18:39.311Z INFO        To run in standalone mode, use the ords serve command:
2023-04-22T19:18:39.311Z INFO        ords --config /etc/ords/config serve
2023-04-22T19:18:39.311Z INFO        Visit the ORDS Documentation to access tutorials, developer guides and more to help you get started with the new ORDS Command Line Interface (http://oracle.com/rest).
[oracle@vcentos79-oracle-sa1 ORDSLOG]$ view /u01/app/oracle/ORDSLOG/ords_install_2023-04-22_191755_77617.log
[oracle@vcentos79-oracle-sa1 ORDSLOG]$

Looking at the log, it performed so many tasks (1700 lines) after connecting to the DB.
Anyway the install and config of ORDS is done.

>>>>>>>>>>> Next step to be continued