I have some SQL scripts that I'm trying to automate. In the past I have used SQL*Plus, and called the sqlplus binary manually, from a bash script.
However, I'm trying to figure out if there's a way to connect to the DB, and call the script from inside of the bash script... so that I can insert
date and make the queries run relative to a certain number of days in the past.
I'm slightly confused. You should be able to call sqlplus from within the bash script. This may be what you were doing with your first statement
Try Executing the following within your bash script:
#!/bin/bash echo Start Executing SQL commands sqlplus <user>/<password> @file-with-sql-1.sql sqlplus <user>/<password> @file-with-sql-2.sql
If you want to be able to pass data into your scripts you can do it via SQLPlus by passing arguments into the script:
Contents of file-with-sql-1.sql
select * from users where username='&1';
Then change the bash script to call sqlplus passing in the value
#!/bin/bash MY_USER=bob sqlplus <user>/<password> @file-with-sql-1.sql $MY_USER
You can also use a "here document" to do the same thing:
VARIABLE=SOMEVALUE sqlplus connectioninfo << HERE start file1.sql start file2.sql $VARIABLE quit HERE