Part 2: Inventory application setup (low code) using apex

Objective: Setup inventory application (low code) using oracle Apex

Part 2: Installing & Configuring Oracle APEX in PDB

In the last blog we saw how to create a PDB. Now let us install the APEX into the PDB which we just created. Please note we arent choosing multilingual apex, just english version for our practise.

Step 1) Tablespaces creation

alter session set container=INVENTORYPDB;

Remember to set db_create_file_dest;

SQL> show parameter db_create_file_Dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /oradata/
SQL>

create tablespace TBSPC_APEX_APEXUSR datafile size 10m autoextend on next 10m;
create tablespace TBSPC_APEX_FILEUSR datafile size 10m autoextend on next 10m;

SQL> select tablespacE_name,file_name,bytes from dba_data_files order by 1;
TABLESPACE_NAME      FILE_NAME                                                                                                 BYTES
-------------------- ---------------------------------------------------------------------------------------------------- ----------
SYSAUX               /oradata/ORA19C/F9B6A2A48B903161E05305C2A8C073B4/datafile/o1_mf_sysaux_l40k2on9_.dbf                  356515840
SYSTEM               /oradata/ORA19C/F9B6A2A48B903161E05305C2A8C073B4/datafile/o1_mf_system_l40k2on0_.dbf                  283115520
TBSPC_APEX_APEXUSR   /oradata/ORA19C/F9B6A2A48B903161E05305C2A8C073B4/datafile/o1_mf_tbspc_ap_l480xq7y_.dbf                 10485760
TBSPC_APEX_FILEUSR   /oradata/ORA19C/F9B6A2A48B903161E05305C2A8C073B4/datafile/o1_mf_tbspc_ap_l480xwwm_.dbf                 10485760
UNDOTBS1             /oradata/ORA19C/F9B6A2A48B903161E05305C2A8C073B4/datafile/o1_mf_undotbs1_l40k2onb_.dbf                104857600
SQL>

SQL> select tablespace_name,file_name from dba_temp_files;
TABLESPACE_NAME      FILE_NAME
-------------------- ----------------------------------------------------------------------------------------------------
TEMP                 /oradata/ORA19C/F9B6A2A48B903161E05305C2A8C073B4/datafile/o1_mf_temp_l40k2onc_.dbf


Step 2) Perform Apex installation

Goto Unzip folder of apex_22.2_en.zip [/u01/app/oracle/APEXBIN]
Goto apex directory [/u01/app/oracle/APEXBIN/apex]
cd /u01/app/oracle/APEXBIN/apex

Verify APEX Presence in CDB:

SQL> select comp_name from dba_registry order by 1;
COMP_NAME
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
JServer JAVA Virtual Machine
OLAP Analytic Workspace
Oracle Database Catalog Views
Oracle Database Java Packages
Oracle Database Packages and Types
Oracle Database Vault
Oracle Label Security
Oracle Multimedia
Oracle OLAP API
Oracle Real Application Clusters
Oracle Text
Oracle Workspace Manager
Oracle XDK
Oracle XML Database
Spatial
15 rows selected.

alter session set container=INVENTORYPDB;

SQL> col conname for a20
SQL> select sys_context('userenv','con_name') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INVENTORYPDB
SQL>

From https://docs.oracle.com/en/database/oracle/apex/22.2/htmig/downloading-installing-apex.html#GUID-7E432C6D-CECC-4977-B183-3C654380F7BF

We are trying to setup Full development environment; hence we choose apexins.sql file.

@apexins.sql TBSPC_APEX_APEXUSR TBSPC_APEX_FILEUSR TEMP /i/

As soon as the command is launched..

SQL> @apexins.sql TBSPC_APEX_APEXUSR TBSPC_APEX_FILEUSR TEMP /i/
...set_appun.sql
PL/SQL procedure successfully completed.
<empty lines trimmed>
...set_ufrom_and_upgrade.sql
PL/SQL procedure successfully completed.

Session altered.

FOO3
------------------------------
install2023-04-22_17-00-53.log
. ORACLE
.
. Oracle APEX Installation.
..........................................
...................................................Continued.
>> will take 10 mins
End of install summary from sqlplus:

Thank you for installing Oracle APEX 22.2.0
Oracle APEX is installed in the APEX_220200 schema.
The structure of the link to the Oracle APEX administration services is as follows:
http://host:port/ords/apex_admin
The structure of the link to the Oracle APEX development interface is as follows:
http://host:port/ords
timing for: Phase 3 (Switch)
Elapsed: 00:00:14.80
timing for: Complete Installation
Elapsed: 00:08:31.51

Exit the sqlplus session.

Step 3) Verify the install

