Cannot Spool in Oracle 11g in docker from Mac, Issues of Filepath or Permission? (SP2-0606: Cannot create SPOOL file)

Question

I installed Oracle 11g xe on my Mac in docker:

docker run -h "oraclehost" --name "oracle" -d -p 1521:1521 carloscastillo/rgt-oracle-xe-11g

In docker terminal (CLI, not the normal Mac terminal), everything seems fine and I can execute queries:

The default interactive shell is now zsh.
To update your account to use zsh, please run `chsh -s /bin/zsh`.
For more details, please visit https://support.apple.com/kb/HT208050.
Kevins-MacBook-Pro:~ kevin$ docker exec -it 6b............1a /bin/sh; exit
# sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar 15 10:50:29 2021
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Enter user-name: system
Enter password: 

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> connect hr/tiger
Connected.

But when I want to create spool files it shows the error bellow. I tried to google it but that still didn't work. Is the filepath correct? or does this action have the permission to do so?

SQL> SPOOL Users/kevin/Desktop/DBM/DBsession1.txt
SP2-0606: Cannot create SPOOL file "Users/kevin/Desktop/DBM/DBsession1.txt"

Another question is that when I want to move arrow keys in the terminal in docker, it shows: ^[[C^[[C^[[D^[[C

I changed Mac terminal preference to Commanded (complete path): /bin/bash as said in this page, but that still does not work...

Can anyone help please leave a message, thank you!

Answer 1

Your scenario

SQL> !df -h
Filesystem      Size  Used Avail Use% Mounted on
overlay          59G   40G   16G  72% /
tmpfs            64M     0   64M   0% /dev
tmpfs           3.0G     0  3.0G   0% /sys/fs/cgroup
shm              64M     0   64M   0% /dev/shm
overlay         3.0G  560K  3.0G   1% /usr/sbin/docker-init
/dev/vda1        59G   40G   16G  72% /etc/hosts
grpcfuse        932G  484G  431G  53% /opt/oracle/oradata
tmpfs           3.0G     0  3.0G   0% /proc/acpi
tmpfs           3.0G     0  3.0G   0% /sys/firmware

SQL>

SQL> spool /Users/BJBRA/Desktop/myfile.txt
SP2-0606: Cannot create SPOOL file "/Users/BJBRA/Desktop/myfile.txt"

What did I do?

SQL> !df -h
Filesystem      Size  Used Avail Use% Mounted on
overlay          59G   40G   17G  71% /
tmpfs            64M     0   64M   0% /dev
tmpfs           3.0G     0  3.0G   0% /sys/fs/cgroup
shm              64M     0   64M   0% /dev/shm
overlay         3.0G  560K  3.0G   1% /usr/sbin/docker-init
grpcfuse        932G  484G  431G  53% /desktop    <---------- HERE it is!
/dev/vda1        59G   40G   17G  71% /etc/hosts
tmpfs           3.0G     0  3.0G   0% /proc/acpi
tmpfs           3.0G     0  3.0G   0% /sys/firmware

SQL> spool /desktop/myfile.txt
SQL> select dummy from dual;

D
-
X

SQL> spool off
SQL> !cat /desktop/myfile.txt
SQL> select dummy from dual;

D
-
X

SQL> spool off

Solution

In order for you container to access a folder on your host-machine, you need to bind mount the host directory using the -v option

docker run -h "oraclehost" --name "oracle" -v /Users/kevin/Desktop/DBM:/DBM -d -p 1521:1521 carloscastillo/rgt-oracle-xe-11g

sqlplus and arrow keys

The tool rlwrap is needed in order for the arrow keys to work. Personally, I never operate a database from inside a container. I always use sqlplus installed on my Mac to access a container database - where I already have rlwrap installed by brew.

Install sqlplus on MacOS (download binaries from oracle.com)

# install on MacOS
brew tap InstantClientTap/instantclient
cp ~/Download/instantclient*.zip /usr/local/Homebrew/Library/Taps/instantclienttap/homebrew-instantclient
brew install instantclient-basic
brew install instantclient-sqlplus
brew install instantclient-tools

brew install rlwrap
Warning: rlwrap 0.45 is already installed and up-to-date.

alias sqlplus
alias sqlplus='rlwrap sqlplus' 

Best of luck!