Ingestion from Oracle using data factory

Chand, Anupam SBOBNG-ITA/RX 451 Reputation points
2021-06-30T06:58:07.777+00:00

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

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,464 questions
{count} votes

1 answer

Sort by: Most helpful
  1. MartinJaffer-MSFT 26,011 Reputation points
    2021-06-30T22:54:27.137+00:00

    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);

    0 comments No comments