Installing APEX on Oracle Database Base Service

This is Part 3 of my  Oracle Database Developer OCI Environment series.

Download APEX

On the database machine, get the latest APEX release from https://www.oracle.com/tools/downloads/apex-downloads/

Download the ‘All Languages’ package or use wget, rename the apex folder to append the version number and create a symlink apex to that folder.

cd /u01
wget -P /tmp https://download.oracle.com/otn_software/apex/apex-latest.zip
unzip -d /tmp /tmp/apex-latest.zip
mv /tmp/apex apex-26-1
ln -sfn apex-26-1 apex

If ORDS is planned to run on a separate machine, repeat those steps on the ORDS machine, as the static files from the apex directory are also required on the ORDS machine.

Run APEX installer

From the apex directory on the database machine, run the APEX installer (change tablespaces according to your standards). The user SYS is mandatory, other users are not supported for running the APEX installation (https://apexapps.oracle.com/apex/r/apex_pm/ideas/details?idea=FR-3902&session=130781358621911).

cd /u01/apex

sql sys/<password>@orclpdb as sysdba @apexins sysaux sysaux temp /i/

or

export ORACLE_PDB_SID=orclpdb
sql / as sysdba @apexins sysaux sysaux temp /i/

Keep the session open. Check the APEX release

select * from apex_release;

Set the APEX Administrator password by running

@apxchpwd.sql

Unlock the APEX_PUBLIC_USER and change his password, which has been randomly set at installation.

alter user apex_public_user identified by oracle account unlock;

Still in the same session, run @apex_rest_config.sql and set both passwords for the APEX_LISTENER user and the APEX_REST_PUBLIC_USER user.

@apex_rest_config.sql

To use web services and for sending email, network services need to be enabled. Copy the following from the documentation and run it.  

BEGIN    
   DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
      host => '*',
      ace => xs$ace_type(privilege_list => xs$name_list('connect'),
                         principal_name => 'APEX_230200',
                         principal_type => xs_acl.ptype_db));
END;
/

Install ORDS

First, we need to make some settings. Add the following to .bashrc and source it.

cat >> ~/.bashrc <<EOF
export _JAVA_OPTIONS="-Xms3000M -Xmx3000M"
export ORDS_CONFIG=/u01/ords-config
export ORDS_BASE=/u01/ords
export PATH=\$ORDS_BASE/bin:\$PATH
EOF
. ~/.bashrc    

Download the latest ORDS from https://download.oracle.com/otn_software/java/ords/ords-latest.zip and unzip the contents to your target directory (eg. /u01/ords-24-2 and reflect that in the $ORDS_BASE variable in .bashrc. Create the directories and optionally create a symlink:

cd /u01
mkdir ords-26-1
wget -P /tmp https://download.oracle.com/otn_software/java/ords/ords-latest.zip
unzip -d ords-26-1 /tmp/ords-latest.zip
ln -sfn ords-26-1 ords

Have your static document root folder at hand, or create a dummy document root and a log folder like

mkdir -p $ORDS_CONFIG/global/doc_root
mkdir -p $ORDS_CONFIG/log  

to avoid error messages because the document root folder does not exist.

ORDS requires at least JDK 17, but newer versions will also work. As opc user, install any JDK version 17 or higher eg.

sudo dnf in jdk-25-headless.x86_64

Optional: run ORDS installation without SYS

If you want to install ORDS without SYS AS SYSDBA, a user needs to be prepared for that task, see MOS note 2597045.1 or the ORDS Installation and Configuration Guide 1.4 ORDS Installer Privileges Script.

Create a user to install ORDS and run the script

grant connect, RESOURCE to ordsinstall identified by oracle;
@/u01/ords/scripts/installer/ords_installer_privileges.sql ordsinstall 

Mind the lines regarding APEX at the end of the script execution:

grant select on APEX_230200.WWV_FLOW_RT$MODULES to "ORDSINSTALL" with grant option;
grant select on APEX_230200.WWV_FLOW_POOL_CONFIG to "ORDSINSTALL" with grant option; 

As these explicit target the give APEX version, these need to be rerun with every new installed APEX version. But installation of a new APEX version requires SYSDBA rights anyways.