SQL> select sys_context('userenv','con_name') from dual;
SYS_CONTEXT('USERENV','CON_NAME')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INVENTORYPDB
SQL> select comp_id,comp_name,version,status,modified from dba_registry order by comp_name;
COMP_ID    COMP_NAME                                VERSION      STATUS     MODIFIED
---------- ---------------------------------------- ------------ ---------- -----------------------------
JAVAVM     JServer JAVA Virtual Machine             19.0.0.0.0   VALID      06-APR-2023 19:59:27
APS        OLAP Analytic Workspace                  19.0.0.0.0   VALID      06-APR-2023 19:59:27
APEX       Oracle APEX                              22.2.0       VALID      22-APR-2023 17:09:25 <<<< The APEX component is installed
CATALOG    Oracle Database Catalog Views            19.0.0.0.0   VALID      06-APR-2023 19:59:23
CATJAVA    Oracle Database Java Packages            19.0.0.0.0   VALID      06-APR-2023 19:59:27
CATPROC    Oracle Database Packages and Types       19.0.0.0.0   VALID      06-APR-2023 19:59:23
DV         Oracle Database Vault                    19.0.0.0.0   VALID      06-APR-2023 19:59:28
OLS        Oracle Label Security                    19.0.0.0.0   VALID      06-APR-2023 19:59:28
ORDIM      Oracle Multimedia                        19.0.0.0.0   VALID      06-APR-2023 19:59:27
XOQ        Oracle OLAP API                          19.0.0.0.0   VALID      06-APR-2023 19:59:28
RAC        Oracle Real Application Clusters         19.0.0.0.0   OPTION OFF 17-APR-2019 02:03:52
CONTEXT    Oracle Text                              19.0.0.0.0   VALID      06-APR-2023 19:59:27
OWM        Oracle Workspace Manager                 19.0.0.0.0   VALID      06-APR-2023 19:59:26
XML        Oracle XDK                               19.0.0.0.0   VALID      06-APR-2023 19:59:27
XDB        Oracle XML Database                      19.0.0.0.0   VALID      06-APR-2023 19:59:24
SDO        Spatial                                  19.0.0.0.0   VALID      06-APR-2023 19:59:28
16 rows selected.
SQL>
SQL> select username,created,oracle_maintained,account_status from dba_users order by created,username;
USERNAME                       CREATED   O ACCOUNT_STATUS
------------------------------ --------- - --------------------
...
DVF                            17-APR-19 Y LOCKED
DVSYS                          17-APR-19 Y LOCKED
INVENTORYADM                   19-APR-23 N OPEN
APEX_220200                    22-APR-23 Y LOCKED <<< so these are the new users
APEX_PUBLIC_USER               22-APR-23 Y LOCKED <<< so these are the new users
FLOWS_FILES                    22-APR-23 Y LOCKED <<< so these are the new users
39 rows selected.

step 4) Let us setup instance administrator now..
Again go the apex directory (unzip content)

cd /u01/app/oracle/APEXBIN/apex
@apxchpwd.sql
admin password: Adm1n!Apex

SQL> @apxchpwd.sql
...set_appun.sql
================================================================================
This script can be used to change the password of an Oracle APEX
instance administrator. If the user does not yet exist, a user record will be
created.
================================================================================
Enter the administrator's username [ADMIN]
User "ADMIN" does not yet exist and will be created.
Enter ADMIN's email [ADMIN] **********
Enter ADMIN's password []
Created instance administrator ADMIN.
SQL>

This isnt a db user, see below no new users.

SQL> select username,created,oracle_maintained,account_status from dba_users where trunc(created)=trunc(sysdate) order by created,username;
USERNAME                       CREATED   O ACCOUNT_STATUS
------------------------------ --------- - --------------------
APEX_220200                    22-APR-23 Y LOCKED
APEX_PUBLIC_USER               22-APR-23 Y LOCKED
FLOWS_FILES                    22-APR-23 Y LOCKED
SQL>

step 5) Set password life time to unlimited to default profile or create a new profile with password life time set to unlimited.

SQL> alter profile default limit password_life_time unlimited;
SQL>
SQL> select * from dba_profiles where RESOURCE_NAME='PASSWORD_LIFE_TIME';
PROFILE              RESOURCE_NAME                  RESOURCE LIMIT                          COM INH IMP
-------------------- ------------------------------ -------- ------------------------------ --- --- ---
DEFAULT              PASSWORD_LIFE_TIME             PASSWORD UNLIMITED                      NO  NO  NO
ORA_STIG_PROFILE     PASSWORD_LIFE_TIME             PASSWORD 60                             NO  NO  NO
SQL>

step 6) Unlock APEX_PUBLIC_USER user and reset its password (the step 5 facilitates this user).

ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;
alter user APEX_PUBLIC_USER identified by apex_public_user;

Output:
SQL> ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;
SQL> alter user APEX_PUBLIC_USER identified by apex_public_user;
Overall /oradata grew by 400MB.

... next step is ORDS (Oracle Restful Data service) installation and configuration.