How to Call a ORACLE DATABASE PACKAGE from Azure data factory and receive the output of that package.

Question

How to Call a ORACLE DATABASE PACKAGE from Azure data factory and receive the output of that package.

ADFUser 1

Team

I need to call a ORACLE DATABASE PACKAGE from azure data factory and use its output in other activities but i am not able to call a PACKAGE. Inside the package there are many stored procedures written so i need to call a specific stored procedure inside a Package .

Please provide the script or way to do this.

Thanks in Advance

Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2025-03-05T11:52:09.8033333+00:00

can you be more specific? Do you wish to trigger the SP within oracle database and copy its result for some other aspects.
ADFUser 1 Reputation point

2025-03-05T11:58:20.32+00:00

Need to call a Oracle database procedure which is inside a PACKAGE.
Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2025-03-05T12:01:50.34+00:00

So you mean to say you have a package that contains the SP logic and that SP is not present within the Oracle database. Post reading the definition from the package, you want the SP logic to be executed directly within the database without that SP not being physically present within the database?
Anonymous

2025-03-06T00:44:17.06+00:00

@ADFUser

Azure Data Factory (ADF) does not natively support direct invocation of Oracle packages. To execute a specific stored procedure within an Oracle package and retrieve its output, you can utilize the Lookup or Script activity in ADF to run an anonymous PL/SQL block. This approach allows you to call the desired procedure and handle its output appropriately.
To execute the package procedure and retrieve its output, use a Lookup activity in ADF. Write a PL/SQL block that declares a variable, calls the procedure, and returns the output using SELECT ... FROM DUAL, allowing ADF to capture the result.
Please refer the below links
https://learn.microsoft.com/en-us/answers/questions/2120741/how-to-call-stored-procedure-in-oracle-from-adf
https://stackoverflow.com/questions/63204428/how-to-call-oracle-stored-procedure-from-azure-data-factory-v2?
Anonymous

2025-03-06T23:49:40.7633333+00:00

@ADFUser
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Anonymous

2025-03-07T22:02:15.2833333+00:00

@ADFUser
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
LEIGHTON Olivia 0 Reputation points

2026-03-13T00:17:36.3333333+00:00
The way I called an Oracle Package from adf was using adf Script. Then you can connect to your linked service Oracle, and call the package. Within the script make sure you have Query ticked.

I am not looking for output of this procedure other than "Succeeded" or "Failed" so I am assuming a lookup after the Script will most likely be required.

e.g.
Output:
BEGIN SCHEMA_NAME.PACKAGE_NAME.PROCEDURE_NAME(p_target_table => 'SCHEMA_NAME.TABLE_NAME', p_staging_table => 'SCHEMA_NAME.STG_TABLE_NAME', p_business_keys => 'SEQUENCE_NUMBER, PARTY'); END;

What was written in the Script Query:

@concat( 'BEGIN ', pipeline().parameters.oracleSchema, '.PACKAGE_NAME.PROCEDURE_NAME(', 'p_target_table => ''', pipeline().parameters.oracleSchema, '.', pipeline().parameters.TargetTable, ''', ', 'p_staging_table => ''', pipeline().parameters.oracleSchema, '.', pipeline().parameters.stagingTable, ''', ', 'p_business_keys => ''', join(pipeline().parameters.businessKeys, ', '), '''', '); ', 'END;' )