Run the ORDS installer

Either use the interactive installation or configure every parameter on the command line.

Interactive ORDS installation

run

ords install --interactive

The ORDS installation comes up with a menu

If ORDS should be installed without SYS, choose 1 and enter your given user from the previous chapter.

Select 3 and set a password for the ORDS_PUBLIC_USER .

Select 9 and set the APEX static resources location to something like /u01/apex/images/ .

Then continue by choosing A.

ORDS non-interactive silent installation

Alternatively, configure ORDS via command line parameters like shown below. Create a file with the passwords for the ordsinstall and ORDS_PUBLIC_USER passwords and pipe it into the command or if you do not want to use a password file, just enter the parameters when the installer starts. There will be no visible prompt, but it is there. Just type the password for SYS or ordsadmin, hit <Return>, enter the Password for apex_public_user, hit <Return> again and you are done. Some examples:

Basic connection with ORDS install user and interactive password entry

ords install --db-hostname localhost --db-port 1521 
    --db-servicename db26pdb.subxxxx.vcndb.oraclevcn.com --feature-sdw true 
    --log-folder $ORDS_CONFIG/log --admin-user ordsinstall --gateway-mode proxied 
    --    gateway-user APEX_PUBLIC_USER --proxy-user --password-stdin

Basic connection with SYS and password file

ords install --db-hostname localhost --db-port 1521 --db-servicename db26pdb.subxxxx.vcndb.oraevcn.com 
    --feature-sdw true --log-folder $ORDS_CONFIG/log --admin-user sys --gateway-mode proxied 
    --gateway-user APEX_PUBLIC_USER --proxy-user --password-stdin stdin < ~/password.txt

TNS connection with SYS and password file

ords --java-options "-DuseOracleHome=true" install --db-tns-dir $ORACLE_HOME/network/admin 
    --feature-sdw true --db-tns-alias orclpdb --admin-user sys --gateway-mode proxied 
    --gateway-user APEX_PUBLIC_USER --bequeath-connect --log-folder /u01/ords-config/log 
    --proxy-user --password-stdin < ~/password.txt

The non-interactive installation does not set the static resources location, so add this with

ords config set standalone.static.path /u01/apex/images/
ords config set standalone.static.context.path /i
ords config set jdbc.MaxLimit 30
ords config set jdbc.InitialLimit 20

If you plan to use the MongoDB-API, also run the following:

ords config set mongo.enabled true
ords config set mongo.access.log $ORDS_CONFIG/log
ords config set mongo.port 27017

Configuring ORDS via command line does not start ORDS afterward, so start ORDS with

ords serve

ORDS should come up without warnings.

Test ORDS connection

For a first try run the following on the same machine

curl -I http://localhost:8080/ords/

To test the connection from any other machine, we need to open the SQLNet port in the firewalls first.
As opc user run

sudo iptables -I INPUT 1 -p tcp --dport 8080 -j ACCEPT
sudo service iptables save
sudo iptables -S | grep 8080

Then we need to add the port to the Security List. As already done with port 1521 in the first chapter, navigate to your VCN and open the Default Security List. Under Ingress Rules, where you already have a rule for port 1521, create a similar rule for port 8080.

So the result should look as follows

Open http://<address>:8080 in a Browser, that should route to the ORDS landing page.

To test SQL Developer Web, we need at least one rest-enabled user. Connect to the orclpdb as SYS. For REST-enabling schemas, the executing user first needs an additional privilege, see MOS-Note 2863257.1

grant inherit privileges on user sys to ords_metadata;

Then run

BEGIN
    ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
                       p_schema => 'HR',
                       p_url_mapping_type => 'BASE_PATH',
                       p_url_mapping_pattern => 'hr',
                       p_auto_rest_auth => FALSE);
    commit;
END;
/

Klick on the ‘Go’ button below SQL Developer Web and login with hr. That should open the SDW homepage.

Navigate back to the ORDS Landing page or go directly to http://hostname:8080/ords/apex . Login to workspace INTERNAL with user ADMIN and the password you provided at installation. 

That should lead you to the APEX welcome page.

So APEX is up and running.

