How to get return value from Oracle stored procedure?

Question

Hi,

I'm trying to use a Script activity to call a Stored Procedure which exists in my on-prem Oracle database. I used script parameters to pass the inputs which works fine. But when I add my output parameter to the script activity, it is returning a ORA-03146 error. The exact error message is: "ORA-03146: invalid buffer length for TTC field".

Can anyone help me resolve this?

Answer 1

Hello Bansal, Nimish,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand that you are having "ORA-03146: invalid buffer length for TTC field" error message after adding output parameter to the script activity.

In ADF's Script activity, this issue can occur when the output parameter's size or type doesn't align with what the Oracle stored procedure expects. What you need to do is to make sure that the output parameter size is handled correctly and aligns with ADF's Script activity requirements while accounting for Oracle's specifics. The following steps will surely help to resolve it:

1. Make sure the output parameter in the stored procedure has a defined size if it's a variable-length type like VARCHAR2 or NVARCHAR2 to Validate Stored Procedure Definition. For example:

     CREATE OR REPLACE PROCEDURE my_proc (
         in_param IN VARCHAR2,
         out_param OUT VARCHAR2
     ) IS
     BEGIN
         out_param := 'Sample Output';
     END;

Specify an appropriate maximum size, e.g., VARCHAR2(100).

2. When defining output parameters in the Script activity, explicitly set a size that matches or exceeds the size in the stored procedure. In the Script activity's Parameters section, add the output parameter with a sufficient size. For instance:

     {
         "name": "output_param",
         "type": "String",
         "size": 500
     }

3. Make sure the integration runtime hosting your Oracle connection uses an up-to-date and compatible Oracle JDBC driver (e.g., ojdbc8.jar for Oracle 12c or above). You can download the latest driver from- https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html
4. In the Script activity, configure the stored procedure call to use named parameters like this, :out_param corresponds to the output parameter. For example:

     BEGIN
         my_proc(:in_param, :out_param);
     END;

5. If you're using a Self-hosted Integration Runtime to connect to your on-prem Oracle database, you will need to look into it that:

• Proper network connectivity between ADF and the database.
• Adequate memory allocation for large data buffers.

6. Test the stored procedure using a SQL client like SQL Plus, SQL Developer, or a direct JDBC connection to verify that the issue is specific to the ADF implementation:

     CallableStatement stmt = conn.prepareCall("{call my_proc(?, ?)}");
     stmt.setString(1, "input_value");
     stmt.registerOutParameter(2, Types.VARCHAR);
     stmt.execute();
     String output = stmt.getString(2);

To troubleshoot further, enable debugging or logging in Azure Data Factory (ADF) to capture the exact query and parameter details being executed. If the issue continues, review the Oracle database logs for more information on the error, and check for any known issues or patches related to your Oracle database version or JDBC driver. Forget not to use the links in additional reading and this: https://support.oracle.com/knowledge/Middleware/2729562_1.html

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.