从C#.net调用oracle SP [英] calling oracle SP from C#.net

问题描述

在oracle程序包中，我有3个存储过程，并且存储过程调用dbmon_dbinfo具有3个PL/SQL块，用于Insert(INS)更新和删除，我从以下代码中调用"INS":

In oracle package I am having 3 Stored procedures and the Stored procedures call dbmon_dbinfo is having 3 PL/SQL blocks for Insert(INS) Update and Delete I am calling ''INS'' from following code:

DateTime dt = DateTime.Now;
            OracleCommand objCommand = new OracleCommand();
            connection.Open();
            objCommand.Connection = connection;
            objCommand.CommandText = "PKG_DBMON_MASTER.dbmon_dbinfo(INS)";
            objCommand.CommandType = CommandType.StoredProcedure;
         //   objCommand.Parameters.Add("p_made", OracleType.VarChar).Value = "INS";
          //  objCommand.Parameters.Add("p_db_id", OracleType.VarChar).Value = 001;
            objCommand.Parameters.Add("name",OracleType.VarChar).Value = txtName.Text;
            objCommand.Parameters.Add("owner_name", OracleType.VarChar).Value = txtOwnerName.Text;
            objCommand.Parameters.Add("owner_email", OracleType.VarChar).Value = txtOwnerMail.Text;
            objCommand.Parameters.Add("project", OracleType.VarChar).Value = txtProject.Text;
            objCommand.Parameters.Add("created_date", OracleType.DateTime).Value = dtpCreatedDate.Text;
         //   objCommand.Parameters.Add("p_droped_date", OracleType.DateTime).Value =null;
            objCommand.Parameters.Add("sys_passwd", OracleType.VarChar).Value = "password";
            objCommand.Parameters.Add("system_passwd", OracleType.VarChar).Value = "password";
            objCommand.Parameters.Add("status",OracleType.VarChar).Value = txtStatus.Text;
            objCommand.Parameters.Add("srv_id", OracleType.Number).Value = decimal.Parse(txtServerID.Text);
            objCommand.Parameters.Add("dbms_id",OracleType.Number).Value =decimal.Parse(txtDBMSID.Text);
            objCommand.Parameters.Add("db_type", OracleType.VarChar).Value = txtStatus.Text;            
            objCommand.ExecuteNonQuery();

但它给出了以下异常

but it gives following exception

ORA-06550: line 1, column 7:
PLS-00801: internal error [22503]
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

为什么?请帮忙.

Why is that ?? please help.

推荐答案

我可以看到两个潜在的问题-您需要确定最合适的问题.第一个是默认情况下，OracleParameter按位置而不是名称进行绑定.要解决此问题，请在执行命令之前添加objCommand.BindByName.第二个问题可能是您对命令名没有足够的限定-可能您可能需要在前面添加用户名(没有看到您的连接方式，我不能再发表评论了)，但这会给您您的命令文本:username.PKG_DBMON_MASTER.dbmon_dbinfo(INS).

啊哈-我刚刚在输入时发现了它-您不需要在命令文本中输入参数值-而是将其更改为PKG_DBMON_MASTER.dbmon_dbinfo()，因为您在代码中指定了参数.顺便说一句-您应该将命令包装在using 块中以使其自动处理.

I can see two potential issues - you''ll need to work out which best fits. The first is that by default, the OracleParameter binds by position and not by name. To fix this, add objCommand.BindByName before you execute the command. The second issue could be that you haven''t qualified your command name enough - potentially you might need to add the user name up front (without seeing how you are connecting, I can''t comment any further), but this would give you the command text: username.PKG_DBMON_MASTER.dbmon_dbinfo(INS).

Ah hah - I''ve just spotted it while typing this in - you don''t need to put a parameter value into your command text - change it to PKG_DBMON_MASTER.dbmon_dbinfo() instead, as you are specifying the parameters in your code. BTW - you should wrap your command in a using block to get it automatically disposed.

这篇关于从C#.net调用oracle SP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！