Call Oracle Stored Procedure with Char Out Parameter errors with PLS-00306: wrong number or types of arguments in call

Question

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?

Answer 1

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