Automating Oracle Stored Procedures

Our users have a lot of love for Oracle. Whether it’s the platform’s unique locking structure, caching, or high availability, it handles significant transaction loads at the world’s most prominent brands. But, automating Oracle Stored Procedures can be a challenge, especially when you’re building enterprise workflows that span more applications than just Oracle.

Common methods we see organizations employing to automate Oracle Stored Procedures include:

Shell scripts running SQLPlus scheduled in Cron

and

Windows Batch jobs connecting through an ODBC driver

While these methods work fine, and can be relatively simple to implement, we have to question their security and resilience. Both of these methods store the username and password in plaintext in the job definition. This is a huge concern to auditors and security experts. Furthermore, network hiccups can cause disruptions in an ODBC connection, interrupting your process. And crontabs can be difficult to schedule and maintain.

Oracle Stored Procedure Execution Method

Separation of security and logic is integral to enterprise automation and was a key driver of the release of our OracleStoredProcudure Execution Method, available as of JAMS V6.4. JAMS offers users a fill-in-the-blank form to define the Stored Procedure’s scheduling logic (time, recurrence, triggers, dependencies, etc.) while providing only a reference to the credentials required to run the job. This reference architecture is important because it lets a variety of team members submit, edit, and manage a job, without ever exposing usernames and passwords. The process of automating Oracle Stored Procedures in JAMS is further streamlined by dynamic lookup. As you begin the process of defining an Oracle job, the JAMS Oracle job scheduler immediately pulls in all the available stored procedures currently stored in the database, as well as any of their defined parameters.

Connect to Non-Oracle Jobs

Once an Oracle Stored Procedure is scheduled in JAMS, you have the ability to integrate it into broad reaching enterprise workflows. You can also apply complex scheduling logic such as custom calendars, date/time exceptions and recurrence, that can be difficult to script with either PL/SQL or Batch and shell scripts. Tie in our CLI or REST API and you can easily (and securely) pass data between multiple databases. The possibilities are near limitless.

Oracle in the Cloud?

What if your database is hosted in the cloud? It doesn’t matter. For maximum flexibility we’ve designed JAMS to be indifferent to the location of the Oracle database server. For example, Oracle Stored Procedures can be run in an on-premise datacenter or on RDS on AWS, with identical results.

Start Automating Your Oracle Stored Procedures with JAMS