How to call a Oracle stored procedure from Logic apps

Question

Hi Team,

Thank you very much for the time spend on this.

so , Here is the task.

I am planning to build a integration in Logic apps to Connect oracle On Prem database and Call

the Oracle Stored procedure (Oracle Package) including the passing parameter.

however , i can't see this is a feasible option in logic apps for this but even i tried to call only a single procedure without parameter but still i am not getting any success and also not getting error , its getting timeout.

so , if anyone has any expertise in this would be great help to progress help and again thank you very much for the help.

Answer 1

Hello @Pawar, Mahendra !

Welcome to Microsoft QnA!

In order to access Oracle On Premises you need the Logic Apps Data Gateway :

https://learn.microsoft.com/en-us/azure/logic-apps/logic-apps-gateway-install

You have to to log in to the gateway with the same Azure account that you will use in Logic Apps.

Once connected, you should be able to see available operations for the Oracle connector, such as "Execute a SQL Query".

If direct execution of the stored procedure isn't available, you might have to use a SQL query to call it. For instance:

sql

    **BEGIN**

      **your_package_name.your_procedure_name(your_parameter_value);**

    **END;**

Below are some suggestions for the Timeout Issue:

•     Ensure that the Oracle database allows connections from the server where the On-Premises Data Gateway is installed.
  

•     The On-Premises Data Gateway does encrypt data that travels between on-premises and Azure.
  

• Monitoring and Logs:
  

•     Use Azure Monitor and Logic Apps' built-in logging to diagnose any issues.
  

•     Also, check logs and monitoring tools on your Oracle server. They might provide insights if the call is reaching the Oracle server and if there are any errors.
  

I hope this helps!

Kindly mark the answer as Accepted and Upvote in case it helped!

Regards

Answer 2

@Pawar, Mahendra Thanks for reaching out.

You can refer to this document to Connect Oracle Database from Azure Logic Apps. The document covers the Prerequisites and you need to leverage the OPDG as documented.

The oracle connector actions are documented here and there is known limitation of the connector.

Execute stored procedure action does have the Parameters list property that you can leverage to pass the input parameter to your oracle store procedure.

however , i can't see this is a feasible option in logic apps for this but even i tried to call only a single procedure without parameter but still i am not getting any success and also not getting error , its getting timeout.

Have you tried to add parameters or didn't see the Parameters list option on your execute stored procedure? Can you share the screenshot? As per the document it should have the parameter to pass the input value. It looks like to execute and return the error store procedure might be taking more than 110 sec as if any Oracle query or stored procedure execution time exceeds 110 seconds, the action will timeout as documented in know issue and limitation.

Let me know if you have any queries or concerns.

Please "Accept Answer" if the answer is helpful so that it can help others in the community.