Create an APEX Interactive Grid on an ORDS AutoREST service

 If the target database cannot be accessed by a database link, this might be a super easy alternative.

Say you have an HR schema with the EMPLOYEES table. RMB and choose Enable REST Service ...

For this easy example, check Enable object and uncheck Authorization required. Click through the dialog until the end.

Open Database Actions and go to REST > AutoREST to find the EMPLOYEES Service. Click on the Open in new tab icon right of the URL.

That should work ootb, copy the URL

Move over to the APEX App Builder, Shared Components, REST Data Sources and click on Create.

Choose from scratch, give it a name, paste the service URL and change REST Data Source Type to Oracle REST Data Services. Click through the end of the dialog, accepting the defaults.

In your application, create a new page with an Interactive Grid. Point it to the new REST Data Source and check Editing Enabled, if you want to use that.

For editing, it is mandatory to select a primary key column. Click through the end of that dialog.

Run the page, change some data and click Save.

That should work without any extra manual configuration or coding.

Installing ORDS 23.1 with Apex 23.1 on Oracle Database 23c Free Developer Release

This post is part of my Oracle Developer VM  Project series. It is not meant to replace the official documentation in any way and what I describe is no recommended configuration. It is only meant to be a guide, if you follow my DB Developer VM Project and want to add these services to your VM.

Part 1: Install Apex

Download the Apex install bundle and unzip it (eg. to /u01), which creates an apex folder. In the unpacked folder run the installer

sqlplus sys/your_password@freepdb1 as sysdba @apexins sysaux sysaux temp /i/

Leave the session open and create the Apex Admin user by running @apxchpwd.sql.

Unlock the APEX_PUBLIC_USER and change his password, which has been randomly set at installation

alter user apex_public_user identified by oracle account unlock;

Still in the same session, run @apex_rest_config.sql and set both passwords for the APEX_LISTENER user and the APEX_REST_PUBLIC_USER user.

To use web services and for sending email, network services need to be enabled. Copy the following from the documentation and run it:

1 2 3 4 5 6 7

BEGIN DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE( host => '*', ace => xs$ace_type(privilege_list => xs$name_list('connect'), principal_name => 'APEX_230100', principal_type => xs_acl.ptype_db)); END;

Part 2: Install ORDS

First, we need to make some settings. Add the following to .bashrc and source it.

export _JAVA_OPTIONS="-Xms3000M -Xmx3000M"
export ORDS_CONFIG=/u01/ords-config
export ORDS_HOME=/u01/ords-23-1
export PATH=$ORDS_HOME/bin:$PATH

Create both directories:

mkdir /u01/ords-23-1
mkdir /u01/ords-config

Download the latest ORDS from https://download.oracle.com/otn_software/java/ords/ords-latest.zip and unzip the contents to your target directory (eg. /u01/). Optional rename the ords directory from the archive and add the ORDS version number, eg. ords-23-1 (and reflect that in the $ORDS_HOME variable in .bashrc). This makes it easier to switch versions when neccesary.

As $PATH and $ORDS_CONFIG are already set, from anywhere run

 

ords install --interactive

ORDS will ask for some configuration parameters. In most cases, you can just accept the defaults.

If you have followed my guide so far, the only parameters, that need to be set, are:

• Enter the database service name [orcl]: freepdb1
• Provide database username with administrator privileges: sys (and your password)
• Enter the APEX static resources location: /u01/apex/images/

After the setup, ORDS will keep running in standalone mode. We are not done yet, but give it a try to check if everything went fine so far. Wait until the ORDS has completely started: 

Open Firefox and enter http://localhost:8080/ords, that should give you the Apex login page (as this is the first call, it will take a few seconds).

Looking at the ORDS terminal window, you will see the following warnings:

Though this is not a production environment, I prefer to configure the system to run without these warnings. At least it makes it easier to notice, if something really goes wrong. In this single user environment, we can set both parameters to lower values like below. 

Go to /u01/ords-config/global/ and edit settings.xml. Add the following lines before the closing </properties>:

<entry key="jdbc.MaxLimit">30</entry>
<entry key="jdbc.InitialLimit">10</entry> 

I also got the following error:

