从 c# 在 Oracle 数据库中输入日期参数的正确格式是什么
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10485904/
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
What is the correct format to input a date parameter in an Oracle database from c#
提问by Cthomas
This is the error message I am getting
这是我收到的错误消息
ORA-06550: line 1, column 15:
PLS-00306: wrong number or types of arguments in call to 'OBPL_SU_PLAN_UPDATE_PLAN_DTLS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Code:
代码：
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "CWPCDS.OBPL_SU_PROCESS_PKG.OBPL_SU_PLAN_UPDATE_PLAN_DTLS";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("P_PLAN_DETAIL_ID", OracleDbType.Int32).Value = detailId;
cmd.Parameters.Add("P_PLAN_USER_DETAIL_ID", OracleDbType.Int32).Value = null;
cmd.Parameters.Add("P_TRANSACTION_PLAN", OracleDbType.NVarchar2, 20).Value = null;
cmd.Parameters.Add("P_PLAN_REV_ID", OracleDbType.Int64).Value = planRev;
cmd.Parameters.Add("P_TRANSACTION_TYPE", OracleDbType.NVarchar2, 20).Value = transType;
cmd.Parameters.Add("P_PLAN_STATUS", OracleDbType.NVarchar2, 30).Value = status;
cmd.Parameters.Add("P_DOC_ISSUE_DATE", OracleDbType.Date).Value = pid;
cmd.Parameters.Add("P_EST_OBLG_DATE", OracleDbType.Date).Value = eod;
cmd.Parameters.Add("P_AMT_TO_APPLY", OracleDbType.Int32).Value = amtApply;
cmd.Parameters.Add("P_WBS", OracleDbType.NVarchar2, 20).Value = wbs;
cmd.Parameters.Add("P_AMT1_TO_APPLY", OracleDbType.Int32).Value = 0;
cmd.Parameters.Add("P_AMT2_TO_APPLY", OracleDbType.Int32).Value = 0;
cmd.Parameters.Add("P_UPDATE_USER", OracleDbType.NVarchar2, 50).Value =
user;
cmd.Parameters.Add("P_DESC_WORK", OracleDbType.NVarchar2, 50).Value = dow;
cmd.Parameters.Add("P_RET_CODE", OracleDbType.Int32).Direction =
ParameterDirection.Output;
cmd.Parameters.Add("P_RET_MSG", OracleDbType.Varchar2, 50).Direction =
ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
I can run the procedure from Toad without any errors, the date format in Toad is '05-MAY-2012'. I get a "too many characters in character literal" error if I try and add a sigle qoute to the date value in the code-behind. Any assistance or direction is greatly appreciated!
我可以从 Toad 运行程序而不会出现任何错误，Toad 中的日期格式是“05-MAY-2012”。如果我尝试向代码隐藏中的日期值添加一个单引号，我会收到“字符文字中的字符过多”错误。非常感谢任何帮助或指导！
回答by tsells
I would recommend adding a DateTime .Net value (variable) and then assigning the parameter to the variable. This will handle the conversion for you.
我建议添加一个 DateTime .Net 值（变量），然后将参数分配给变量。这将为您处理转换。
DateTime dt = new DateTime(2012, 5, 7, 12, 23, 22, 0);
cmd.Parameters.Add(dt.ToString("dd-MMM-yyyy"), OracleDbType.Date).Value = dt;
回答by JotaBe
You can use:
您可以使用：
OracleCommandBuilder.DeriveParameters Method
OracleCommandBuilder.DeriveParameters 方法
to guess which is the required data type and name for each parameter. Once you know that, you can modify your method accordingly.
猜测每个参数所需的数据类型和名称。一旦你知道这一点，你就可以相应地修改你的方法。
Sometimes you get errors because the type you decided to use isn't the one expected by the database (i.e. for C# DateTime there can be TimeStamp, Date, SmallDate... depending on the DB kind. Using the wrong type for the paramter can cause errors or truncate data).
有时您会收到错误，因为您决定使用的类型不是数据库预期的类型（即对于 C# DateTime，可能有 TimeStamp、Date、SmallDate... 取决于 DB 类型。使用错误的参数类型可能导致错误或截断数据）。
Never try to pass the values as strings. Pass them as the corresponding C# type. I.e. for passing dates, pass a DateTime object. The program will take care to convert it to a format which will be understood by the database. If you pass a string, unless it's a universal format, you can get into trouble if you change the DB config, i.e. the default language. That's why it's better of to let the system take care of the conversion details. It will always do it the right way.
切勿尝试将值作为字符串传递。将它们作为相应的 C# 类型传递。即传递日期，传递一个 DateTime 对象。该程序将注意将其转换为数据库可以理解的格式。如果您传递一个字符串，除非它是通用格式，否则如果您更改数据库配置，即默认语言，您可能会遇到麻烦。这就是为什么最好让系统处理转换细节的原因。它总是会以正确的方式做到这一点。
