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\n SET FEEDBACK ON\n $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\n SET FEEDBACK ON\n $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!

Bjarte Brandt · Accepted Answer · 2021-02-05 13:00:17Z

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!

answered Feb 5, 2021 at 13:00

Bjarte Brandt

4,5212 gold badges28 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pmdba Over a year ago

Also, placing username/password and other connection info in your environment variables is very bad practice. If you are using sqlplus, then you should be able to use an Oracle Wallet to protect those credentials and other connection details. See here: oracle-base.com/articles/10g/…

Martin · Accepted Answer · 2021-02-08 16:19:47Z

0

Thank you very much for the tip creating a file containing the query. At the end I was able to do it the following way with the HERE command:

sqlplus -L "$ORACLE_USERNAME/$ORACLE_PASSWORD@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$ORACLE_HOST)(PORT=$ORACLE_PORT))(CONNECT_DATA=(SERVICE_NAME=$ORACLE_DATABASE)))" << HERE
start tmp_query.sql
quit
HERE

answered Feb 8, 2021 at 16:19

Martin

1,2863 gold badges9 silver badges9 bronze badges

Collectives™ on Stack Overflow

Bash Oracle connection execute multiple queries

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related