INFO Disabling document root because the specified folder does not exist: /u01/ords-config/global/doc_root

To get rid of this INFO either create that directory or edit /u01/ords-config/global/settings.xml and set standalone.doc.root to an existing folder.

Sometimes I ran into errors were all like WARNING The pool named: |apex|al| is invalid and will be ignored: The username or password for the connection pool named |apex|al|, are invalid, expired, or the account is locked

Probably I messed up my passwords when running the installer. This can be fixed by setting the passwords for the following users (and unlocking them, just in case):

1 2 3 4 5 6 7 8

alter user ORDS_PUBLIC_USER account unlock; alter user APEX_PUBLIC_USER account unlock; alter user APEX_LISTENER account unlock; alter user APEX_REST_PUBLIC_USER account unlock; alter user ORDS_PUBLIC_USER identified by oracle; alter user APEX_PUBLIC_USER identified by oracle; alter user APEX_LISTENER identified by oracle; alter user APEX_REST_PUBLIC_USER identified by oracle;

Now ORDS should come up without errors.

Just to double check that everything is there, call http://localhost:8080/ords/sql-developer

Of course, you need a rest enabled schema to log in. That's it, everything is running.

Cloning a PDB with APEX pointing to ORDS REST services on the same database

This post shows cloning a database with APEX and some database REST services in the same database, which are accessed from APEX via a REST data source. Say we have a PDB testpdb and clone it to clonepdb for dev/test. The EMPLOYEES table in HR is REST enabled and the APEX application has a REST Data Source for that REST service. Without any changes, the REST Data Source from clonepdb would still point to testpdb. But it should point to clonepdb. This could be changed manually, but if this is needed for automatic provisioning of developer environments, some automation would be helpfull.

The URL is shown in ORDS Database Actions under REST/AutoREST

The APEX application has a REST Data Source TestEmployees pointing to that service:

The Remote Server attribute contains the IP address and the database connection, in this case the TESTPDB. Click on the pen next to it.

Enable Prompt on Install.

Now clone testpdb to clonepdb:

create pluggable database clonepdb from testpdb 
file_name_convert = ('/testpdb/', '/clonepdb/');

alter pluggable database clonepdb open;

For easier access add clonepdb to tnsnames.ora:

cat >> $TNS_ADMIN/tnsnames.ora <<EOF
CLONEPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = clonepdb)
    )
  )

EOF
tnsping clonepdb

Stop ORDS, register clonepdb with ORDS interactively or create a password.txt file with your passwords and run:

cat > password.txt <<EOF 
<password>
<password>
EOF

ords install --db-pool clonepdb --db-hostname localhost --db-port 1521 --db-servicename clonepdb --feature-sdw true --feature-db-api true --feature-rest-enabled-sql true --admin-user sys --proxy-user --password-stdin < password.txt

Restart ORDS and navigate to REST/AutoREST. The service for the employees table also exists in clonepdb ...

... and works as expected.

The APEX application also made it into clonepdb, but of course the TestEmployees REST Data Source still points to testpdb

As the application has been cloned with the PDB, the Prompt on Install attribute does not trigger. We need to ex- and import the application. Export the application from testpdb and make sure that Export with Original IDs is checked

Import the application on clonepdb and check Reuse Application ID 100 From Export File 

In the last step, the installer prompts for the Base URL of the Remote Server, which has been tagged for Prompt on Install. Change it from testpdb to clonepdb.

Open the REST Data Source, it now shows the correct local endpoint. 

But for automatic deployment of developer environments, we need to get rid of the interactive step. Before we can import the export file by running it in SQLcl, we need to set the override with APEX_APPLICATION_INSTALL.SET_REMOTE_SERVER .

declare
    l_base_url varchar2(255);
begin
  apex_application_install.set_remote_server(
    p_static_id => '192_168_56_104_ords_testpdb',
    p_base_url  => 'http://192.168.56.104:8080/ords/clonepdb/');

    l_base_url := apex_application_install.get_remote_server_base_url('192_168_56_104_ords_testpdb');
    DBMS_OUTPUT.PUT_LINE('Base URL for ' || '192_168_56_104_ords_testpdb' || ' is now ' || l_base_url);
end;
/

That should give

