IDataParameter Error, Error Code = ORA-01006
Posted: Thu 27 Nov 2008 07:57
//When have same Parameter Named "p3"
string sql = @"declare begin
insert into NH_SC.T_DEPARTMENT(ID,NAME,P_ID,REGION_TYPE,LEVEL_STR,OPTIMISTICLOCKFIELD)
values(:p0,:p1,null,:p2,null,:p3);
insert into NH_SC.T_LOG(OPERATE_USER,OPERATE_TIME,OPERATE_TYPE,ALIAS_NAME,CLASS_NAME,OPERATE_LOG,OPTIMISTICLOCKFIELD,ID)
values(:p5,:p6,:p7,:p8,:p9,:p10,:p3,SQ_NH_SC_T_LOG_894FD914.nextval);
select SQ_NH_SC_T_LOG_894FD914.CurrVal into :p4 from DUAL;
:n := 1;
end;";
DbProviderFactory fac = OracleProviderFactory.Instance;
using (DbConnection conn = fac.CreateConnection())
{
conn.ConnectionString = "Direct=True;Server=192.168.8.27;Port=1521;Sid=ora;User Id=nh_sc;Password=123;";
conn.Open();
using (DbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
IDataParameter ret = cmd.CreateParameter();
ret.DbType = DbType.Int32;
ret.Direction = ParameterDirection.Output;
ret.ParameterName = ":n";
cmd.Parameters.Add(ret);
IDataParameter pID = cmd.CreateParameter();
pID.DbType = DbType.String;
pID.Direction = ParameterDirection.Input;
pID.ParameterName = ":p0";
pID.Value = "510000";
cmd.Parameters.Add(pID);
IDataParameter pName = cmd.CreateParameter();
pName.DbType = DbType.String;
pName.Direction = ParameterDirection.Input;
pName.ParameterName = ":p1";
pName.Value = "ccc";
cmd.Parameters.Add(pName);
IDataParameter pRegionType = cmd.CreateParameter();
pRegionType.DbType = DbType.Int32;
pRegionType.Direction = ParameterDirection.Input;
pRegionType.ParameterName = ":p2";
pRegionType.Value = 1;
cmd.Parameters.Add(pRegionType);
IDataParameter pOPTIMISTICLOCKFIELD = cmd.CreateParameter();
pOPTIMISTICLOCKFIELD.DbType = DbType.Int32;
pOPTIMISTICLOCKFIELD.Direction = ParameterDirection.Input;
pOPTIMISTICLOCKFIELD.ParameterName = ":p3";
pOPTIMISTICLOCKFIELD.Value = 1;
cmd.Parameters.Add(pOPTIMISTICLOCKFIELD);
IDataParameter p4 = cmd.CreateParameter();
p4.DbType = DbType.String;
p4.Direction = ParameterDirection.Input;
p4.ParameterName = ":p4";
p4.Value = 1;
cmd.Parameters.Add(p4);
IDataParameter p5 = cmd.CreateParameter();
p5.DbType = DbType.String;
p5.Direction = ParameterDirection.Input;
p5.ParameterName = ":p5";
p5.Value = "p5";
cmd.Parameters.Add(p5);
IDataParameter p6 = cmd.CreateParameter();
p6.DbType = DbType.DateTime;
p6.Direction = ParameterDirection.Input;
p6.ParameterName = ":p6";
p6.Value = DateTime.Now;
cmd.Parameters.Add(p6);
IDataParameter p7 = cmd.CreateParameter();
p7.DbType = DbType.String;
p7.Direction = ParameterDirection.Input;
p7.ParameterName = ":p7";
p7.Value = "p7";
cmd.Parameters.Add(p7);
IDataParameter p8 = cmd.CreateParameter();
p8.DbType = DbType.String;
p8.Direction = ParameterDirection.Input;
p8.ParameterName = ":p8";
p8.Value = "p8";
cmd.Parameters.Add(p8);
IDataParameter p9 = cmd.CreateParameter();
p9.DbType = DbType.String;
p9.Direction = ParameterDirection.Input;
p9.ParameterName = ":p9";
p9.Value = "p9";
cmd.Parameters.Add(p9);
IDataParameter p10 = cmd.CreateParameter();
p10.DbType = DbType.String;
p10.Direction = ParameterDirection.Input;
p10.ParameterName = ":p10";
p10.Value = "p10";
cmd.Parameters.Add(p10);
cmd.ExecuteNonQuery();
}
}
string sql = @"declare begin
insert into NH_SC.T_DEPARTMENT(ID,NAME,P_ID,REGION_TYPE,LEVEL_STR,OPTIMISTICLOCKFIELD)
values(:p0,:p1,null,:p2,null,:p3);
insert into NH_SC.T_LOG(OPERATE_USER,OPERATE_TIME,OPERATE_TYPE,ALIAS_NAME,CLASS_NAME,OPERATE_LOG,OPTIMISTICLOCKFIELD,ID)
values(:p5,:p6,:p7,:p8,:p9,:p10,:p3,SQ_NH_SC_T_LOG_894FD914.nextval);
select SQ_NH_SC_T_LOG_894FD914.CurrVal into :p4 from DUAL;
:n := 1;
end;";
DbProviderFactory fac = OracleProviderFactory.Instance;
using (DbConnection conn = fac.CreateConnection())
{
conn.ConnectionString = "Direct=True;Server=192.168.8.27;Port=1521;Sid=ora;User Id=nh_sc;Password=123;";
conn.Open();
using (DbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
IDataParameter ret = cmd.CreateParameter();
ret.DbType = DbType.Int32;
ret.Direction = ParameterDirection.Output;
ret.ParameterName = ":n";
cmd.Parameters.Add(ret);
IDataParameter pID = cmd.CreateParameter();
pID.DbType = DbType.String;
pID.Direction = ParameterDirection.Input;
pID.ParameterName = ":p0";
pID.Value = "510000";
cmd.Parameters.Add(pID);
IDataParameter pName = cmd.CreateParameter();
pName.DbType = DbType.String;
pName.Direction = ParameterDirection.Input;
pName.ParameterName = ":p1";
pName.Value = "ccc";
cmd.Parameters.Add(pName);
IDataParameter pRegionType = cmd.CreateParameter();
pRegionType.DbType = DbType.Int32;
pRegionType.Direction = ParameterDirection.Input;
pRegionType.ParameterName = ":p2";
pRegionType.Value = 1;
cmd.Parameters.Add(pRegionType);
IDataParameter pOPTIMISTICLOCKFIELD = cmd.CreateParameter();
pOPTIMISTICLOCKFIELD.DbType = DbType.Int32;
pOPTIMISTICLOCKFIELD.Direction = ParameterDirection.Input;
pOPTIMISTICLOCKFIELD.ParameterName = ":p3";
pOPTIMISTICLOCKFIELD.Value = 1;
cmd.Parameters.Add(pOPTIMISTICLOCKFIELD);
IDataParameter p4 = cmd.CreateParameter();
p4.DbType = DbType.String;
p4.Direction = ParameterDirection.Input;
p4.ParameterName = ":p4";
p4.Value = 1;
cmd.Parameters.Add(p4);
IDataParameter p5 = cmd.CreateParameter();
p5.DbType = DbType.String;
p5.Direction = ParameterDirection.Input;
p5.ParameterName = ":p5";
p5.Value = "p5";
cmd.Parameters.Add(p5);
IDataParameter p6 = cmd.CreateParameter();
p6.DbType = DbType.DateTime;
p6.Direction = ParameterDirection.Input;
p6.ParameterName = ":p6";
p6.Value = DateTime.Now;
cmd.Parameters.Add(p6);
IDataParameter p7 = cmd.CreateParameter();
p7.DbType = DbType.String;
p7.Direction = ParameterDirection.Input;
p7.ParameterName = ":p7";
p7.Value = "p7";
cmd.Parameters.Add(p7);
IDataParameter p8 = cmd.CreateParameter();
p8.DbType = DbType.String;
p8.Direction = ParameterDirection.Input;
p8.ParameterName = ":p8";
p8.Value = "p8";
cmd.Parameters.Add(p8);
IDataParameter p9 = cmd.CreateParameter();
p9.DbType = DbType.String;
p9.Direction = ParameterDirection.Input;
p9.ParameterName = ":p9";
p9.Value = "p9";
cmd.Parameters.Add(p9);
IDataParameter p10 = cmd.CreateParameter();
p10.DbType = DbType.String;
p10.Direction = ParameterDirection.Input;
p10.ParameterName = ":p10";
p10.Value = "p10";
cmd.Parameters.Add(p10);
cmd.ExecuteNonQuery();
}
}