oracle System.Data.OracleClient.OracleException: ORA-01036: 非法变量名/编号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22107183/
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
System.Data.OracleClient.OracleException: ORA-01036: illegal variable name/number
提问by user2884405
I'm facing this error that telling me I'm using an illegal variable or number and it highligh this line in my code Line 34:rowsAffected = command.ExecuteNonQuery();. I think I have the issue in the parameters that need to be changed based on Oracle format but not sure. I did replace all the @with p.Course_id, then ?p.course, p_course_idas I did in my stored procedure in oracle but none of them work. I'm still getting same error.
Please help me sort out this issue. Thank you
我正面临这个错误，它告诉我我正在使用一个非法变量或数字，它在我的代码中突出显示了这一行Line 34:rowsAffected = command.ExecuteNonQuery();。我想我在需要根据 Oracle 格式更改的参数中存在问题但不确定。我确实@用p.Course_id, then替换了所有的?p.course，p_course_id就像我在 oracle 中的存储过程中所做的那样，但它们都不起作用。我仍然遇到同样的错误。请帮我解决这个问题。谢谢
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OracleClient;
public class PostForum
{
public static int INSERTforum(int course_Id, string question, string posterName, DateTime blog_date)
{
int rowsAffected = 0;
using (OracleConnection connection = ConnectionManager.GetDatabaseConnection())
{
OracleCommand command = new OracleCommand("INSERTforum", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@course_Id", SqlDbType.Int).Value = course_Id;
command.Parameters.Add("@question", SqlDbType.VarChar).Value = question;
command.Parameters.Add("@posterName", SqlDbType.VarChar).Value = posterName;
command.Parameters.Add("@blogdate", SqlDbType.DateTime).Value = blog_date;
rowsAffected = command.ExecuteNonQuery();
}
return rowsAffected;
}
}
Here is my stored procedure
这是我的存储过程
CREATE OR REPLACE PROCEDURE INSERTforum(
p_course_id IN forum.COURSE_ID%TYPE,
p_question IN forum.QUESTION%TYPE,
p_postername IN forum.POSTERNAME%TYPE,
p_blogdate IN forum.BLOG_DATE%TYPE)
AS
BEGIN
INSERT INTO forum ("COURSE_ID", "QUESTION", "POSTERNAME", "BLOG_DATE")
VALUES (p_course_id, p_question,p_postername, p_blogdate);
COMMIT;
END;
/
回答by Steve
I think that your problem is raised by the use of an invalid enum in your Add methods calls
我认为您的问题是由在您的 Add 方法调用中使用无效枚举引起的
If you run this code, you could notice that the OracleTypefor Int32 is not the same of SqlDbType
如果您运行此代码，您可能会注意到Int32的OracleType与SqlDbType 不同
OracleType e = OracleType.Int32;
int i = (int)e;
Console.WriteLine(i.ToString()); // Output = 28
SqlDbType z = SqlDbType.Int;
i = (int)z;
Console.WriteLine(i.ToString()); // Output = 8
So, I suggest to use the correct enum for your ADO.NET provider.
因此，我建议为您的 ADO.NET 提供程序使用正确的枚举。
It is interesting to note that calling Add with SqlDbTypeinstead of OracleTypeis accepted and don't raise a compiler time error. This happens because the Add method has an overload that accepts an object as second parameter (It is used to pass directly a value when constructing the parameter).
有趣的是，调用 Add withSqlDbType而不是OracleType被接受并且不会引发编译器时间错误。发生这种情况是因为 Add 方法有一个重载，它接受一个对象作为第二个参数（它用于在构造参数时直接传递一个值）。
An alternative is to use AddWithValueof the OracleParameterCollection
另一种方法是在使用AddWithValue的OracleParameterCollection
command.Parameters.AddWithValue("@course_Id", course_Id);
command.Parameters.AddWithValue("@question", question);
command.Parameters.AddWithValue("@posterName", posterName);
command.Parameters.AddWithValue("@blogdate", blog_date);