And opening the REST Data Source in the APEX application on clonepdb should show the URL pointing to clonepdb.

So all that is needed after cloning the database with the APEX application and the REST service is running a script with apex_application_install.set_remote_server and re-importing the application.

The Database Developer VM: installing Apex 22.2 and ORDS 22.4

This post is part of my Oracle Developer VM  Project series. It is not meant to replace the official documentation in any way and what I describe is no recommended configuration. It is only meant to be a guide, if you follow my DB Developer VM Project and want to add these services to your VM.

Part 1: Install Apex

According to the documentation, memory_target needs to be set to at least 300M for Apex, but in my setup the database won't start with this amount of memory. I get the following error:

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 6304M

So first, we will set that parameter to the required value (or higher, if you need it).

1 2	alter system set memory_target='6304M' scope=spfile; startup force;

If the database won't start up because you run into a ORA-00845: MEMORY_TARGET not supported on this system then your shared memory (/dev/shm) is configured too low. Checking with df -h will show a value smaller than 6.304 MB. To solve this, run

mount -t tmpfs shmfs -o size=10G /dev/shm

and startup the database again.

This should give you the following: 

Download the Apex install bundle and unzip it (eg. to /u01), which creates an apex folder. In the unpacked folder run the installer

sqlplus sys/your_password@orclpdb as sysdba @apexins sysaux sysaux temp /i/

Leave the session open and create the Apex Admin user by running @apxchpwd.sql.

Unlock the APEX_PUBLIC_USER and change his password, which has been randomly set at installation

alter user apex_public_user identified by oracle account unlock;

Still in the same session, run @apex_rest_config.sql and set both passwords for the APEX_LISTENER user and the APEX_REST_PUBLIC_USER user.

To use web services and for sending email, network services need to be enabled. Copy the following from the documentation and run it:

1 2 3 4 5 6 7

BEGIN DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE( host => '*', ace => xs$ace_type(privilege_list => xs$name_list('connect'), principal_name => 'APEX_220200', principal_type => xs_acl.ptype_db)); END;

Part 2: Install ORDS

First, we need to make some settings. Add the following to .bashrc and source it.

export _JAVA_OPTIONS="-Xms3000M -Xmx3000M"
export ORDS_CONFIG=/u01/ords-config
export ORDS_HOME=/u01/ords-22-4
export PATH=$ORDS_HOME/bin:$PATH

Create both directories:

mkdir /u01/ords-22-4
mkdir /u01/ords-config

Download the latest ORDS from https://download.oracle.com/otn_software/java/ords/ords-latest.zip and unzip the contents to your target directory (eg. /u01/). Optional rename the ords directory from the archive and add the ORDS version number, eg. ords-22-4 (and reflect that in the $ORDS_HOME variable in .bashrc). This makes it easier to switch versions when neccesary.

As $PATH and $ORDS_CONFIG are already set, from anywhere run

 

ords install --interactive

ORDS will ask for some configuration parameters. In most cases, you can just accept the defaults.

If you have followed my guide so far, the only parameters, that need to be set, are:

• Enter the database service name [orcl]: orclpdb
• Provide database username with administrator privileges: sys (and your password)
• Enter the APEX static resources location:/u01/apex/images/

After the setup, ORDS will keep running in standalone mode. We are not done yet, but give it a try to check if everything went fine so far. Wait until the ORDS has completely started: 

Open Firefox and enter http://localhost:8080/ords, that should give you the Apex login page (as this is the first call, it will take a few seconds).

Looking at the ORDS terminal window, you will see the following warnings:

Though this is not a production environment, I prefer to configure the system to run without these warnings. At least it makes it easier to notice, if something really goes wrong. In this single user environment, we can set both parameters to lower values like below. 

Go to /u01/ords-config/global/ and edit settings.xml. Add the following lines before the closing </properties>:

<entry key="jdbc.MaxLimit">20</entry>
<entry key="jdbc.InitialLimit">10</entry> 

I also got the following error:

INFO Disabling document root because the specified folder does not exist: /u01/ords-config/global/doc_root

To get rid of this INFO either create that directory or edit /u01/ords-config/global/settings.xml and set standalone.doc.root to an existing folder.

