Last week, I wrote about how to use bash arrays and the MySQL database to create unit and integration test scripts. While the MySQL example was nice for some users, there were some others who wanted me to show how to write bash shell scripts for Oracle unit and integration testing. That’s what this blog post does.
If you don’t know much about bash shell, you should start with the prior post to learn about bash arrays, if-statements, and for-loops. In this blog post I only cover how to implement a bash shell script that runs SQL scripts in silent mode and then queries the database in silent mode and writes the output to an external file.
To run the bash shell script, you’ll need the following SQL files, which you can see by clicking not he title below:
Setup SQL Files ↓
The actor.sql file:
The film.sql file:
The movie.sql file:
The tables.sql file, lets you verify the creation of the actor, film, and movie tables:
The results.sql file, lets you see join results from actor, film, and movie tables:
The following list_oracle.sh shell script expects to receive the username, password, and fully qualified path in that specific order. The script names are entered manually in the array because this should be a unit test script.
This is an insecure version of the list_oracle.sh script because you provide the password on the command line. It’s better to provide the password as you run the script.
You can run the shell script with the following syntax:
You can then display the results from the output.txt file with the following command:
It will display the following output:
As always, I hope this helps those looking for a solution.