Azure Logic Apps - Oracle Connector - Execute a query-CodePudding

I am working on an Azure Logic Apps integration which insert data into an on-premise Oracle Database. I can successfully run the Logic Apps integration and insert the data into our Oracle table (a custom staging table).

I would like to run a truncate on that table before inserting the new data. I added the Oracle - Execute a Oracle Query component and then provided the following query: TRUNCATE TABLE .<TABLE_NAME>;

After deploying to Azure, the integration failed on the Execute a Oracle Query with error:

"message": "BadGateway",
"innerError": {
"status": 502,
"message": "Unable to find the requested .Net Framework Data Provider. It may not be installed.\r\n inner exception: Unable to find the requested .Net Framework Data Provider. It may not be installed....",

If I remove the Execute a Oracle Query component, the Oracle Insert row component works fine. I am not sure why it says the gateway may not be installed. I suspect it's a badly trapped error message.

Is that how the Execute a Oracle Query is supposed to be used? Otherwise, how can I execute the truncate or how can I debug this error message?

Thanks!

CodePudding user response：

The Oracle Connector for Azure Logic Apps does not support DDL. The exception you are getting is the generic one in Azure Logic Apps. I honestly believe the exception handler module is not very reliable here, because it usually goes to the gateway issue when the problem has no relation whatsoever. However, you have the option to overcome this limitation by using a call to an stored procedure.

In Oracle

create or replace procedure pr_trc_table ( ptab in varchar2 )
is 
begin 
  execute immediate 'truncate table '||ptab||' ' ;
exception when others then raise;
end;
/

This Oracle Connector developed by Microsoft has a lot of limitations, which is normal as they try to ensure you use their own Azure SQL Service.

When invoking a Stored Procedure on an Oracle server, we have the following limitations:
OUT parameters are not supported currently.
Return value is not available since Oracle Stored Procedure does not return any result.
Oracle Functions are not supported so they are not listed in the UI.
The response size limit is 8MB.
The request size limit is 2MB.
Oracle native query is supported with the following limitations:
RefCursor is not supported.
OUT parameters are not supported.
Only one result set can be returned.
Gateway version 3000.63.4 (October release) or later is required.
Minimum supported version for Oracle Data Access Client is version 11

Besides, if any oracle query or stored procedure execution time exceeds 110 seconds, the action will timeout. Insert and update to a table does not return the full item, it returns only the input properties for the operation. A Primary Key is required to get deterministic paging result for GetRows operation.