How to use sqlldr on Oracle database inside a docker container?

Question

I installed oracle db version 19c in my docker environment with the following command:

docker  run --name oracle19c --network host -p 1521:1521 -p 5500:5500
 -v /opt/oracle:/u01/oracle oracle/database:19.3.0-ee

Then I connect to it with:

docker exec -ti oracle19c sqlplus system/oracle@orclpdb1
SQL>

Then I setup my database. Afterwards I want to import dummy data from a tbl file so I exit sqlplus and I use the command:

sqlldr userid=system control=/home/userhere/sql_loader/control.ctl log=sf1customer.log

and get sqlldr: not found

I don't have much experience with Docker, but my research leads to me to believe that SQL *Loader does not come with the docker image. However, I do not know how to extend the image or where exactly I would call SQL *Loader even if I did. I am on a Ubuntu server and any help would be appreciated.

Answer 1

SQL*Loader is in the image - but the docker container is separate from your host OS, so ubuntu doesn't know any of the files or commands inside it exist. Any commands inside the container should be run as docker commands. If you try this, it should connect to your running container and print the help page:

docker exec -ti oracle19c sqlldr

Since you're running this command on the docker container, sqlldr doesn't have access to any of your host OS's files unless you specifically granted them to the container. But good news - when you started the database with docker run, that's what the -v /opt/oracle:/u01/oracle part of the command did - it mapped /opt/oracle on your Ubuntu filesystem to /u01/oracle in the docker container. So any files that you put in /opt/oracle will be available in the container under /u01/oracle.

So you'll need to do a couple things:

1. Your control.ctl file, log file, and whatever data file you're using need to be accessible to the container. Either move them to /opt/oracle or shutdown your database container and restart it with something like -v /home/userhere/sql_loader:/u01/oracle in the command.
2. You might also need to edit your control.ctl file to make sure that it doesn't reference any file paths on your host OS. Either use relative paths (./myfile.csv) or absolute paths with the container's filesystem (/u01/oracle/myfile.csv)

Now you should be able to run sqlldr on the container, and it should be able to access your data files.

docker exec -ti oracle19c sqlldr userid=system control=/u01/oracle/control.ctl log=/u01/oracle/sf1customer.log

Edit: Oh, I should mention - as an alternative, if you download and install the Oracle Instant Client in Ubuntu, you could run sqlldr locally in Ubuntu, and connect to the docker container over the network as a "remote" database:

sqlldr system@localhost:1521/orclpdb1 control=/home/userhere/sql_loader/control.ctl log=sf1customer.log

That way you don't have to move your files anywhere.