Bash Oracle connection execute multiple queries

Question

I am new in programming oracle in bash and I have to create a function which reads a CSV and updates a table in an Oracle DB, which is working so far. I have used the tutorial from here: https://zwbetz.com/connect-to-an-oracle-database-and-run-a-query-from-a-bash-script/

The code is:

export ORACLE_SID=$OSID
export ORACLE_HOST=$HOST
export ORACLE_PORT=$PORT
export ORACLE_DATABASE=$DB
export ORACLE_USERNAME=$USER 
export ORACLE_PASSWORD=$PW

while IFS="|" read -r col1, col2, col3; do
    sql="INSERT INTO my_table(col1, col2, col3)
                VALUES ('$col1',
                        '$col2', 
                        '$col3');"
    echo -e "SET PAGESIZE 0
 SET FEEDBACK ON
 $sql" | 
    sqlplus -S -L "$ORACLE_USERNAME/$ORACLE_PASSWORD@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$ORACLE_HOST)(PORT=$ORACLE_PORT))(CONNECT_DATA=(SERVICE_NAME=$ORACLE_DATABASE)))"
    
done < $INPUT_ADD

The query is send with this commando:

echo -e "SET PAGESIZE 0
 SET FEEDBACK ON
 $sql" | 
    sqlplus -S -L "$ORACLE_USERNAME/$ORACLE_PASSWORD@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$ORACLE_HOST)(PORT=$ORACLE_PORT))(CONNECT_DATA=(SERVICE_NAME=$ORACLE_DATABASE)))"

What I am not sure about is, is sqlplus always opening for each execution of the query a new DB connection? And if so, is it posible to leave the connection open and close the connection when the while loop finished?

Thank you for your help!

question from:https://stackoverflow.com/questions/66060884/bash-oracle-connection-execute-multiple-queries

Answer 1

You can generate the sql-file containing all your inserts in the while loop and at the end, outside the loop, call sqlplus with the file as input. Now you only connect once.

A more one-liner *nix approach would be:

awk '{transform-magic}' file.csv | sqlplus <connect-info>

But I rather recommend existing tools for the job.

• sqlldr - SQL*Loader
• https://github.com/csv2db/csv2db
• Oracle External Tables (view the csv file as an Oracle table and use SQL for DML)

Best of luck!