存储过程如下:create or replace procedure test_milo_add(in_testname in test_milo.testname%type, out_testid out test_milo.testid%type) is
begininsert into test_milo (testid,testname) values (test_milo_testid.nextval,in_testname);
select crm.test_milo_testid.currval into out_testid from dual;end test_milo_add;
用.net代码执行该过程,完全正常。 OracleCommand oc = c.CreateCommand();
oc.CommandText = "test_milo_add";
oc.Parameters.Add("in_testname", OracleDbType.Varchar2, entity.TESTNAME, ParameterDirection.Input);
oc.Parameters.Add("out_testid", OracleDbType.Int32,ParameterDirection.Output);
oc.CommandType = CommandType.StoredProcedure; var rowaffected=oc.ExecuteNonQuery();但是我想将存储过程中的代码搬到sql语句里直接执行,像这样: OracleCommand oc = c.CreateCommand();
oc.CommandText = "insert into test_milo (testid,testname) values (test_milo_testid.nextval,:in_testname);select crm.test_milo_testid.currval into :out_testid from dual;";
oc.Parameters.Add("in_testname", OracleDbType.Varchar2, entity.TESTNAME, ParameterDirection.Input);
oc.Parameters.Add("out_testid", OracleDbType.Int32,ParameterDirection.Output);
var rowaffected=oc.ExecuteNonQuery();
就不行了,报错:Oracle.DataAccess.Client.OracleException: ORA-00911: invalid character求解,应该怎么写才对?前提是我不想使用存储过程,希望直接运行sql语句。
begininsert into test_milo (testid,testname) values (test_milo_testid.nextval,in_testname);
select crm.test_milo_testid.currval into out_testid from dual;end test_milo_add;
用.net代码执行该过程,完全正常。 OracleCommand oc = c.CreateCommand();
oc.CommandText = "test_milo_add";
oc.Parameters.Add("in_testname", OracleDbType.Varchar2, entity.TESTNAME, ParameterDirection.Input);
oc.Parameters.Add("out_testid", OracleDbType.Int32,ParameterDirection.Output);
oc.CommandType = CommandType.StoredProcedure; var rowaffected=oc.ExecuteNonQuery();但是我想将存储过程中的代码搬到sql语句里直接执行,像这样: OracleCommand oc = c.CreateCommand();
oc.CommandText = "insert into test_milo (testid,testname) values (test_milo_testid.nextval,:in_testname);select crm.test_milo_testid.currval into :out_testid from dual;";
oc.Parameters.Add("in_testname", OracleDbType.Varchar2, entity.TESTNAME, ParameterDirection.Input);
oc.Parameters.Add("out_testid", OracleDbType.Int32,ParameterDirection.Output);
var rowaffected=oc.ExecuteNonQuery();
就不行了,报错:Oracle.DataAccess.Client.OracleException: ORA-00911: invalid character求解,应该怎么写才对?前提是我不想使用存储过程,希望直接运行sql语句。
用 OracleCommand oc = c.CreateCommand(); 这个 oracle当然不认识
sorry 才看明白
insert 后面应该直接加select 不能再values了
insert into test_milo (testid,testname) values (test_milo_testid.nextval,:in_testname);select crm.test_milo_testid.currval
是这个意思不?