Use sqlcl with Oracle Linux 9 in Windows Terminal on Windows Subsystem for Linux

This post shows a quick and easy way to use Linux commands on Windows in the Windows Terminal by using Windows Subsystem for Linux (WSL). Oracle Developers can - for example - use sqlcl from that environment.

First, install Windows Terminal from the Microsoft Store, or with winget if you have already installed it.

Windows Subsystem for Linux is a great way to have Linux functionality on Windows. For the first time usage, follow the Microsoft Documentation to install it. Oracle Linux 9 is available in the Microsoft Store as fully compatible, free RHEL/CentOS alternatives. That is a nice option, if you have to develop for RHEL, but want to save the license costs. 

And SQLcl can easily installed, as long as you have Java on the system.

Download Oracle Linux 9 from the Microsoft Store or use winget:

winget search 'oracle linux'
winget install 9MXQ65HLMC27

It installed within minutes and can immediately be used. A nice thing about Windows Terminal with WSL is, that WSL automatically registers with Windows Terminal as an option.

If, for whatever reason, the Oracle Linux 9 option does not show up, try the following. Open Windows Terminal settings, choose Add a new profile, select OracleLinux_9 from the select list and click on Duplicate.

Open an Oracle Linux 9 tab and first install all updates via sudo dnf update. 

...

For SQLcl install Java. As this is a minimal installation, unzip and ncurses are missing so also install them.

sudo dnf in java-17-openjdk.x86_64

sudo dnf in ncurses

sudo dnf in unzip.x86_64

Download latest SQLcl zip archive from https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip and unzip it

wget https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip
unzip sqlcl-latest.zip

Add the path to the SQLcl binary to your .bashrc and source it. A cool feature of WLS is, that all your Windows drives are automatically mounted under /mnt/c, /mnt/d etc. So if you already have a network/admin directory under Windows, you can point your TNS_ADMIN to that location. Also set SQLPATH to enable some of the cool features of SQLcl. This is what my .bashrc ends with.

export PATH=/home/oracle/sqlcl/bin:$PATH
export TNS_ADMIN=/mnt/d/Oracle/network/admin

export SQLPATH=~/sql

Create a directory ~/sql and inside a file named login.sql with the above content.

set statusbar on
set highlighting on

 That't it. Open an Oracle Linux 9 tab in your Windows Terminal and connect to all your databases via sqlcl

Or just take it to use all usual Linux commands in Windows.

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.

How to install Synology Desktop Utilities on non-Debian Linux Systems

For Linux Desktop users with a Synology NAS, it is nice to use their Deskop Tooling. Unfortunately, Synology abandoned Support for .rpm based systems and only supports Debian's .deb format. Now these tools are not a must-use. Note Station can be used via the web front end and syncing could be done with any native Linux solution that you like. But if you want the ease of their tools, here is a possible solution.

UPDATE: since the Synology Drive Client is available via Flathub, I added this as the easiest option.

I have done this on OpenSUSE Tumbleweed, but this should work on any other .rpm based system.

1. The easiest way: Synology Drive Client from Flathub

On a KDE Plasma system like Tumbleweed or Manjaro, open Discover and add Flathub to your sources:

Search for Synology and install Synology Drive

As Flakpaks have limited system access by design, you need to configure Synology Drive to have file system access. The easiest way to do this is via another Flatpak app, search for Flatseal and install it.

Open Flatseal, select Synolgy Drive and grant it access to all directories you want to sync 

Start Synology Drive and use it as usual

This solution has a drawback at least on my installation on OpenSUSE Tumbleweed with KDE: it only runs with X11, not with Plasma. If this happens to you, try the next solution.

2. The (somewhat) easy way: Synology Drive Client

If you can not or don't want to use Flakpaks, then try this solution:

Alien is your friend, so get any of the alien packages from software.opensuse.org, download the .deb version of Synology Drive Client, use alien on it and just install the newly created .rpm (in my case synology-drive-7.0.2-12683.x86_64.rpm).

