I have the following Oracle Stored Procedure:
PROCEDURE SP_ITEMEXISTS(pID IN NUMBER, pExists OUT CHAR) IS
BEGIN
select CASE count(*) WHEN 0 THEN 'N' ELSE 'Y' END into pExists from items where id = pID;
END SP_ITEMEXISTS;
I'm calling it from Enterprise Library 6.0 with ODP.NET with the following code:
public bool ItemExists(int itemID)
{
string procedureName = "SP_ItemExists";
var database = new DatabaseProviderFactory().CreateDefault();
bool returnValue = false;
using (OracleCommand command = (OracleCommand)database.GetStoredProcCommand(procedureName))
{
command.Parameters.Add("pID", OracleDbType.Int32, itemID, ParameterDirection.Input);
command.Parameters.Add("pExists", OracleDbType.Char, ParameterDirection.Output);
using (OracleDataReader reader = ((OracleDataReaderWrapper)database.ExecuteReader(command)).InnerReader)
{
char exists = reader.GetChar("pExists");
if (exists.ToString().ToUpper() == "Y")
returnValue = true;
else
returnValue = false;
}
}
return returnValue;
}
I receive the following error: ORA-06550: line 1, column 7:\nPLS-00306: wrong number or types of arguments in call to 'SP_ITEMEXISTS'\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored
I have other stored procedures that I can call successfully, but the others have a refcursor OUT parameter. How do I call it when the OUT type is Char?
ExecuteReader is used only for RefCursor as you found out already. Use database.ExecuteNonQuery() for calling a procedure or function. Read out return values with command.Parameters("pExists").Value
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments