how to execute docker sqlplus query inside bash script?

Question

On Ubuntu 21.04, I installed oracle dtb from docker, works fine I am using it for sql developer but now I need to use it in shell script. I am not sure if it can work this way, or is it some better way. I am trying to run simple script:

#!/bin/bash
SSN_NUMBER="${HOME}/bin/TESTS/sql_log.txt"
select_ssn () {
        sudo docker exec -it oracle bash -c "source /home/oracle/.bashrc; sqlplus username/password@ORCLCDB;" <<EOF > $SSN_NUMBER
        select SSN from employee
        where fname = 'James';
        quit
EOF
}

select_ssn

After I run this, nothing happens and I need to kill the session. or

the input device is not a TTY

is displayed

The last letter in SSN already stands for "number" I believe. Probably [prefer lowes case for your private variables.](https://stackoverflow.com/questions/673055/correct-bash-and-shell-script-variable-capitalization) — tripleee, May 20 '21 at 17:19
Typically on Ubuntu your user should be a member of the `docker` group, and not need or want to use `sudo` to run Docker commands. (On e.g. Red Hat the approach is to require `root` for Docker access.) — tripleee, May 20 '21 at 17:23
Does `sqlplus` allow you to specify the query on the command line? Is the `source` command really necessary? — tripleee, May 20 '21 at 17:34

tripleee · Accepted Answer · 2023-01-11T12:44:21.717

Specifying a here document from outside Docker is problematic. Try inlining the query commands into the Bash command line instead. Remember that the string argument to bash -c can be arbitrarily complex.

docker exec -it oracle bash -c "
    source /home/oracle/.bashrc
    printf '%s\n' \\
        \"select SSN from employee\" \\
        \"where fname = \'James\'\;\" |
    sqlplus -s username/password@ORCLCDB" > "$SSN_NUMBER"

I took out the sudo but perhaps you really do need it. I added the -s option to sqlplus based on a quick skim of the manual page.

The quoting here is complex and I'm not entirely sure it doesn't require additional tweaking. I went with double quotes around the shell script, which means some things inside those quotes will be processed by the invoking shell before being passed to the container.

In the worst case, if the query itself is static, storing it inside the image in a file when you build it will be the route which offers the least astonishment.

Thanks, works well, can you please explain function of these "\" and "\\" in query ? — xmilan365, May 20 '21 at 18:34
The first backslash escapes these literal characters inside the double quotes. The arguments to `printf` are quoted strings, each one becomes a line of query which gets piped to `sqlplus` on standard input. — tripleee, May 21 '21 at 03:13

how to execute docker sqlplus query inside bash script?

1 Answers1