Sometimes I ran into errors were all like WARNING The pool named: |apex|al| is invalid and will be ignored: The username or password for the connection pool named |apex|al|, are invalid, expired, or the account is locked

Probably I messed up my passwords when running the installer. This can be fixed by setting the passwords for the following users (and unlocking them, just in case):

1 2 3 4 5 6 7 8

alter user ORDS_PUBLIC_USER account unlock; alter user APEX_PUBLIC_USER account unlock; alter user APEX_LISTENER account unlock; alter user APEX_REST_PUBLIC_USER account unlock; alter user ORDS_PUBLIC_USER identified by oracle; alter user APEX_PUBLIC_USER identified by oracle; alter user APEX_LISTENER identified by oracle; alter user APEX_REST_PUBLIC_USER identified by oracle;

Now ORDS should come up without errors.

Just to double check that everything is there, call http://localhost:8080/ords/sql-developer

Of course, you need a rest enabled schema to log in. That's it, everything is running.

Use SQL PIVOT to display a pivot table in Apex #JoelKallmanDay

In a project, we had the question on how we can visualize a pivot table in Apex. We have locations with warehouses, which we want to display as rows. In the columns, we needed the quantity of each stock item.

So our simple data model is:

To query all items with their stock, we use

select loc_id, loc_name, sto_quantity, item_name
from location, stock, item
where sto_loc_id_fk = loc_id
and sto_item_id_fk = item_id

Using this statement for an Interactive Report in Apex gives the following output:

We have several lines for the same item, eg. Tire B in Frankfurt. So we need to sum up per item and location.

Apex does not have a native feature to make a pivot table from this data, as this is already implemented with the SQL PIVOT. The following statement gives us a row for every location and a colum for every items stock

select * from (
select loc_id, loc_name, sto_quantity, item_name
from location, stock, item
where sto_loc_id_fk = loc_id
and sto_item_id_fk = item_id
)
pivot( sum(sto_quantity) as quantity for item_name 
    in ('Tire A' as Tire_A, 'Tire B' as Tire_B, 'Tire C' as Tire_C,
        'Screw A' as Screw_A, 'Screw B' as Screw_B, 'Screw C' as Screw_C)
);

Using this as a source for another Interactive Report gives us the required pivot view

Easy as that. If you want to try yourself, here is the SQL code to create the tables and some test data.

drop table location;
drop table stock;
drop table item;

create table location (
    loc_id                         number generated by default on null as identity 
                                   constraint location_loc_id_pk primary key,
    loc_name                       varchar2(30 char)
)
;

create table stock (
    sto_id                         number generated by default on null as identity 
                                   constraint stock_sto_id_pk primary key,
    sto_loc_id_fk                number,
    sto_item_id_fk                     number,
    sto_quantity                   number
)
;

create table item (
    item_id                        number generated by default on null as identity 
                                   constraint item_item_id_pk primary key,
    item_name                      varchar2(30 char)
)
;


create or replace procedure create_stock_data as 
    type vc_list is table of varchar2(32) 
        index by pls_integer;
    loc_list    vc_list;
    item_list   vc_list;
begin

    loc_list := vc_list('Frankfurt','Hannover','Berlin','Regensburg','Elmshorn','Bensheim');
    
    for idx in indices of loc_list loop
        dbms_output.put_line( idx || ': ' || loc_list(idx) );
        insert  into location
                (loc_id, loc_name)
        values  (idx, loc_list(idx) );
    end loop;
    
    item_list := vc_list('Tire A', 'Tire B', 'Tire C','Screw A','Screw B','Screw C');  
    
    for idx in indices of loc_list loop
        dbms_output.put_line( idx || ': ' || item_list(idx) );
        insert  into item
                (item_id, item_name)
        values  (idx, item_list(idx) );
    end loop;   

    for i in 1..100 loop
      dbms_output.put_line(i);
      insert   into stock
               (sto_id, sto_loc_id_fk, sto_item_id_fk, sto_quantity)
      values   (i, round(dbms_random.value(1,6)), round(dbms_random.value(1,6)), round(dbms_random.value(1,100)) );
    end loop;

end create_stock_data;
/