I used stored proceture in the Oracle. How can I use out parameteres of the SP in C# code?
I use the following code for it:
OracleSP
PROCEDURE TABMPWORKREQUEST_INS(INTWORKREQUEST_ IN NUMBER, VCWORKREQUESTNUMBER_ OUT VARCHAR2,
INTREQUESTEDBY_ IN NUMBER, INTWORKTYPE_ IN NUMBER,INTACTIONTYPE_ IN NUMBER,
INTPRIORITY_ IN NUMBER, INTRECORDID_ IN NUMBER, INTPERMITREQUIRED_ IN NUMBER, VCINSPECTIONREQUIRED_ IN CHAR,
VCLASTUSERID_ IN VARCHAR2, INTCOSTCENTRECODE_ IN NUMBER, VCBUDGETHEAD_ IN VARCHAR2
, VCREFERENCEWORKORDERNUMBER_ IN VARCHAR2, VCJOBDESCRIPTION_ IN VARCHAR2,
VCSTATUS_ IN CHAR, VCHISTORYFLAG_ IN CHAR, VCREASONCODE_ IN VARCHAR2
, VCMSSNUMBER_ IN VARCHAR2, INTAUTHORIZELEVEL_ IN NUMBER, INTPRINTFLAG_ IN NUMBER,
INTINSPECTIONFLAG_ IN NUMBER, INTDAYNUMBER_ IN NUMBER, INTDURATION_ IN NUMBER, INTPROGRESS_ IN NUMBER,
INTWORKSHOPFLAG_ IN CHAR)
IS
EXISTANCE number;
UID_ VARCHAR(5);
SS varchar2(20);
BEGIN
SELECT COUNT(*) into EXISTANCE FROM TABMPWORKREQUEST WHERE INTWORKREQUEST = INTWORKREQUEST_ ;
IF VCLASTUSERID_ = '0' THEN
UID_ := 'U2';
ELSE
UID_ := VCLASTUSERID_;
END IF;
select GetWorkOrderNumber(INTREQUESTEDBY_) INTO SS from dual ;
VCWORKREQUESTNUMBER_ :=SS;
INSERT INTO TABMPWORKREQUEST
VALUES( CMMS.SEQTABMPWORKREQUEST.NEXTVAL , SS , sysdate , INTrequestedby_ ,
INTworktype_ , INTactiontype_ , INTpriority_ , Intrecordid_ , Intpermitrequired_ , Vcinspectionrequired_ ,
Vclastuserid_ , SYSDATE , sysdate , INTcostcentrecode_ , Vcbudgethead_ , sysdate ,
Vcreferenceworkordernumber_ , Vcjobdescription_ , '01' , Vchistoryflag_ , Vcreasoncode_ ,
sysdate , sysdate , Vcmssnumber_ , Intauthorizelevel_ , Intprintflag_ , Intinspectionflag_ ,
Intdaynumber_ , Intduration_ , Intprogress_ , Intworkshopflag_ );
END TABMPWORKREQUEST_INS;
and c# code
public bool InsertMpWORKREQUEST(ClsFieldsMpWORKREQUEST fieldsMpWORKREQUEST)
{
string DTBDDATETIME;
if (fieldsMpWORKREQUEST.DTBDDATETIME != null)
{
DTBDDATETIME = String.Format("{0:dd/MM/yyyy HH:mm:ss}", fieldsMpWORKREQUEST.DTBDDATETIME);
DTBDDATETIME = "TO_DATE('" + DTBDDATETIME + "', 'MM/DD/YYYY HH24:MI:SS')";
}
else
{
DTBDDATETIME = "NULL";
}
string DateExpire = string.Empty;
if (fieldsMpWORKREQUEST.DTEXPIRYDATE != null)
{
DateExpire = String.Format("{0:dd/MM/yyyy HH:mm:ss}", fieldsMpWORKREQUEST.DTEXPIRYDATE);
DateExpire = "TO_DATE('" + DateExpire + "', 'MM/DD/YYYY HH24:MI:SS')";
}
else
{
DateExpire = "NULL";
}
string DtStartDate;
if (fieldsMpWORKREQUEST.DTSTARTDATE != null)
{
DtStartDate = String.Format("{0:dd/MM/yyyy HH:mm:ss}", fieldsMpWORKREQUEST.DTSTARTDATE);
DtStartDate = "TO_DATE('" + DtStartDate + "', 'MM/DD/YYYY HH24:MI:SS')";
}
else
{
DtStartDate = "NULL";
}
string DtComplementationDate;
if (fieldsMpWORKREQUEST.DTCOMPLETIONDATE != null)
{
DtComplementationDate = String.Format("{0:dd/MM/yyyy HH:mm:ss}", fieldsMpWORKREQUEST.DTCOMPLETIONDATE);
DtComplementationDate = "TO_DATE('" + DtComplementationDate + "', 'MM/DD/YYYY HH24:MI:SS')";
}
else
{
DtComplementationDate = "NULL";
}
OracleConnection cn = new OracleConnection(this.GetConnectionString());
OracleCommand cmd = new OracleCommand("CMMS_MPWORKREQUEST_PKG.TABMPWORKREQUEST_INS", cn);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter INTWORKREQUEST = new OracleParameter();
INTWORKREQUEST.ParameterName = "INTWORKREQUEST_";
INTWORKREQUEST.OracleType = OracleType.Number;
INTWORKREQUEST.Direction = ParameterDirection.Input;
INTWORKREQUEST.Value = fieldsMpWORKREQUEST.INTWORKREQUEST;
cmd.Parameters.Add(INTWORKREQUEST);
// cmd.Parameters.AddWithValue("VCWORKREQUESTNUMBER_", fieldsMpWORKREQUEST.VCWORKREQUESTNUMBER).Direction=ParameterDirection.Output;
OracleParameter requestNo = new OracleParameter();
requestNo.ParameterName = "VCWORKREQUESTNUMBER_";
requestNo.OracleType = OracleType.VarChar;
requestNo.Size = 14;
requestNo.Direction = ParameterDirection.Output;
cmd.Parameters.Add(requestNo);
OracleParameter INTREQUESTEDBY = new OracleParameter();
INTREQUESTEDBY.ParameterName = "INTREQUESTEDBY_";
INTREQUESTEDBY.OracleType = OracleType.Number;
INTREQUESTEDBY.Direction = ParameterDirection.Input;
INTREQUESTEDBY.Value = fieldsMpWORKREQUEST.INTREQUESTEDBY.INTSECTIONID;
cmd.Parameters.Add(INTREQUESTEDBY);
OracleParameter INTWORKTYPE = new OracleParameter();
INTWORKTYPE.ParameterName = "INTWORKTYPE_";
INTWORKTYPE.OracleType = OracleType.Number;
INTWORKTYPE.Direction = ParameterDirection.Input;
INTWORKTYPE.Value = fieldsMpWORKREQUEST.INTREQUESTEDBY.INTSECTIONID;
cmd.Parameters.Add(INTWORKTYPE);
OracleParameter INTACTIONTYPE = new OracleParameter();
INTACTIONTYPE.ParameterName = "INTACTIONTYPE_";
INTACTIONTYPE.OracleType = OracleType.Number;
INTACTIONTYPE.Direction = ParameterDirection.Input;
INTACTIONTYPE.Value = fieldsMpWORKREQUEST.INTACTIONTYPE.INTSECTIONID;
cmd.Parameters.Add(INTACTIONTYPE);
OracleParameter INTPRIORITY = new OracleParameter();
INTPRIORITY.ParameterName = "INTPRIORITY_";
INTPRIORITY.OracleType = OracleType.Number;
INTPRIORITY.Direction = ParameterDirection.Input;
INTPRIORITY.Value = fieldsMpWORKREQUEST.INTPRIORITY.INTGENERALCODE;
cmd.Parameters.Add(INTPRIORITY);
OracleParameter INTRECORDID = new OracleParameter();
INTRECORDID.ParameterName = "INTRECORDID_";
INTRECORDID.OracleType = OracleType.Number;
INTRECORDID.Direction = ParameterDirection.Input;
INTRECORDID.Value = fieldsMpWORKREQUEST.INTRECORDID;
cmd.Parameters.Add(INTRECORDID);
OracleParameter INTPERMITREQUIRED = new OracleParameter();
INTPERMITREQUIRED.ParameterName = "INTPERMITREQUIRED_";
INTPERMITREQUIRED.OracleType = OracleType.Number;
INTPERMITREQUIRED.Direction = ParameterDirection.Input;
INTPERMITREQUIRED.Value = fieldsMpWORKREQUEST.INTPERMITREQUIRED;
cmd.Parameters.Add(INTPERMITREQUIRED);
OracleParameter VCINSPECTIONREQUIRED = new OracleParameter();
VCINSPECTIONREQUIRED.ParameterName = "VCINSPECTIONREQUIRED_";
VCINSPECTIONREQUIRED.OracleType = OracleType.Char;
VCINSPECTIONREQUIRED.Direction = ParameterDirection.Input;
VCINSPECTIONREQUIRED.Value = fieldsMpWORKREQUEST.VCINSPECTIONREQUIRED;
cmd.Parameters.Add(VCINSPECTIONREQUIRED);
OracleParameter VCLASTUSERID = new OracleParameter();
VCLASTUSERID.ParameterName = "VCLASTUSERID_";
VCLASTUSERID.OracleType = OracleType.VarChar;
VCLASTUSERID.Direction = ParameterDirection.Input;
VCLASTUSERID.Value = fieldsMpWORKREQUEST.VCLASTUSERID;
cmd.Parameters.Add(VCLASTUSERID);
OracleParameter INTCOSTCENTRECODE = new OracleParameter();
INTCOSTCENTRECODE.ParameterName = "INTCOSTCENTRECODE_";
INTCOSTCENTRECODE.OracleType = OracleType.Number;
INTCOSTCENTRECODE.Direction = ParameterDirection.Input;
INTCOSTCENTRECODE.Value = fieldsMpWORKREQUEST.INTCOSTCENTRECODE.INTGENERALCODE;
cmd.Parameters.Add(INTCOSTCENTRECODE);
OracleParameter vCBUDGETHEAD = new OracleParameter();
vCBUDGETHEAD.ParameterName = "VCBUDGETHEAD_";
vCBUDGETHEAD.OracleType = OracleType.VarChar;
vCBUDGETHEAD.Direction = ParameterDirection.Input;
vCBUDGETHEAD.Value = fieldsMpWORKREQUEST.VCBUDGETHEAD;
cmd.Parameters.Add(vCBUDGETHEAD);
OracleParameter VCREFERENCEWORKORDERNUMBER = new OracleParameter();
VCREFERENCEWORKORDERNUMBER.ParameterName = "VCREFERENCEWORKORDERNUMBER_";
VCREFERENCEWORKORDERNUMBER.OracleType = OracleType.VarChar;
VCREFERENCEWORKORDERNUMBER.Direction = ParameterDirection.Input;
VCREFERENCEWORKORDERNUMBER.Value = fieldsMpWORKREQUEST.VCREFERENCEWORKORDERNUMBER;
cmd.Parameters.Add(VCREFERENCEWORKORDERNUMBER);
OracleParameter VCJOBDESCRIPTION = new OracleParameter();
VCJOBDESCRIPTION.ParameterName = "VCJOBDESCRIPTION_";
VCJOBDESCRIPTION.OracleType = OracleType.VarChar;
VCJOBDESCRIPTION.Direction = ParameterDirection.Input;
VCJOBDESCRIPTION.Value = fieldsMpWORKREQUEST.VCJOBDESCRIPTION;
cmd.Parameters.Add(VCJOBDESCRIPTION);
OracleParameter VCSTATUS = new OracleParameter();
VCSTATUS.ParameterName = "VCSTATUS_";
VCSTATUS.OracleType = OracleType.Char;
VCSTATUS.Direction = ParameterDirection.Input;
VCSTATUS.Value = fieldsMpWORKREQUEST.VCSTATUS;
cmd.Parameters.Add(VCSTATUS);
OracleParameter VCHISTORYFLAG = new OracleParameter();
VCHISTORYFLAG.ParameterName = "VCHISTORYFLAG_";
VCHISTORYFLAG.OracleType = OracleType.Char;
VCHISTORYFLAG.Direction = ParameterDirection.Input;
VCHISTORYFLAG.Value = fieldsMpWORKREQUEST.VCHISTORYFLAG;
cmd.Parameters.Add(VCHISTORYFLAG);
OracleParameter VCREASONCODE = new OracleParameter();
VCREASONCODE.ParameterName = "VCREASONCODE_";
VCREASONCODE.OracleType = OracleType.VarChar;
VCREASONCODE.Direction = ParameterDirection.Input;
VCREASONCODE.Value = fieldsMpWORKREQUEST.VCREASONCODE;
cmd.Parameters.Add(VCREASONCODE);
OracleParameter VCMSSNUMBER = new OracleParameter();
VCMSSNUMBER.ParameterName = "VCMSSNUMBER_";
VCMSSNUMBER.OracleType = OracleType.VarChar;
VCMSSNUMBER.Direction = ParameterDirection.Input;
VCMSSNUMBER.Value = fieldsMpWORKREQUEST.VCMSSNUMBER;
cmd.Parameters.Add(VCMSSNUMBER);
OracleParameter INTAUTHORIZELEVEL = new OracleParameter();
INTAUTHORIZELEVEL.ParameterName = "INTAUTHORIZELEVEL_";
INTAUTHORIZELEVEL.OracleType = OracleType.Number;
INTAUTHORIZELEVEL.Direction = ParameterDirection.Input;
INTAUTHORIZELEVEL.Value = fieldsMpWORKREQUEST.INTAUTHORIZELEVEL;
cmd.Parameters.Add(INTAUTHORIZELEVEL);
OracleParameter INTPRINTFLAG = new OracleParameter();
INTPRINTFLAG.ParameterName = "INTPRINTFLAG_";
INTPRINTFLAG.OracleType = OracleType.Number;
INTPRINTFLAG.Direction = ParameterDirection.Input;
INTPRINTFLAG.Value = fieldsMpWORKREQUEST.INTPRINTFLAG;
cmd.Parameters.Add(INTPRINTFLAG);
OracleParameter INTINSPECTIONFLAG = new OracleParameter();
INTINSPECTIONFLAG.ParameterName = "INTINSPECTIONFLAG_";
INTINSPECTIONFLAG.OracleType = OracleType.Number;
INTINSPECTIONFLAG.Direction = ParameterDirection.Input;
INTINSPECTIONFLAG.Value = fieldsMpWORKREQUEST.INTINSPECTIONFLAG;
cmd.Parameters.Add(INTINSPECTIONFLAG);
OracleParameter INTDAYNUMBER = new OracleParameter();
INTDAYNUMBER.ParameterName = "INTDAYNUMBER_";
INTDAYNUMBER.OracleType = OracleType.Number;
INTDAYNUMBER.Direction = ParameterDirection.Input;
INTDAYNUMBER.Value = fieldsMpWORKREQUEST.INTDAYNUMBER;
cmd.Parameters.Add(INTDAYNUMBER);
OracleParameter INTDURATION = new OracleParameter();
INTDURATION.ParameterName = "INTDURATION_";
INTDURATION.OracleType = OracleType.Number;
INTDURATION.Direction = ParameterDirection.Input;
INTDURATION.Value = fieldsMpWORKREQUEST.INTDURATION;
cmd.Parameters.Add(INTDURATION);
OracleParameter INTPROGRESS = new OracleParameter();
INTPROGRESS.ParameterName = "INTPROGRESS_";
INTPROGRESS.OracleType = OracleType.Number;
INTPROGRESS.Direction = ParameterDirection.Input;
INTPROGRESS.Value = fieldsMpWORKREQUEST.INTPROGRESS;
cmd.Parameters.Add(INTPROGRESS);
OracleParameter INTWORKSHOPFLAG = new OracleParameter();
INTWORKSHOPFLAG.ParameterName = "INTWORKSHOPFLAG_";
INTWORKSHOPFLAG.OracleType = OracleType.Char;
INTWORKSHOPFLAG.Direction = ParameterDirection.Input;
INTWORKSHOPFLAG.Value = fieldsMpWORKREQUEST.INTWORKSHOPFLAG;
cmd.Parameters.Add(INTWORKSHOPFLAG);
string requestNo2 = string.Empty;
int rowEfect = 0;
try
{
cn.Open();
rowEfect = cmd.ExecuteNonQuery();
requestNo2 = cmd.Parameters["VCWORKREQUESTNUMBER_"].Value.ToString();
}
catch (Exception)
{
throw;
}
finally
{
if (cn.State != ConnectionState.Closed) cn.Close();
}
return rowEfect > 0;
}
But I get the following error:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'TABMPWORKREQUEST_INS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
How can I fix this problem?
The objects in the MSFT System.Data namespace relating to Oracle have been deprecated:
http://msdn.microsoft.com/en-us/library/77d8yct7.aspx
But there is a generic System.Data.OleDB namespace:
http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparameter.direction(v=VS.71).aspx
The Command object has a parameters collection. Each parameter has several properties, including a direction property. When you set that property to output, you can examine its value after issuing the command.
A third-party provider for Oracle should offer something similar.
P.S. I haven't used the OleDB namespace in a very long time -- but recall that the parameters had to be added to the Parameters collection in the same order in which they appear in the SP's signature. Be sure to set the appropriate datatype for each parameter and provide the length of any text values being passed.
P.P.S. It looks as though you're adding the same parameter to the collection twice; is one of these supposed to be commented out?
cmd.Parameters.AddWithValue("VCWORKREQUESTNUMBER_",fieldsMpWORKREQUEST.VCWORKREQUESTNUMBER).Direction=ParameterDirection.Output;
OracleParameter requestNo = new OracleParameter();
requestNo.ParameterName = "VCWORKREQUESTNUMBER_";
requestNo.OracleType = OracleType.VarChar;
requestNo.Size = 14;
requestNo.Direction = ParameterDirection.Output;
cmd.Parameters.Add(requestNo);