REQUEST A DEMO

Calling Oracle Stored procedures using Powershell

I’ve been doing some Powershell work lately, and needed to call an Oracle stored procedure with input and output arguments. I didn’t see a good example that mimicked what I was doing, so once I figured it out, I figured it was a good idea to share it.

Stored Procedure

First, I created a simple stored procedure with both an input and output parameter.

 

Powershell script

Then I created a Powershell script to call the stored proc.

Overall, the logic of the script is pretty straightforward.

  • Create an Oracle connection and an Oracle Command
  • Set the Command type to be Stored Procedure
  • Add an input parameter, and set its type and value
  • Add an output parameter, and set its type
  • Open the connection
  • Execute the stored procedure
  • Close the connection
  • Read the value of the output parameter

Output

I first ran it with $inputString set to “foo”, and then ran it again with $inputString set to “bar”

powershell

And we can see, it’s working successfully. Coolio.

Parameter Naming is important!

It took me a while to get this working correctly, as I kept getting this error:

Exception calling "ExecuteNonQuery" with "0" argument(s): "ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to ‘ISFOO’

powershell.error

I was calling it with two parameters (1 input, 1 output), and I knew that the data types were correct. So, WTF?

Here’s how I originally had setup the input parameter:

$cmd.Parameters.Add("inParam", [System.Data.OracleClient.OracleType]::VarChar) | out-null;
$cmd.Parameters["inParam"].Direction = [System.Data.ParameterDirection]::Input;
$cmd.Parameters["inParam"].Value = $inputString;

Looks pretty much the same – no?

It is – except for one thing – the name of the parameter.

The name of the parameter that you add in your powershell script has to match the name of the parameter in the stored procedure.

Recall that the stored procedure is defined as:

create or replace PROCEDURE  isFoo (inputString IN  VARCHAR2, outNumber OUT NUMBER) IS …

Notice that the variable name is inputString. Not inParam. They must match.

This bit me in the ass. Hard.

Now I know. And so do you. We’ll see how well my memory is next time I run into this error. At least this blog post will help me next time.

Heaven help me if someone changes the name of the variable in the stored proc, because then my code will break. This seems like a tight coupling that is prone to errors.

blurg.

Summary

Once you get your parameter naming correct, it’s pretty straightforward.

Hope this helps.