The tricky part was to get alien up and running. Alien requires rpm-build, which requires the patch utility. Tumbleweed comes with an older version of patch that comes with mingw, but rpm-build requires a newer one. So uninstall patch and install Gnu patch, then install rpm-build, then alien.

sudo alien -r synology-drive-client-12682.x86_64.deb
sudo zypper in synology-drive-7.0.2-12683.x86_64.rpm

Install it with zypper and you are done, this should also run on KDE Plasma.

Everything works, it also starts automatically on login.

3. The hard way: Synology Note Station Client

Ideally, the above way should work with any of the Synology Tools. But doing so completely messed up my KDE Plasma environment. Right after the zypper install command, my did not react to any input anymore. After restart, I did not boot to the login screen, but was stuck on a dark grey screen with a mouse pointer. Instead of repairing, I quickly reinstalled Tumbleweed and tried it again. Same problem again, I can reproduce this at will. I did not had the time to fix this, so I reinstalled Tumbleweed again. Here is the workaround that I used:

sudo alien -g synology-note-station-client-2.2.1-553-linux-x64.deb

Again, use alien, but this time use the -g option to just unpack the .rpm to a directory structure.

This gives you a directory structure, that just needs to be copied to the corresponding directories ...

cd synology-drive-client-12682.x86_64
sudo cp -R * /

and that's it.

That works fine, I haven't had any issues until now.

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

Clone a pluggable Database with APEX and connect to ORDS 22.x

 This is an update to my older post Clone a pluggable Database with APEX and connect to ORDS, which is still valid for older ORDS-versions before 22.x.

---8<-----------------------------------------------------------------------------

Sometimes I quickly need another test/dev environment for Apex. There are several ways to get one: snapshot or clone the VM, create one with Liquibase or clone the PDB and link the ORDS to it as described below. This is meant as a short cookbook, find more detailed information on the sites from Jeff Smith or Oracle-Base.

Start by connecting to your CDB and create a clone of the PDB where Apex is installed.

create pluggable database apexpdb2 from apexpdb1
file_name_convert = ('/apexpdb1/', '/apexpdb2/');

alter pluggable database apexpdb2 open;

sho pdbs

alter session set container=apexpdb2;

select * from apex_release;

Double check, that the new PDB has a valid APEX version installed. Add the newly created database in your $TNSADMIN/tnsnames.ora like

APEXPDB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = apexpdb2)
    )
  )

So much for for the database. Now all we have to do is to tell ORDS about the 2nd PDB. There is a chapter in the documentation for this, so the setup is straight forward. For dealing with ORDS, some settings should be made in .bashrc

export ORDS_CONFIG=/u01/ords/config
export ORDS_HOME=/u01/ords-22-2
export PATH=$ORDS_HOME/bin:$PATH

Stop ORDS if it is already running We need to add the DB with the following if we want to use the interactive mode

ords install -i

At the end, ORDS should be running in standalone mode. If you don't like interactive installations, you can do the same with one (loooong) command. First we need a text file with the passwords for the admin and proxy user:

[oracle@orcldevvm-ol8 ~]$ cat password.txt 
oracle
oracle

Then run something like

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

Open the new URL with your browser, in my case:

vm:8080/ords/apexpdb2/apex

The difference between the first and all other databases for an ORDS is, that the first one is always accessible without an additional base-path (like vm:8080/ords/apex) whereas the others always need that base-path. If you want a base-path also for the first database, see this posting from Jeff Smith. 

Apex for the second PDB should be available under that new URL.

And there is also a second Database Actions / SQLDeveloper Web under that new URL pattern.

This is done in less than 5 minutes and depending on your environment, this might be faster than cloning the complete VM. And dropping it only takes seconds, what also might be faster than restoring a snapshot of your VM.

Run two different Apex versions on a single standalone ORDS

