We want to migrate an existing Oracle database on Solaris on-prem to Azure Linux. There are quite a few Batch jobs running on existing on-prem database which are scheduled through ESP scheduler which is an IBM product. As we are moving completely to Azure linux, we would like to schedule the same Oracle Batch jobs from Azure logic apps. Could you please let us know the procedure to implement this, the connectors we can use and the process to be followed ect. Thanks, Vijay

@Vijay I'm not exactly familiar with how Oracle Batch Jobs work but some quick research seems to suggest that you just need to use SQL Queries. If that is the case, you can just use the Oracle Connector's Execute a Query or Execute a Stored Procedure from a Logic App that is triggered using a recurrence schedule . If it's more complex than that, you could use Azure Functions with a Timer Trigger and have custom code to work with Oracle Batch Jobs. Also, if applicable, a simple solution would be to just have a CRON Job on your Linux VM that runs a script.

Azure logic apps for scheduling Batch jobs in oracle database

Accepted answer

Pramod Valavala 20,626 Reputation points Microsoft Employee

2021-07-20T08:28:13.88+00:00

@Vijay I'm not exactly familiar with how Oracle Batch Jobs work but some quick research seems to suggest that you just need to use SQL Queries.

If that is the case, you can just use the Oracle Connector's Execute a Query or Execute a Stored Procedure from a Logic App that is triggered using a recurrence schedule.

If it's more complex than that, you could use Azure Functions with a Timer Trigger and have custom code to work with Oracle Batch Jobs.

Also, if applicable, a simple solution would be to just have a CRON Job on your Linux VM that runs a script.
Please sign in to rate this answer.
Vijay 21 Reputation points

2021-07-20T11:57:03.637+00:00

Thanks Pramod.

What ever you said is correct. Batch jobs need to be run using sql queries which are executed using shell scripts placed in the server.

Can you explain us how Azure functions with a timer trigger would work here?

We can use cron jobs but we have some dependent jobs which is the reason we preferred ESP Scheduler in on premise environment.

But as we are moving/migrating the Oracle database to Azure linux environment, we would like to completely remove the ESP scheduler and schedule the jobs using logic apps.

Please guide us.

Thanks,
Vijay

Pramod Valavala 20,626 Reputation points Microsoft Employee

2021-07-20T13:21:38.933+00:00

@Vijay Azure Functions would trigger based on the set schedule (which itself is a CRON expression) and then you would use an Oracle Client SDK (depending on the language) to run the SQL Queries.

Since this is all code, you can code up any other functionality you require and if you require more complex scenarios, you can even leverage Durable Functions which will allow you to build workflows like Logic Apps but using code.

Vijay 21 Reputation points

2021-07-20T13:43:12.037+00:00

Thanks Pramod,

Let me review the Durable Functions. Will reach out to you if anything else required.

Vijay 21 Reputation points

2021-07-28T11:00:08.387+00:00

Hi Pramod,

Could you please help me if you have any detailed steps to use Azure functions to connect to Linux VM and execute the shell scripts. These shell scripts will in turn connected to the oracle database and run the sql scripts.

My Linux server details:

[oracle@vijayVM ~]$ uname -a
Linux vijayVM 4.14.35-1902.10.7.el7uek.x86_64 #2 SMP Fri Jan 31 12:23:16 PST 2020 x86_64 x86_64 x86_64 GNU/Linux

Reason why I gave the details is that, I saw a blog where Azure functions cannot connect to a Oracle Linux server. Where they can connect to ubuntu, centos etc.

Correct me if I am wrong.

Below are the blogs list:

https://acloudguru.com/hands-on-labs/install-azure-functions-core-tools-on-linux

https://packages.microsoft.com/config/

Pramod Valavala 20,626 Reputation points Microsoft Employee

2021-07-28T12:10:50.997+00:00

@Vijay It would be best to create a new post for this query since its more about connecting to a Linux VM than an Oracle Database. That would make it more discoverable and could engage the wider community.

Anyways, I don't see how there would be a limitation for an Azure Function to connect to an Oracle Linux Server. One way of connecting would be using SSH, for which you can use a library based on the language you plan to use. In case of C#, you could use something like SSH.NET.

Another option would be to create a simple service that runs the script locally but can accept a HTTP Request. This simplifies integration between your function app and the shell script.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Azure logic apps for scheduling Batch jobs in oracle database

0 additional answers

Your answer