Calling Oracle Stored procedures using Powershell
Gary Sherman
May 15, 2012

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.

5 Comments to "Calling Oracle Stored procedures using Powershell"
  1. Peter says:

    Is it also possible to call a stored procedure from/in a package?

    I think i have to edit

    $inputString = “foo”;

    but how is the syntax?

    Many thanx in advance

  2. gsherman says:

    Hi Peter,

    I believe to call a proc that is in a package, you just call it with packageName.ProcName

    This might also be helpful:
    http://oracle.ittoolbox.com/groups/technical-functional/oracle-db-l/packages-procedures-functionswhen-and-why-4771909

    Hope this helps.

  3. Anonymous says:

    Gary,

    Since you’re setting the parameter by name, I’m guessing that’s why its demanding the same name. Could you instead call it by position? (which would allay the fear of a parameter name change).

    E.g.: $cmd.Parameters[1].Value = $inputString;

  4. Juan says:

    LOL I SPENT THE WHOLE AFTERNOON TRYING TO FIND OUT WTF WAS HAPPENING WITH THE SCRIPT UNTIL I READ THE PART OF THE PARAMETER NAMES………………………………. THANKS THO!

  5. Ricardo says:

    The example is really good, something important to highlight is you must work with Powershell 32 bit platform – it does not work with 64 bit. Good luck !

Leave a Comment

International: +1 (512) 610-5400
Toll Free: 1 (800) 684-2055