I have this stored procedure:
CREATE OR REPLACE PROCEDURE "LIQUIDACION_OBTENER" (
p_Cuenta IN NUMBER,
p_Fecha IN DATE,
p_Detalle OUT LIQUIDACION.FILADETALLE%TYPE
) IS
BEGIN
SELECT FILADETALLE
INTO p_Detalle
FROM Liquidacion
WHERE (FILACUENTA = p_Cuenta)
AND (FILAFECHA = p_Fecha);
END;
/
...and my c# code:
string liquidacion = string.Empty;
OracleCommand command = new OracleCommand("Liquidacion_Obtener");
command.BindByName = true;
command.Parameters.Add(new OracleParameter("p_Cuenta", OracleDbType.Int64));
command.Parameters["p_Cuenta"].Value = cuenta;
command.Parameters.Add(new OracleParameter("p_Fecha", OracleDbType.Date));
command.Parameters["p_Fecha"].Value = fecha;
command.Parameters.Add("p_Detalle", OracleDbType.Varchar2, ParameterDirection.Output);
OracleConnectionHolder connection = null;
connection = this.GetConnection();
command.Connection = connection.Connection;
command.CommandTimeout = 30;
command.CommandType = CommandType.StoredProcedure;
OracleDataReader lector = command.ExecuteReader();
while (lector.Read())
{
liquidacion += ((OracleString)command.Parameters["p_Detalle"].Value).Value;
}
the thing is that when I try to put a value into the parameter "Fecha" (that is a date) the code gives me this error (when the line command.ExecuteReader(); is executed)
Oracle.DataAccess.Client.OracleException : ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYSTEM.LIQUIDACION_OBTENER", line 9
ORA-06512: at line 1
I tried with the datetime and was not the problem, I eve tried with no input parameters and just the output and still got the same error. Aparently the problem is with the output parameter. I already tried putting p_Detalle OUT VARCHAR2 instead of p_Detalle OUT LIQUIDACION.FILADETALLE%TYPE but it didn't work either
I hope my post is understandable.. thanks!!!!!!!!!!
Probably... this can be the problem. Try to use Oracle to_date function to match the date format.
If that doesn't work, as a last resort change the parameter to varchar and pass the date as a string to the stored procedure. Use to_date inside Oracle.
This way your stored procedure would be:
CREATE OR REPLACE PROCEDURE "LIQUIDACION_OBTENER" (
p_Cuenta IN NUMBER,
p_Fecha IN VARCHAR,
p_Detalle OUT LIQUIDACION.FILADETALLE%TYPE
) IS
BEGIN
SELECT
FILADETALLE
INTO
p_Detalle
FROM
Liquidacion
WHERE
(FILACUENTA = p_Cuenta)
AND (FILAFECHA = to_date(p_Fecha, 'yyyy-MON-dd'));
END;