Forum Discussion

mjain91515's avatar
mjain91515
Copper Contributor
Sep 20, 2024

What is the way to use OUTPUT parameter for an Oracle Stored procedure in ADF pipelines?

I have a oracle database package and i am trying to call a stored procedure inside that package. The procedure has a OUT parameter which we want to use in the activities further in ADF pipelines. Bu...
timsburke's avatar
timsburke
Copper Contributor
Nov 25, 2024

mjain91515... did you ever find a solution for this?  If so, can you share?  TIA

SanjayGanvkar's avatar
SanjayGanvkar
Copper Contributor
Feb 22, 2025

Reply for anyone who might have a similar query. Use the Script parameter section in the script activity with a variable of direction output

  • timsburke's avatar
    timsburke
    Copper Contributor
    Feb 25, 2025

    Hi SanjayGanvkar...I tried that but was getting errors.  Do you have an example of something that worked for you?  Thanks in advance for your help

    • SanjayGanvkar's avatar
      SanjayGanvkar
      Copper Contributor
      Feb 26, 2025
      For whatever reason, my reply with a complete code is not being published.
      Either the bots have marked it as unprintable :) and  rejected it, or there is a delay in the approval process.
      Below is a working snippet, hoping it gets through. The DB/Pipleine portion has 3 examples ( in,in-out,function return value)
       
      ----------- Oracle Database Package -------------------
      CREATE OR REPLACE PACKAGE MYSCHEMA.test_adf_param  AS
      PROCEDURE adf_param_out( p_out OUT VARCHAR2);
      PROCEDURE adf_param_in_out( p_in_out IN OUT VARCHAR2);
      FUNCTION adf_param_fn RETURN VARCHAR2;
      END test_adf_param;
       
      CREATE OR REPLACE PACKAGE BODY MYSCHEMA.test_adf_param IS
      -- Returns value via the OUT route
          PROCEDURE adf_param_out(p_out OUT VARCHAR2) IS
          BEGIN
              p_out := 'This is an out example';
          END adf_param_out;
      -- Takes in a value, processes it and returns the final value
          PROCEDURE adf_param_in_out(p_in_out IN OUT VARCHAR2) IS
          BEGIN
              p_in_out := p_in_out || ' This is an in-out example';
          END adf_param_in_out;    
      -- Returns a value from a function
          FUNCTION adf_param_fn RETURN VARCHAR2 IS
          BEGIN
              RETURN 'This is a function return call';
          END adf_param_fn;    
       
      END test_adf_param;
       
      ----------- Pipeline -------------------
      Three pairs of Script/SetVariable examples, where the script executes the Oracle package, and setvariable holds the return value.
      You can inspect the variables for the return value.String lengths are fixed to 80. Take not of the ? signifying the placeholders
       
      PipeLine Name : PL_DB_PARAM_EXAMPLE
      PipeLine Variables; v_out,v_in_out,v_fn_retval   ( All Strings )
       
      Method 1: Script Activity (ScriptProcedureOutParam) -> Set Variable (SetOut)
      -------------------------------------------------------------------------------------
      name": "ScriptProcedureOutParam",
      activity: Script
      { "parameters": [ {  "name": "p_out",  "type": "String",  "value": "",  "direction": "Output",  "size": "80" } ], 
      "type": "NonQuery", "text": "BEGIN MYSCHEMA.test_adf_param.adf_param_out(p_out => ?); END;" }
       
      "name": "SetOut",
      "type": "SetVariable",
      "variableName": "v_out", "value": "@activity('ScriptProcedureOutParam').output.outputParameters.p_out",
       
      Method 2: Script Activity (ScriptProcedureInOutParam) -> Set Variable (SetInOut)
      -------------------------------------------------------------------------------------
      name": "ScriptProcedureInOutParam",
      activity: Script
      { "parameters": [ { "name": "p_in_out", "type": "String", "value": "ToBeAppended", "direction": "InputOutput", "size": "80" } ], 
      "type": "NonQuery", "text": "BEGIN MYSCHEMA.test_adf_param.adf_param_in_out(p_in_out => ?); END;" }
       
      "name": "SetInOut",
      "type": "SetVariable",
      "variableName": "v_in_out", "value": "@activity('ScriptProcedureInOutParam').output.outputParameters.p_in_out",
       
      Method 3: Script Activity (ScriptFunctionRetVal) -> Set Variable (SetFnRetVal)
      -------------------------------------------------------------------------------------
      name": "ScriptFunctionRetVal",
      activity: Script
      { "parameters": [ { "name": "p_fn", "type": "String", "value": "", "direction": "Output", "size": 80 } ], 
      "type": "NonQuery", "text": "BEGIN ?:=MYSCHEMA.test_adf_param.adf_param_fn; END;" }
       
      "name": "SetFnRetVal",
      "type": "SetVariable",
      "variableName": "v_fn_retval", "value": "@activity('ScriptFunctionRetVal').output.outputParameters.p_fn",
    • SanjayGanvkar's avatar
      SanjayGanvkar
      Copper Contributor
      Feb 26, 2025

      Below are 3 examples ( in,in-out and a function return ). Look  for the parameter section in the Script Activity and also the expression in the SetVariable Activities to extract the return values

      Replace the MYSCHEMA,MY_LINKED_SERVICE,MYCONNECTSTRING as appropriate to your env
       
      ----------- Oracle Database Package -------------------
      CREATE OR REPLACE PACKAGE MYSCHEMA.test_adf_param  AS
      PROCEDURE adf_param_out( p_out OUT VARCHAR2);
      PROCEDURE adf_param_in_out( p_in_out IN OUT VARCHAR2);
      FUNCTION adf_param_fn RETURN VARCHAR2;
      END test_adf_param;
      /
       
      CREATE OR REPLACE PACKAGE BODY MYSCHEMA.test_adf_param IS
      -- Returns value via the OUT route
          PROCEDURE adf_param_out(p_out OUT VARCHAR2) IS
          BEGIN
              p_out := 'This is an out example';
          END adf_param_out;
      -- Takes in a value, processes it and returns the final value
          PROCEDURE adf_param_in_out(p_in_out IN OUT VARCHAR2) IS
          BEGIN
              p_in_out := p_in_out || ' This is an in-out example';
          END adf_param_in_out;    
      -- Returns a value from a function
          FUNCTION adf_param_fn RETURN VARCHAR2 IS
          BEGIN
              RETURN 'This is a function return call';
          END adf_param_fn;    
       
      END test_adf_param;
      /
      ----------- Pipeline -------------------
      {
          "name": "PL_DB_PARAM_EXAMPLE",
          "properties": {
              "activities": [
                  {
                      "name": "ScriptProcedureOutParam",
                      "description": "Example for an out param call",
                      "type": "Script",
                      "dependsOn": [],
                      "policy": {
                          "timeout": "0.12:00:00",
                          "retry": 0,
                          "retryIntervalInSeconds": 30,
                          "secureOutput": false,
                          "secureInput": false
                      },
                      "userProperties": [],
                      "linkedServiceName": {
                          "referenceName": "MY_LINKED_SERVICE",
                          "type": "LinkedServiceReference",
                          "parameters": {
                              "l_oracle_connect_string": {
                                  "value": "MYCONNECTSTRING",
                                  "type": "Expression"
                              }
                          }
                      },
                      "typeProperties": {
                          "scripts": [
                              {
                                  "parameters": [
                                      {
                                          "name": "p_out",
                                          "type": "String",
                                          "value": "",
                                          "direction": "Output",
                                          "size": "80"
                                      }
                                  ],
                                  "type": "NonQuery",
                                  "text": "BEGIN MYSCHEMA.test_adf_param.adf_param_out(p_out => ?); END;"
                              }
                          ],
                          "scriptBlockExecutionTimeout": "02:00:00"
                      }
                  },
                  {
                      "name": "SetOut",
                      "type": "SetVariable",
                      "dependsOn": [
                          {
                              "activity": "ScriptProcedureOutParam",
                              "dependencyConditions": [
                                  "Succeeded"
                              ]
                          }
                      ],
                      "policy": {
                          "secureOutput": false,
                          "secureInput": false
                      },
                      "userProperties": [],
                      "typeProperties": {
                          "variableName": "v_out",
                          "value": {
                              "value": "@activity('ScriptProcedureOutParam').output.outputParameters.p_out",
                              "type": "Expression"
                          }
                      }
                  },
                  {
                      "name": "ScriptProcedureInOutParam",
                      "description": "Example for an in out param call",
                      "type": "Script",
                      "dependsOn": [],
                      "policy": {
                          "timeout": "0.12:00:00",
                          "retry": 0,
                          "retryIntervalInSeconds": 30,
                          "secureOutput": false,
                          "secureInput": false
                      },
                      "userProperties": [],
                      "linkedServiceName": {
                          "referenceName": "MY_LINKED_SERVICE",
                          "type": "LinkedServiceReference",
                          "parameters": {
                              "l_oracle_connect_string": {
                                  "value": "MYCONNECTSTRING",
                                  "type": "Expression"
                              }
                          }
                      },
                      "typeProperties": {
                          "scripts": [
                              {
                                  "parameters": [
                                      {
                                          "name": "p_in_out",
                                          "type": "String",
                                          "value": "ToBeAppended",
                                          "direction": "InputOutput",
                                          "size": "80"
                                      }
                                  ],
                                  "type": "NonQuery",
                                  "text": "BEGIN MYSCHEMA.test_adf_param.adf_param_in_out(p_in_out => ?); END;"
                              }
                          ],
                          "scriptBlockExecutionTimeout": "02:00:00"
                      }
                  },
                  {
                      "name": "SetInOut",
                      "type": "SetVariable",
                      "dependsOn": [
                          {
                              "activity": "ScriptProcedureInOutParam",
                              "dependencyConditions": [
                                  "Succeeded"
                              ]
                          }
                      ],
                      "policy": {
                          "secureOutput": false,
                          "secureInput": false
                      },
                      "userProperties": [],
                      "typeProperties": {
                          "variableName": "v_in_out",
                          "value": {
                              "value": "@activity('ScriptProcedureInOutParam').output.outputParameters.p_in_out",
                              "type": "Expression"
                          }
                      }
                  },
                  {
                      "name": "ScriptFunctionRetVal",
                      "description": "Example for Function Return call",
                      "type": "Script",
                      "dependsOn": [],
                      "policy": {
                          "timeout": "0.12:00:00",
                          "retry": 0,
                          "retryIntervalInSeconds": 30,
                          "secureOutput": false,
                          "secureInput": false
                      },
                      "userProperties": [],
                      "linkedServiceName": {
                          "referenceName": "MY_LINKED_SERVICE",
                          "type": "LinkedServiceReference",
                          "parameters": {
                              "l_oracle_connect_string": {
                                  "value": "@pipeline().globalParameters.g_oracle_ebxaa_connect_string",
                                  "type": "Expression"
                              }
                          }
                      },
                      "typeProperties": {
                          "scripts": [
                              {
                                  "parameters": [
                                      {
                                          "name": "p_fn",
                                          "type": "String",
                                          "value": "",
                                          "direction": "Output",
                                          "size": 80
                                      }
                                  ],
                                  "type": "NonQuery",
                                  "text": "BEGIN ?:=MYSCHEMA.test_adf_param.adf_param_fn; END;"
                              }
                          ],
                          "scriptBlockExecutionTimeout": "02:00:00"
                      }
                  },
                  {
                      "name": "SetFnRetVal",
                      "type": "SetVariable",
                      "dependsOn": [
                          {
                              "activity": "ScriptFunctionRetVal",
                              "dependencyConditions": [
                                  "Succeeded"
                              ]
                          }
                      ],
                      "policy": {
                          "secureOutput": false,
                          "secureInput": false
                      },
                      "userProperties": [],
                      "typeProperties": {
                          "variableName": "v_fn_retval",
                          "value": {
                              "value": "@activity('ScriptFunctionRetVal').output.outputParameters.p_fn\n\n ",
                              "type": "Expression"
                          }
                      }
                  }
              ],
              "variables": {
                  "v_out": {
                      "type": "String"
                  },
                  "v_in_out": {
                      "type": "String"
                  },
                  "v_fn_retval": {
                      "type": "String"
                  }
              },
              "annotations": []
          }
      }

Resources