Automate SQL

This project creates a way to automate SQL queries in a remote server (or locally). The main perl script run_job.pl is used to run the SQL code and send out the email. Each "job" consists of a pair of files with the same names except for the file type. One file ends with .sql and contains the actual SQL code to be executed. The other file ends with .mail and lists the recipients for emails generated by the job. All jobs are located in the parms folder.

For instance the job to check flight records has the following two files:

flights.sql and flights.mail

Scheduling of automated jobs can be done with Unix crontab. This method is especially useful if you have several dashboards that you are maintaining and you need regular updates to tables. (crontab -e to edit).

This is an example of a crontab that will run a job that updates the table everyday at 9am adn on the 20th of the month at 8am:

# run every morning at 9am
#00 09 * * *    bash -lc "/home/andrew/automate_sql/perl run_job update_table"

# run every month at 8am on the 20th - Pull new client data
#00 08 20 * *    bash -lc "/home/andrew/automate_sql/perl run_job monthly_client_data"

.sql file

The .sql file contains your SQL code. Make sure your SQL code is error free before adding it to the parms folder. Comments are allowed and will be ignored. The perl script can handle seperate queries as it breaks the SQL file down by ;. If you do not add the ; at the end of your query, you will run into errors. Variables in the SQL code does not work. If you need variables, you can edit the perl script and modify the SQL code to accept the perl variable.

.mail file

The .mail indicates mail recipiants to the status of the job. Lines that start with # will be ignored and and have one email per line.

Here is an example of the .mail file:

# this line will be ignored
andrew@gmail.com
andrew@yahoo.com
# andrew@outlook.com

Create a .pw_file

The Perl script will look for your database credentials in the hidden .pw_file file. Lines that begin with # will be ignored. Make sure the first entree is the username and the next is password. Seperate this by comma with no space. This is what it will look like:

# This file is used to place log in info
# lines starting with this hashtag will be ignored
# separate elements with a comma with no space
# first element is db username - this is for db access
# second element is db password - this is for db access

username,password

Make sure that only you have access to this file. You can cant the file permission so that only you read and write to it:

chmod 600 ~/.pw_file

Example of a success email

This is an example of a simple query

** /home/andrew/automate_sql/run_job.pl test completed without errors **

Query number 0 was executed successfully:
select distinct(flights) from airline_data.flight_performance
    where flight_number <> '00000' and flight_date > date'2023-03-31'
Number of rows from this query: 10637; Number of columns from this query: 1; Execution time: 0.23 seconds

Total number of SQL queries executed: 1
Total job duration: 0.23 seconds

Completion Time: 11:45:57
Failure Date: 2024-11-05

This is an example of a query that drops several tables

** /home/andrew/automate_sql/run_job.pl create_drop completed without errors **

Query number 0 was executed successfully:
drop table andrew.my_table_test.my_table_test
1 tables dropped; Execution time: 0.03 seconds

Query number 1 was executed successfully:
drop table andrew.my_table_test.my_table_test_2
2 tables dropped; Execution time: 0.02 seconds

Total number of SQL queries executed: 2
Total job duration: 0.05 seconds
Completion Time: 23:53:26
Failure Date: 2024-11-05

Example of a failure email

This failed because it cannot use SQL defined variables

/home/andrew/automate_sql/run_job.pl variable FAILED 

Could not execute SQL statement: ORA-06550: line 2, column 20:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   * & = - + ; < / > at in is mod remainder not rem
   <an exponent (**)> <> or != or ~= >= <= <> and or like like2
   like4 likec between || multiset member submultiset (DBD ERROR: error possibly near <*> indicator at char 27 in 'DECLARE
    comp NUMBER := <*>9')
SQL: DECLARE
    comp NUMBER := 9

 Failure Time: 00:02:10
 Failure Date: 2024-11-05

This failed because I do not have access to this table

/home/andrew/automate_sql/run_job.pl no_access FAILED 

Could not prepare SQL statement: ORA-00942: table or view does not exist (DBD ERROR: error possibly near <*> indicator at char 19 in 'select * from FLIGHTS.<*>BUDGET')
SQL: select * from FLIGHTS.BUDGET

 Failure Time: 11:51:43
 Failure Date: 2024-11-05