在C# 2008中调用 Oracle11g 的过程出错,哪出问题了,找了半天没解决。请帮忙!
过程头:
01.PROCEDURE PDICT_TABLETYPE(
02. iOperateType IN NUMBER,
03. v_iTypeId IN OUT NUMBER,
04. v_sTypeName IN VARCHAR2)
05.IS
06.
复制代码在PL/SQL中调用,是没问题的。
以下是 C#调用代码
01.OracleConnection DBCon = new OracleConnection(DBCONNStr);
02. try
03. {
04. OracleCommand DBCmd = new OracleCommand("PDICT_TABLETYPE", DBCon);
05. DBCmd.Parameters.Add(new OracleParameter("IOPERATETYPE", OracleType.Number)).Value = Int32.Parse(iOperateType.ToString()); //1添加2修改3删除
06. OracleParameter pTypeID = new OracleParameter("V_ITYPEID", OracleType.Number);
07. pTypeID.Direction = ParameterDirection.InputOutput;
08. pTypeID.Value = Int32.Parse(v_iTypeId.ToString());
09. DBCmd.Parameters.Add(pTypeID);
10. //DBCmd.Parameters.Add(new OracleParameter("V_ITYPEID", OracleType.Number)).Value = 1;
11. DBCmd.Parameters.Add(new OracleParameter("V_STYPENAME", OracleType.VarChar)).Value = v_sTypeName.ToString();
12. if (DBCon.State == ConnectionState.Closed) DBCon.Open();
13. DBCmd.ExecuteNonQuery();
14. return true;
15. }
16. catch (Exception Ex)
17. {
18. //return false;
19. throw Ex;
20. }
21. finally
22. {
23. DBCon.Close();
24. }
复制代码
执行到 : DBCmd.ExecuteNonQuery(); 报错了!
是哪出问题了??
原贴:http://www.itpub.net/thread-1560917-1-1.html
ITPUT 世态炎凉!
过程头:
01.PROCEDURE PDICT_TABLETYPE(
02. iOperateType IN NUMBER,
03. v_iTypeId IN OUT NUMBER,
04. v_sTypeName IN VARCHAR2)
05.IS
06.
复制代码在PL/SQL中调用,是没问题的。
以下是 C#调用代码
01.OracleConnection DBCon = new OracleConnection(DBCONNStr);
02. try
03. {
04. OracleCommand DBCmd = new OracleCommand("PDICT_TABLETYPE", DBCon);
05. DBCmd.Parameters.Add(new OracleParameter("IOPERATETYPE", OracleType.Number)).Value = Int32.Parse(iOperateType.ToString()); //1添加2修改3删除
06. OracleParameter pTypeID = new OracleParameter("V_ITYPEID", OracleType.Number);
07. pTypeID.Direction = ParameterDirection.InputOutput;
08. pTypeID.Value = Int32.Parse(v_iTypeId.ToString());
09. DBCmd.Parameters.Add(pTypeID);
10. //DBCmd.Parameters.Add(new OracleParameter("V_ITYPEID", OracleType.Number)).Value = 1;
11. DBCmd.Parameters.Add(new OracleParameter("V_STYPENAME", OracleType.VarChar)).Value = v_sTypeName.ToString();
12. if (DBCon.State == ConnectionState.Closed) DBCon.Open();
13. DBCmd.ExecuteNonQuery();
14. return true;
15. }
16. catch (Exception Ex)
17. {
18. //return false;
19. throw Ex;
20. }
21. finally
22. {
23. DBCon.Close();
24. }
复制代码
执行到 : DBCmd.ExecuteNonQuery(); 报错了!
是哪出问题了??
原贴:http://www.itpub.net/thread-1560917-1-1.html
ITPUT 世态炎凉!
[System.Data.OracleClient.OracleException] = {"ORA-01036: 非法的变量名/编号\n"}
create or replace PROCEDURE PDICT_TABLETYPE(
iOperateType IN NUMBER,
v_iTypeId IN OUT NUMBER,
v_sTypeName IN VARCHAR2)
IS
BEGIN
--添加数据
IF iOperateType=1 THEN
SELECT MAX(iTypeId) INTO v_iTypeId FROM tDICT_TableType;
IF v_iTypeId IS NULL THEN
v_iTypeId:=1;
ELSE
v_iTypeId:=v_iTypeId+1;
END IF;
INSERT INTO tDICT_TableType VALUES (v_iTypeId,v_sTypeName);
END IF;
--修改数据
IF iOperateType=2 THEN
UPDATE tDICT_TableType SET sTypeName=v_sTypeName WHERE iTypeId=v_iTypeId;
END IF; --删除数据
IF iOperateType=3 THEN
DELETE FROM tdict_tabletype WHERE itypeid=v_itypeid;
END IF;
END PDICT_TABLETYPE;
CREATE TABLE "SYSTEM"."TDICT_TABLETYPE"
("ITYPEID" NUMBER(2,0),
"STYPENAME" VARCHAR2(10 BYTE)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT);
try
{
OracleCommand DBCmd = new OracleCommand("PDICT_TABLETYPE", DBCon);
DBCmd.Parameters.Add(new OracleParameter(":iOperateType", OracleType.Number)).Value = Int32.Parse(iOperateType.ToString()); //1添加2修改3删除
OracleParameter pTypeID = new OracleParameter(":v_iTypeId", OracleType.Number);
pTypeID.Direction = ParameterDirection.InputOutput;
pTypeID.Value = Int32.Parse(v_iTypeId.ToString());
DBCmd.Parameters.Add(pTypeID);
//DBCmd.Parameters.Add(new OracleParameter(":V_ITYPEID", OracleType.Number)).Value = 1;
DBCmd.Parameters.Add(new OracleParameter(":v_sTypeName", OracleType.VarChar)).Value = v_sTypeName;
if (DBCon.State == ConnectionState.Closed) DBCon.Open();
DBCmd.ExecuteNonQuery();
return true;
}
catch (Exception Ex)
{
//return false;
throw Ex;
}
finally
{
DBCon.Close();
}
(
v_id number,
v_name varchar2
)
is
begin
insert into test(id,name) values(v_id,v_name);
commit;
end; OracleConnection conn = new OracleConnection("server=cw;uid=ls0019999;pwd=aaaaaa");
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "proc2";
cmd.Parameters.Add("v_id", OracleType.Number).Direction = ParameterDirection.Input;//指明传入的参数是输入给oracle存储过程用的
cmd.Parameters["v_id"].Value =this.textBox1.Text.Trim();
cmd.Parameters.Add("v_name",OracleType.NVarChar).Direction = ParameterDirection.Input;//指明传入的参数是输入给oracle存储过程用的
cmd.Parameters["v_name"].Value =this.textBox2.Text.Trim();
cmd.ExecuteNonQuery();
conn.Close();
create or replace procedure proc2
(
v_id number,
v_name varchar2
)
is
begin
insert into test(id,name) values(v_id,v_name);
commit;
end; OracleConnection conn = new OracleConnection("server=cw;uid=ls0019999;pwd=aaaaaa");
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "proc2";
cmd.Parameters.Add("v_id", OracleType.Number).Direction = ParameterDirection.Input;//指明传入的参数是输入给oracle存储过程用的
cmd.Parameters["v_id"].Value =this.textBox1.Text.Trim();
cmd.Parameters.Add("v_name",OracleType.NVarChar).Direction = ParameterDirection.Input;//指明传入的参数是输入给oracle存储过程用的
cmd.Parameters["v_name"].Value =this.textBox2.Text.Trim();
cmd.ExecuteNonQuery();
conn.Close();
1.有时,人就是一叶障目,关键还是从多方面考虑!
2.人是不能偷懒的,概怎做,就怎做,尤其是在不同厂家产品不兼容的基础上。最后调用代码,供同任们学习参考:OracleConnection DBCon = new OracleConnection(DBCONNStr);
try
{
OracleCommand DBCmd = new OracleCommand();
DBCmd.Connection = DBCon;
DBCmd.CommandType = CommandType.StoredProcedure;
DBCmd.CommandText = "PDICT_TABLETYPE"; DBCmd.Parameters.Add(new OracleParameter("iOperateType", OracleType.Number)).Value = Int32.Parse(iOperateType.ToString()); //1添加2修改3删除
OracleParameter pTypeID = new OracleParameter("v_iTypeId", OracleType.Number);
pTypeID.Direction = ParameterDirection.InputOutput;
if (v_iTypeId != null)
pTypeID.Value = Int32.Parse(v_iTypeId.ToString());
else
pTypeID.Value = DBNull.Value;
DBCmd.Parameters.Add(pTypeID);
OracleParameter pTypeName = new OracleParameter("v_sTypeName", OracleType.NVarChar);
pTypeName.Direction = ParameterDirection.Input;
if (v_sTypeName != null)
pTypeName.Value = v_sTypeName.ToString();
else
pTypeName.Value = DBNull.Value;
DBCmd.Parameters.Add(pTypeName);
if (DBCon.State == ConnectionState.Closed) DBCon.Open();
DBCmd.ExecuteNonQuery();
return true;
}
catch (Exception Ex)
{
//return false;
throw Ex;
}
finally
{
DBCon.Close();
}分共享了!再次感谢回复的朋友 们!!