A single standalone ORDS can handle multiple database connections with Apex as described here. But without additional configuration, this can be problematic if not all Apex instances are on the same version. For example, in the Developer VM Project, we have two PDB's with APEX locally installed at version 21.5.

When upgrading one PDB to Apex 22.1, the last step according to the documentation is Copying the Images Directory. After that step, the images directory files are on version 22.1 whereas the unpatched 2nd PDB is still running 21.5, which will lead to the following error:

Error: There is a problem with your environment because the Application Express files are not up-to-date!

So as long as not all Apex environments on a given ORDS instance are updated within the same maintenance window, seperate images directories are needed for at least any Apex version running on that ORDS. An image prefix maps to a virtual directory, for more information see Image Prefix changes in Oracle Application Express 4.2.2.

I have found no solution to configure multiple virtual directories on for standalone ORDS. But even with only a single static files directory, several file versions can be put below that single root.

Just organize the files in separate directories per version like images/images-21-5. This can be configured per application under Shared Components/User Interface Attributes.

If you have more than one application on the Apex instance, the setting could be set at once for the whole instance with the reset_image_prefix.sql script, that comes with your Apex installation under utilities. Usage is described in the MOS-Note How to Switch APEX to Use Oracle APEX Static Resources on Content Delivery Network (CDN) (Doc ID 2817084.1).

This script also does some cleanup which takes some time, so according to this blog post this is not recommended for production.

begin 
        apex_instance_admin.set_parameter(
            p_parameter => 'IMAGE_PREFIX',
            p_value     => '/i/images-21-5' );
        
        commit;
  end;

Alternatively this can be done with the above listed procedure, that does not do the invalidation. But in my instance, the changes took not effect immediately. So probably the invalidation in the script is there for a reason.

Especially if your Apex application is used over public internet, you might want to get rid of the static files anyways and use the the Content Delivery Network instead as described in Announcing Oracle APEX Static Resources on Content Delivery Network.

Use SQLcl in Oracle Linux 8 on Windows Subsystem for Linux

There is an update to this post on my Blog.

---8<-----------------------------------------------------------------------------------

Windows Subsystem for Linux is a great way to have Linux functionality on Windows. For the first time usage, follow the Microsoft Documentation to install it. Oracle Linux 7.9 and 8.5 are available in the Microsoft Store as fully compatible, free RHEL/CentOS alternatives. And SQLcl can easily installed, as long as you have Java on the system.

It installed within minutes and can immediately be updated via sudo dnf update. But when I tried so install Java, I got a 

RPM: error: db5 error(-30969) from dbenv->open: BDB0091 DB_VERSION_MISMATCH: Database environment version mismatch

Looks like the RPM repository is defect. I found the solution on GitHub:

sudo -s
cd /var/lib/rpm
rpm --rebuilddb
dd if=/dev/zero of=__db.001 bs=1M count=1
dd if=/dev/zero of=__db.002 bs=1M count=1
dd if=/dev/zero of=__db.003 bs=1M count=1
rpm -qa rpm

Follow the postings on GitHub. There seem to be easier solutions, but I did not double check them, because the first one worked for me.

sudo dnf in java-11-openjdk.x86_64

Now JDK installs without problems, for SQLcl 22.1 at least JDK 11.x is mandatory.

Download latest SQLcl zip archive from oracle.com/sqlcl and copy the contents to any directory, like ~/bin/sqlcl

Add the path to the SQLcl binary to your .bashrc and source it. A cool feature of WLS is, that all your Windows drives are automatically mounted under /mnt/c, /mnt/d etc. So if you already have a network/admin directory under Windows, you can point your TNS_ADMIN to that location. 

export PATH=/home/arne/bin/sqlcl/bin:$PATH
export TNS_ADMIN=/mnt/d/Oracle/network/admin

This is what my .bashrc ends with.

That gives me access from Linux to all databases I have configured under Windows.