question

anuthereaper avatar image
0 Votes"
anuthereaper asked MartinJaffer-MSFT commented

Ingestion from Oracle using data factory

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
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@anuthereaper did my suggestion help you? If it solved your issue, please mark as accepted answer. If not, please share more details.

0 Votes 0 ·

@anuthereaper if you found your own solution, please share it here with the community.

0 Votes 0 ·

1 Answer

MartinJaffer-MSFT avatar image
0 Votes"
MartinJaffer-MSFT answered

Hello @anuthereaper 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);

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.