Automation approach to executing Shell scripts and performing checks on an Oracle DB I am in the process of producing an automation framework that will:


Check Oracle tables for entries, such as IDs and status numbers
Log onto a UNIX box via Putty and switch to a directory where the scripts are located
Execute Shell scripts
Perform further checks on Oracle tables depending on values
Repeat above actions (conditional)
Log/notify users of errors during the process above


Would I be able to achieve this purely using Bash/Shell and SQL, or would I need to possibly add another language into the mix?

I was thinking of building it in a modularized fashion.

Thanks
If it were me, I would put everything in the database that I can.  For instance, all SQL logic should go into packages/procedures, and I would control the execution of these routines via DBMS_SCHEDULER jobs if possible.  Notifications based off of results from these routines can be sent via UTL_MAIL.  Putting this in the database allows you to lock it down with proper security.

If you truly need to execute Shell Scripts (steps 1 and 2 above) outside of the database, there are various ways you can do this from a PL/SQL routine, but they get hard to manage if things don't go exactly as planned, so the shell script needs to be able to handle errors gracefully.  Just try to minimize security issues by properly locking down any external files being called, etc.  I'm not a fan of calling shell scripts from PL/SQL code, but ultimately your process and comfort level is going to dictate your approach.

There are other approaches too, especially if you have money to spend.  Another option would be to look into enterprise-level scheduler applications.  There are a fair number out there, and if this process is complicated enough or starts affecting other processes within your enterprise these can help make sense of the chaos.

At the end of the day, it'll come down to your comfort level with the technology(ies) and the amount of resources (e.g. time and money) you can spend that will determine your best approach.