How to call stored procedure in Oracle from ADF?

Question

I have a stored procedure called test_proc in my oracle database. When I execute Script activity in my pipeline with the query "exec test_proc", it gives "Invalid SQL statement" error. PFB screenshots.

Any solutions for this?

Answer 1

To call a stored procedure in Oracle from Azure Data Factory (ADF) and resolve the "Invalid SQL statement" error, follow these steps:

1. Correct SQL Syntax for Oracle: Oracle does not use EXEC to call stored procedures. Instead, use the CALL statement or use an anonymous PL/SQL block. Example:

   
      CALL test_proc;
   
   

   Or, if your procedure has parameters:

   
      CALL test_proc(param1, param2);
   
   

2. Use PL/SQL Block (if needed): If the procedure requires a PL/SQL block:

   
      BEGIN
   
         test_proc;
   
      END;
   
   

3. Script Activity Setup: Ensure that the "Script Activity" in ADF is configured correctly:
   • Ensure your query is using one of the above syntaxes.
   • Ensure the ADF connection to the Oracle database is valid and points to the correct schema.