C#调用ORACLE PROCEDURE 返回值只有实际信息的一半，请大神指定，谢谢！ 代码如下。

[布丁88]

public partial class Form1 : Form
{
private OracleConnection con; //创建连接对象
public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{
try
{
if (con == null) //判断连接对象是否为空
{
con = new OracleConnection("User Id=SJ;Password=DONTZ1110;Data Source=roxlinclient;"); //创建数据库连接对象
}
if (con.State == ConnectionState.Closed) //判断数据库连接是否关闭
con.Open(); //打开数据库连接
}
catch (Exception ex)
{
MessageBox.Show( "Open DB Erro:" + ex.Message);
}
}

private void button1_Click(object sender, EventArgs e)
{
OracleCommand cmd = new OracleCommand("SAJET.SJ_WO_MATERIAL4", con);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter param1 = new OracleParameter("TWO", OracleType.VarChar, 30);
param1.Direction = ParameterDirection.Input;
param1.Value="TEST";
cmd.Parameters.Add(param1);
OracleParameter param2 = new OracleParameter("TKPNO", OracleType.VarChar, 30);
param2.Direction = ParameterDirection.Input;
param2.Value = "63147020002-HC2";
cmd.Parameters.Add(param2);
OracleParameter param3 = new OracleParameter("TDATECODE", OracleType.VarChar, 30);
param3.Direction = ParameterDirection.Input;
param3.Value = "20131120";
cmd.Parameters.Add(param3);
OracleParameter param4 = new OracleParameter("TREELNO", OracleType.VarChar, 30);
param4.Direction = ParameterDirection.Input;
param4.Value = "A00008975698";
cmd.Parameters.Add(param4);
OracleParameter param5 = new OracleParameter("TPDLINEID", OracleType.VarChar, 30);
param5.Direction = ParameterDirection.Input;
param5.Value = "20092";
cmd.Parameters.Add(param5);
OracleParameter param6 = new OracleParameter("TPROCESSID", OracleType.VarChar, 30);
param6.Direction = ParameterDirection.Input;
param6.Value = "200007";
cmd.Parameters.Add(param6);
OracleParameter param7 = new OracleParameter("TEMPID", OracleType.VarChar, 30);
param7.Direction = ParameterDirection.Input;
param7.Value = "20001511";
cmd.Parameters.Add(param7);
OracleParameter param8 = new OracleParameter("TVENDOR", OracleType.VarChar, 30);
param8.Direction = ParameterDirection.Input;
param8.Value = "0001848U";
cmd.Parameters.Add(param8);
OracleParameter param9 = new OracleParameter("TQTY", OracleType.VarChar, 30);
param9.Direction = ParameterDirection.Input;
param9.Value = "900";
cmd.Parameters.Add(param9);
OracleParameter param10 = new OracleParameter("TRES", OracleType.VarChar,100);
param10.Direction = ParameterDirection.Output;
cmd.Parameters.Add(param10);
cmd.ExecuteNonQuery();
String res = cmd.Parameters[9].Value.ToString();
MessageBox.Show(res);
}
}

[布丁88]

PROCEDURE 已经贴出 请指点！

[beyon2008]

过程太长了，不好看，建议你在过程加一个返回参：prm_OUTRESULT OUT sys_refcursor 通过open prm_OUTRESULT for select ……的方式调试

[布丁88]

问题解决了，谢谢大家的帮助。问题现象和解决方法如下： 1：我的系统是64BIT WIN7 2：安装ORACLE 客户端为32位ORACLE 12C, 更改后为：ORACLE 11G 64BIT CLIENT 3：ORACLE 数据库为 ORACLE 11G 4：修改方法将项目属性 生成->目标平台 为ANY CPU ,去掉首选32前面的 对号。 问题解决！

[布丁88]

请大神指点，顶！

[wangchangming]

贴上过程SAJET.SJ_WO_MATERIAL4

[布丁88]

