将自定义对象传递给存储过程中的oracle参数 [英] passing custom object to oracle parameters in stored procedure

问题描述

我这样做是为了插入程序而不是为更新程序工作



i尝试这个编码



类型：

I have done this for inserting procedure but not working for updating procedure

i tried this coding

type:

create type smp_emp_type as object
  (id number(3), name varchar2(30), gender varchar2(1)) NOT FINAL;

表格：

table:

create table smp_emp
(id number(3), name varchar2(30), gender varchar2(1)) ;

insert_procedure：

insert_procedure:

CREATE OR REPLACE procedure ins1 (emp IN smp_emp_type) as
 begin
    Insert into smp_emp  values (emp.id,emp.name,emp.gender);
end

CREATE OR REPLACE procedure upd1 (emp IN smp_emp_type) as
 begin
   update smp_emp e set e.name =emp.name,e.gender = emp.gender where id = emp.id;
end

程序是成功创建的

bt update不能使用我的代码：

both the procedures are created sucessfully
bt update is not working with my code:

update_pro updobj = new update_pro();
           updobj._Id = Convert.ToInt16(textBox1.Text);
           updobj._Name = textBox2.Text;
           updobj._Gender = textBox3.Text;
           OracleConnection conn = new OracleConnection("Data Source=r2;User ID=test;Password=test;");
           conn.Open();

           OracleCommand cmd = new OracleCommand("upd1", conn);
           OracleParameter emp_obj = new OracleParameter();
           emp_obj.OracleDbType = OracleDbType.Object;
           emp_obj.Direction = ParameterDirection.Input;
           emp_obj.UdtTypeName = "SMP_EMP_TYPE";
           emp_obj.Value = updobj;
           cmd.Parameters.Add(emp_obj);
           cmd.ExecuteNonQuery();

显示错误消息为ORA-00900：无效的SQL语句;



plz帮我解决这个问题

showing error message as ORA-00900: invalid SQL statement;

plz help me to solve this

推荐答案

我想你忘了这个e。在id = emp.id之前

I think you forget this "e." before id = emp.id

CREATE OR REPLACE procedure upd1 (emp IN smp_emp_type) as
 begin
   update smp_emp e set e.name =emp.name,e.gender = emp.gender where e.id = emp.id;
end

正确答案是

OracleCommand cmd = new OracleCommand（upd1，conn）;

cmd.CommandType = CommandType。 StoredProcedure

OracleParameter emp_obj = new OracleParameter（）;

emp_obj.OracleDbType = OracleDbType.Object;

emp_obj.Direction = ParameterDirection .Input;

emp_obj.UdtTypeName =SMP_EMP_TYPE;

emp_obj.Value = updobj;

cmd.Parameters.Add（emp_obj）;

cmd.ExecuteNonQuery（）;

the correct answer is
OracleCommand cmd = new OracleCommand("upd1", conn);
cmd.CommandType = CommandType. StoredProcedure
OracleParameter emp_obj = new OracleParameter();
emp_obj.OracleDbType = OracleDbType.Object;
emp_obj.Direction = ParameterDirection.Input;
emp_obj.UdtTypeName = "SMP_EMP_TYPE";
emp_obj.Value = updobj;
cmd.Parameters.Add(emp_obj);
cmd.ExecuteNonQuery();

这篇关于将自定义对象传递给存储过程中的oracle参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！