oracle oracle存储过程的问题——参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2548006/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Problem with oracle stored procedure - parameters
提问by Nicole
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:
...和我的 C# 代码：
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)
问题是当我尝试将一个值放入参数“Fecha”（即日期）时，代码给了我这个错误（当该行command.ExecuteReader();被执行时）
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
我尝试了 datetime 并且不是问题，我曾经尝试过没有输入参数而只有输出，但仍然出现相同的错误。显然问题出在输出参数上。我已经尝试将 p_Detalle OUT VARCHAR2 而不是 p_Detalle OUT LIQUIDACION.FILADETALLE%TYPE 但它也不起作用
I hope my post is understandable.. thanks!!!!!!!!!!
我希望我的帖子是可以理解的..谢谢!!!!!!!!!!!!
采纳答案by Leniel Maccaferri
Probably... this can be the problem. Try to use Oracle to_date function to match the date format.
可能……这就是问题所在。尝试使用 Oracle to_date 函数来匹配日期格式。
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.
如果这不起作用，作为最后的手段，将参数更改为 varchar 并将日期作为字符串传递给存储过程。在 Oracle 中使用 to_date。
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;
回答by OMG Ponies
What is the output from:
输出是什么：
DESC LIQUIDACION
The out parameter notation you have is correct:
您拥有的输出参数符号是正确的：
p_Detalle OUT LIQUIDACION.FILADETALLE%TYPE
...so that leaves the issue to be in the C# code, when converting the Oracle data type to its .NET sibling. I've seen it before, when using Java...
...因此，在将 Oracle 数据类型转换为其 .NET 同级时，问题仍存在于 C# 代码中。我以前见过它，在使用 Java 时...
Previously
之前
If the data type of LIQUIDACION.filafechais VARCHAR2/etc (not DATE), use:
如果数据类型LIQUIDACION.filafecha是 VARCHAR2/etc（不是 DATE），使用：
SELECT la.filadeetaile
INTO p_Detalle
FROM LIQUIDACION la
WHERE la.filacuenta = p_Cuenta
AND la.filafecha = TO_CHAR(p_Fecha, 'YYYY-MON-DD');
...or you could change the parameter to match the data type:
...或者您可以更改参数以匹配数据类型：
p_Fecha IN LIQUIDACION.filafecha%TYPE
...and correct the C# code to pass the proper information.
...并更正 C# 代码以传递正确的信息。
Ideally, a date column should be a DATE data type...
理想情况下，日期列应该是 DATE 数据类型...
回答by APC
It appears that you are not defining the size of the output parameter:
看来您没有定义输出参数的大小：
command.Parameters.Add("p_Detalle", OracleDbType.Varchar2, ParameterDirection.Output);
I don't know what ADO defaults to, but it is obviously smaller the length of LIQUIDACION.FILADETALLE%TYPE. Oracle hurls ORA-06502 when the receiving variable is too small or of the wrong datatype. Try something like (adjusting the size to match the database column definition):
我不知道 ADO 默认是什么，但它显然比LIQUIDACION.FILADETALLE%TYPE. 当接收变量太小或数据类型错误时，Oracle 会抛出 ORA-06502。尝试类似（调整大小以匹配数据库列定义）：
command.Parameters["p_Detalle"].Size = 20;
回答by Douglas Lise
Try to use to_date(p_fecha, 'yyyy-mmm-dd') when setting the parameter:
设置参数时尝试使用 to_date(p_fecha, 'yyyy-mmm-dd') ：
command.Parameters["p_Fecha"].Value = to_date(fecha, 'yyyy-MON-dd');