CREATE OR REPLACE PROCEDURE SAJET.SJ_WO_MATERIAL4 ( TWO IN VARCHAR2, TKPNO IN VARCHAR2, TDATECODE IN VARCHAR2, TREELNO IN VARCHAR2, TPDLINEID IN VARCHAR2, TPROCESSID IN VARCHAR2, TEMPID IN VARCHAR2, TVENDOR IN VARCHAR2, TQTY IN VARCHAR2, TRES OUT NVARCHAR2 ) IS C_EMPID NUMBER; C_CNT NUMBER; CQTY NUMBER; C_QTY NUMBER; T_QTY NUMBER; TARGETQTY NUMBER; --WO COUNT INPUTQTY NUMBER; --INPUT COUNT QTY NUMBER; --WILL MAKE COUNT M_QTY NUMBER; CVENDORID NUMBER; V_Code SAJET.SUPPLIER_VENDOR.VENDOR_CODE%type; V_Name SAJET.SUPPLIER_VENDOR.VENDOR_NAME%type; C_KPID SAJET.SYS_PART.PART_ID%TYPE; C_STAGE SAJET.SYS_PROCESS.STAGE_ID%TYPE; C_WOSEQ SMT.G_WO_MSL.WO_SEQUENCE%TYPE; C_DATECODE2 SMT.G_SMT_STATUS.DATECODE%TYPE; BEGIN TRES:='OK'; C_CNT:=0; C_WOSEQ:='00'||TWO; CQTY:=TO_NUMBER(TQTY); IF (CQTY > 0) THEN TRES:='OK'; ELSE TRES:='QTY ERR!'; END IF; -- GET WO TARGET QTY BY QI SELECT TARGET_QTY,INPUT_QTY INTO TARGETQTY,INPUTQTY FROM SAJET.G_WO_BASE WHERE WORK_ORDER=TWO; QTY:=TARGETQTY-INPUTQTY; IF QTY<=0 THEN TRES:='WO FULL !'; GOTO ENDP; END IF; --CHECK METRAL ID QTY BEGIN SELECT SUM(QTY) INTO M_QTY FROM SMT.G_SMT_TRAVEL WHERE REEL_NO=TREELNO; IF M_QTY>=CQTY THEN TRES:='數量不足,請更換倉庫發料ID！'; goto endp; END IF; EXCEPTION WHEN OTHERS THEN TRES:='OK'; END; IF CQTY>TARGETQTY THEN CQTY:=TARGETQTY; END IF; SELECT STAGE_ID INTO C_STAGE FROM SAJET.SYS_PROCESS WHERE PROCESS_ID=TPROCESSID; SELECT PART_ID INTO C_KPID FROM SAJET.SYS_PART WHERE PART_NO=TKPNO; --Check Vendor ID by qi BEGIN SELECT VENDOR_ID INTO CVENDORID FROM SAJET.SYS_VENDOR WHERE VENDOR_CODE=TVENDOR; EXCEPTION WHEN OTHERS THEN BEGIN select vendor_code,vendor_name into v_code,v_name from SAJET.SUPPLIER_VENDOR where vendor_code=TVENDOR; select max(vendor_id) into cvendorid from sajet.sys_vendor; cvendorid:=cvendorid+1; insert into sajet.sys_vendor (vendor_id,vendor_code,vendor_name,update_userid,update_time,enabled ) values(cvendorid,v_code,v_name,tempid,sysdate,'Y'); COMMIT; --TRES:='VENDOR ERRO !'; --GOTO ENDP; END; END; SELECT COUNT(*) INTO C_CNT FROM SMT.G_WO_MSL WHERE WORK_ORDER=TWO AND WO_SEQUENCE=C_WOSEQ; IF C_CNT=0 THEN INSERT INTO SMT.G_WO_MSL (WORK_ORDER,WO_SEQUENCE,EMP_ID,STATUS)VALUES (TWO,C_WOSEQ,TEMPID,'SMTLOADING'); END IF; SELECT COUNT(*) INTO C_CNT FROM SMT.G_WO_MSL_DETAIL WHERE WO_SEQUENCE=C_WOSEQ AND ITEM_PART_ID=C_KPID; IF C_CNT=0 THEN INSERT INTO SMT.G_WO_MSL_DETAIL (WO_SEQUENCE,ITEM_PART_ID,STATION_NO) VALUES (C_WOSEQ,C_KPID,'DIP'); END IF; BEGIN SELECT DATECODE INTO C_DATECODE2 FROM SMT.G_SMT_STATUS WHERE WO_SEQUENCE=C_WOSEQ AND ITEM_PART_ID=C_KPID AND pdline_id=TPDLINEID AND PROCESS_ID=TPROCESSID AND ROWNUM=1; C_CNT:=1; EXCEPTION WHEN OTHERS THEN C_CNT:=0; END; IF (C_CNT=1) THEN BEGIN BEGIN SELECT QTY INTO T_QTY FROM SMT.G_SMT_STATUS WHERE WO_SEQUENCE=C_WOSEQ AND ITEM_PART_ID=C_KPID AND pdline_id=TPDLINEID AND PROCESS_ID=TPROCESSID AND QTY>0; IF T_QTY > 0 THEN TRES:='當前無需上料！'; goto endp; END IF; EXCEPTION WHEN OTHERS THEN TRES:='OK'; END; SELECT SUM(QTY) INTO M_QTY FROM SMT.G_SMT_TRAVEL WHERE WO_SEQUENCE=C_WOSEQ AND ITEM_PART_ID=C_KPID AND pdline_id=TPDLINEID AND PROCESS_ID=TPROCESSID; --CHECK METRAL MORE IF M_QTY>=TARGETQTY THEN TRES:='料件已滿，無需上料！'; goto ENDP; ELSIF ((CQTY+M_QTY)>TARGETQTY) THEN C_QTY:=TARGETQTY-M_QTY; --INSERT INTO SMT.G_SMT_TRAVEL --(WO_SEQUENCE,STATION_NO,ITEM_PART_ID,EMP_ID,pdline_id,STAGE_ID,PROCESS_ID,IN_TIME,DATECODE,REEL_NO,VENDOR_ID,QTY) VALUES --(C_WOSEQ,'DIP',C_KPID,TEMPID,TPDLINEID,C_STAGE,TPROCESSID,SYSDATE,TDATECODE,TREELNO,CVENDORID,C_QTY); ELSE--IF ((CQTY+M_QTY)=TARGETQTY) THEN C_QTY:=CQTY; /*ELSE C_QTY:=CQTY+T_QTY; INSERT INTO SMT.G_SMT_TRAVEL (WO_SEQUENCE,STATION_NO,ITEM_PART_ID,EMP_ID,pdline_id,STAGE_ID,PROCESS_ID,IN_TIME,DATECODE,REEL_NO,VENDOR_ID,QTY) VALUES (C_WOSEQ,'DIP',C_KPID,TEMPID,TPDLINEID,C_STAGE,TPROCESSID,SYSDATE,TDATECODE,TREELNO,CVENDORID,CQTY);*/ END IF; INSERT INTO SMT.G_SMT_TRAVEL (WO_SEQUENCE,STATION_NO,ITEM_PART_ID,EMP_ID,pdline_id,STAGE_ID,PROCESS_ID,IN_TIME,DATECODE,REEL_NO,VENDOR_ID,QTY) VALUES (C_WOSEQ,'DIP',C_KPID,TEMPID,TPDLINEID,C_STAGE,TPROCESSID,SYSDATE,TDATECODE,TREELNO,CVENDORID,C_QTY); DELETE SMT.G_SMT_STATUS WHERE WO_SEQUENCE=C_WOSEQ AND ITEM_PART_ID=C_KPID AND pdline_id=TPDLINEID AND PROCESS_ID=TPROCESSID; INSERT INTO SMT.G_SMT_STATUS (WO_SEQUENCE,STATION_NO,ITEM_PART_ID,EMP_ID,pdline_id,STAGE_ID,PROCESS_ID,IN_TIME,DATECODE,REEL_NO,VENDOR_ID,QTY) VALUES (C_WOSEQ,'DIP',C_KPID,TEMPID,TPDLINEID,C_STAGE,TPROCESSID,SYSDATE,TDATECODE,TREELNO,CVENDORID,C_QTY); EXCEPTION WHEN OTHERS THEN TRES:='UPDATE\DELETE SMT_TRAVEL ERROR!'; END; ELSIF (C_CNT=0) THEN BEGIN INSERT INTO SMT.G_SMT_STATUS (WO_SEQUENCE,STATION_NO,ITEM_PART_ID,EMP_ID,pdline_id,STAGE_ID,PROCESS_ID,IN_TIME,DATECODE,REEL_NO,VENDOR_ID,QTY) VALUES (C_WOSEQ,'DIP',C_KPID,TEMPID,TPDLINEID,C_STAGE,TPROCESSID,SYSDATE,TDATECODE,TREELNO,CVENDORID,CQTY); INSERT INTO SMT.G_SMT_TRAVEL (WO_SEQUENCE,STATION_NO,ITEM_PART_ID,EMP_ID,pdline_id,STAGE_ID,PROCESS_ID,IN_TIME,DATECODE,REEL_NO,VENDOR_ID,QTY) VALUES (C_WOSEQ,'DIP',C_KPID,TEMPID,TPDLINEID,C_STAGE,TPROCESSID,SYSDATE,TDATECODE,TREELNO,CVENDORID,CQTY); EXCEPTION WHEN OTHERS THEN TRES:='INSERT SMT_STATUS TABEL ERROR!'; END; END IF; IF TRES='OK' THEN COMMIT; ELSE ROLLBACK; END IF; <<ENDP>> NULL; EXCEPTION WHEN OTHERS THEN TRES := 'INVALID MATERIAL '; END; /