CODEWITHSUNDEEP
oracle-databasepowershellstored-procedures
aastle
aastle

Reputation: 21

PowerShell execute Oracle 8i stored procedure error: wrong number or types of parameters

My PowerShell 2.0 code:

$Cmd = New-Object System.Data.OracleClient.OracleCommand
$Conn = New-Object System.Data.OracleClient.OracleConnection
$Conn.Open() 
$Cmd.Connection = $Conn
$Cmd.CommandType = [System.Data.CommandType]::StoredProcedure
$Cmd.CommandText = "WEAMFG.PF_MERCH_OLY_PKG.ALAN_TEST"
$Cmd.Parameters.Add("P_INPUT", [System.Data.OracleClient.OracleType]::VarChar)
$Cmd.Parameters["P_INPUT"].Value = "1"
$Cmd.ExecuteNonQuery()  

$Conn.Close()

My Pl/Sql code:

PROCEDURE ALAN_TEST(P_INPUT VARCHAR2)
IS
vAnswer VARCHAR2(4000);
BEGIN
SELECT answer 
INTO vAnswer 
FROM weamfg.ASKTOM
WHERE ROWNUM = 1;
END ALAN_TEST;

Executing the stored procedure throws an error message:

Exception calling "ExecuteNonQuery" with "0" argument(s): "ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'ALAN_TEST' ORA-06550: line 1, column 7: PL/SQL: Statement ignored.

I am using .NET 3.5 on a Windows XP machine. Any ideas?

EDIT: I can execute a stored procedure that has no input parameters and returns a cursor with no errors.

Upvotes: 0

Views: 1317

Answers (2)

aastle
aastle

Reputation: 21

The PowerShell code I posted wasn't entirely accurate, I was trying to passing the OracleCommand and OracleConnection objects as parameters to a PowerShell function. I thought they had been initialized when I called the PS function but they weren't. The additional error message from PowerShell was an invalid method invocation error which I had overlooked.

Upvotes: 0

kevinskio
kevinskio

Reputation: 4551

Your procedure does not have a specifically declared out parameter. Or, if you were using a function, does not return anything.

Try changing the signature to 

PROCEDURE ALAN_TEST(P_INPUT OUT VARCHAR2);

Many times when interacting with Oracle if you are not sure whether you will receive one record or many the best way is to return a sys_refcursor which can hold one or more records.

PROCEDURE ALAN_TEST(P_INPUT IN VARCHAR2, p_cursor out sys_refcursor);

Upvotes: 0

Related Questions