错误解决：ORA-01036: 非法的变量名/编号

 

错误解决：ORA-01036: 非法的变量名/编号

分类： Oracle 小菜2009-04-15 15:38 4468人阅读 评论(0) 收藏 举报

 

出现此错误的原因很可能是存储过程的包体的参数定义与包头的定义不一样， 很多时候修改了包体，却忘了修改包头。 另外，创建OracleParameter时， 由于构造函数的版本众多，使用了某些版本时,可能会出现这个错误，建议 创建OracleParameter时，一定要指定OracleType。  直接使用Sql 语句时，在Sql 语句中的 参数部分没有使用冒号作为前缀，或 者错误使用了Sql Server 的"@"符号都会导致此错误。 总之此错误出现在Parameter指定的ParameterName,与实际参数名称不匹配时。 要注意：在 new OracleParameter(） 中，指定的ParameterName 只需要包含 参数的字符部分，无需保含前缀，如：冒号。  简单点:@ 改成 ：

 

 

  SqlClient参数表达式：@ParameterName   
  OracleClient参数表达式：:ParameterName   
  OleDB.Net参数表达式：?  

 

通过使用OleDb操作Oracle数据库，成功实现图片上传到Blob类型的字段，但有时会发生ORA-01036错误的问题，经查询是错误提示为illegal variable name/number，不知道有谁能详细解释illegal variable name/number的意思

Oracle Data Provider for .NET
Hi
I am using ODP.NET (Oracle Data Provider for .NET) in my asp.net application.
I have a table in my oracle database called "equipmentgroup". When the page loads for the first time i retrieve all the records from the table to a dataset and save it to viewstate. Later on any addition or modification is done in the dataset only in disconnected mode. Finally,when user clicks update i call this function "update" which should do a batch update but instead it gives the following error :

"ORA-01036: illegal variable name/number "


        private void update()
        {
            OracleParameter workParam;

            OracleConnection cnn = new OracleConnection("Data Source=NEELESHR;User Id=tmse; Password=tmse;");
            string sql = "INSERT INTO EquipmentGroup (Code, Description, LifeTime, PriamryLife, Grading, Inflator, ExtensionRate, MaintenanceFee) VALUES (:Code, :Description, :LifeTime, :PriamryLife, :Grading, :Inflator, :ExtensionRate, :MaintenanceFee)";
            OracleCommand cmd = new OracleCommand(sql,cnn);
            cmd.CommandType = CommandType.Text;            

            OracleDataAdapter da = new OracleDataAdapter();
            da.InsertCommand = cmd;
            
            workParam = da.InsertCommand.Parameters.Add("Code",OracleType.Char,10,"Code");
            workParam.SourceVersion = DataRowVersion.Current;

            workParam = da.InsertCommand.Parameters.Add("Description",OracleType.VarChar,50,"Description");
            workParam.SourceVersion = DataRowVersion.Current;

            workParam = da.InsertCommand.Parameters.Add("LifeTime",OracleType.Number);
            workParam.SourceColumn = "LifeTime";
            workParam.SourceVersion = DataRowVersion.Current;

            workParam = da.InsertCommand.Parameters.Add("PriamryLife",OracleType.Number);
            workParam.SourceColumn = "PriamryLife";
            workParam.SourceVersion = DataRowVersion.Current;

            workParam = da.InsertCommand.Parameters.Add("Grading",OracleType.Char,10,"Grading");
            workParam.SourceVersion = DataRowVersion.Current;

            workParam = da.InsertCommand.Parameters.Add("Inflator",OracleType.Number);
            workParam.SourceColumn = "Inflator";
            workParam.SourceVersion = DataRowVersion.Current;

            workParam = da.InsertCommand.Parameters.Add("ExtensionRate",OracleType.Number);
            workParam.SourceColumn = "ExtensionRate";
            workParam.SourceVersion = DataRowVersion.Current;

            workParam = da.InsertCommand.Parameters.Add("MaintenanceFee",OracleType.Number);
            workParam.SourceColumn = "MaintenanceFee";
            workParam.SourceVersion = DataRowVersion.Current;
            

            try
            {
                da.Update(ds,"EquipmentGroup");
            }
            catch(Exception e)
            {
                Message.Text = e.Message;
            }

        }

Hi,

I think that you should add parameters with ":" included, like:
workParam =
da.InsertCommand.Parameters.Add(":Code",OracleType.Char,10,"Code");

OleDb Data Provider for .NET

string sql = "INSERT INTO EquipmentGroup (Code, Description, LifeTime, PriamryLife, Grading, Inflator, ExtensionRate, MaintenanceFee) VALUES (?, ?, ?, ?, ?, ?, ?, ?, )";

Hi,

I think that you should add parameters with ":" included, like:
workParam =
da.InsertCommand.Parameters.Add(":Code",OracleType.Char,10,"Code");