Redirecting output from an Oracle console application in a named pipe

Question

I'm currently developing a plugin to compile Oracle code in my text editor. The folks that developed SQL Developer recently added a command line version sqlcl. The problem is, this a Java application - and firing this up (jvm) each time I need to do a compilation can be expensive - with some reports of it taking ~20 seconds.

One suggestion I've seen was to use a named pipe, which if I do so manually, appears to work well.

Terminal 1:

mkfifo sqlconsole
tail -f sqlconsole | /opt/sqlcl/bin/sql /nolog

Terminal 2:

echo "conn hr/[email protected]/xe" > sqlconsole

And the statements are run successfully.

The issue with this though, is that in terminal 2, I'm not getting any of the output from terminal 1 (which I want).

..

I found this article about reading output from a named pipe, however even with that, the output from sqlcl is not re-directed (and, as writing this up, seems to have broken input)

#!/bin/bash
#consolereader.sh
trap "rm -f sqlconsole" EXIT

if [[ ! -p sqlconsole ]]; then
    echo "pipe does not exist" >&2
    exit 1
fi

while true
do
    if read line < sqlconsole; then
        if [[ "$line" == 'quit' ]]; then
            break
        fi
        echo $line
    fi
done

Terminal 1:

mkfifo sqlconsole
tail -f sqlconsole | /opt/sqlcl/bin/sql /nolog

Terminal 2:

./consolereader.sh &
echo "conn hr/[email protected]/xe" > sqlconsole

Is there a better approach I can take - such that I can leave sqlcl running in the background, and still get the output in the session I send the commands from?

..

Edit: Trying Germar's solution:

setUpPipes.sh (terminal 1):

#!/bin/bash
rm -f sqlconsole
rm -f sqlconsole_out
mkfifo sqlconsole
mkfifo sqlconsole_out
tail -f sqlconsole | /opt/sqlcl/bin/sql /nolog | tee -a sqlconsole_out

compileOracle.sh (terminal 2):

#!/bin/bash
echo "begin.."
tail -f /home/trent/pipedemo/sqlconsole_out &
echo "about to run connection"
echo "conn hr/[email protected]/xe" > /home/trent/pipedemo/sqlconsole
echo "select * from dual" > /home/trent/pipedemo/sqlconsole
echo "disconnect" > /home/trent/pipedemo/sqlconsole
echo "finished"
exit 0

Answer 1

One approach you could take is to make use of the SPOOL command in your SQL interpreter.

So, start your named pipe as you were already doing so:

mkfifo sqlconsole
tail -f sqlconsole | /opt/sqlcl/bin/sql /nolog

Next, make your SQL script, but this time enabling serveroutput and also spooling to a specified file. In this example, I will just do it to out.txt.

conn hr/[email protected]/xe
SPOOL out.txt    
select * from dual;

set serveroutput on

exec dbms_output.put_line('PROCESS_FINISHED');

SPOOL OFF
disconnect

Here, I've also opted to print a string to the spooled file - PROCESS_FINISHED - as a way to flag when the script has finished, since the SQL script and bash script will be running side by side, with the bash script likely to complete before the script has finished.

With that, then I can make a bash script (atomRunner.sh) to send it to the named pipe:

#!/bin/bash
> out.txt
cat connect.sql > sqlconsole
MAX_TIME=10
scriptStart=$(date -u +"%s")
secondsSince=0

while true; do

    if [[ "${secondsSince}" -ge "${MAX_TIME}" ]] || grep -q "PROCESS_FINISHED" out.txt; then
        break
    fi
    nowDate=$(date -u +"%s")
    secondsSince=$((nowDate-scriptStart))
    sleep 0.1
done

cat out.txt

if [[ "${secondsSince}" -ge "${MAX_TIME}" ]]; then
    echo "Script took longer than expected to complete" >&2
    exit 1
fi

exit 0

Then running:

$ ./atomRunner.sh 

SQL> set serveroutput on
SQL> select * from dual;

D
-
X
SQL> exec dbms_output.put_line('PROCESS_FINISHED')
PROCESS_FINISHED

SQL> SPOOL OFF