Ingestion from Oracle using data factory

Question

Hi,

We have an Oracle 12c database in our vendor Data center. We are trying to fetch/SELECT data from the DB and move it to ADLS using data factory. The vendor has already installed SHIR on their data center and we have verified that the SHIR is online.
However, the vendor has not provided direct access to the table or view. They have asked us to execute a stored procedure to fetch the data. What is the best way to execute a stored procedure on an Oracle DB to SELECT a bunch of data using ADF? It doesn't seem to works the same way as MS SQL.

Regards,
Anupam

Answer 1

Hello anonymous user and welcome back to Microsoft Q&A.

Please tell me if I understand your ask correctly.

You are able to connect to an Oracle source correctly, but for some reason you are required to use a stored procedure rather than loading the whole table or a select query.

When used in a copy activity, the Azure SQL server has options for table, query, and stored procedure.

However, when used in a copy activity, the Oracle has options only for table and query. There is no stored procedure option.

So, you want to know how to execute a stored procedure from an ADF copy activity.

I do not have an Oracle to test against, but I do have a suggestion. Use the query option, but instead of writing a select statement, write an execute statement.

execute my_package(my_parameter);