1

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

  • I don't see any reason why you cannot do this via Bash scripts and SQL, all scheduled from within cron. If you're already confident with this approach, I don't see the need to do something else, but maybe I'm not fully understanding the question... – John Eisbrener May 18 '17 at 13:15
  • Are you saying execute the SQL within the Bash scripts or hold those queies externally somewhere? – Porkball21 May 18 '17 at 13:54
1

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.

  • This is exactly what I was looking for John! Thank you very much! – Porkball21 May 18 '17 at 15:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.