CODEWITHSUNDEEP
c#oracle-databasestored-proceduresoracle11g
Tanmay
Tanmay

Reputation: 31

Geeting error ORA-01036 while executing Oracle Procedure using C#

Im getting error while executing the oracle Procedure in C#

this is my C# code is pasted below in this there are 30 fields which I want to insert into the database 

OracleCommand orclCMD = new OracleCommand();
                orclCMD.Connection = CGlobalVariable.orclDB.opneCon();
                orclCMD.CommandText = "SPKFIL_HPT_SPECTRO";
                orclCMD.CommandType = CommandType.StoredProcedure;
                orclCMD.Parameters.Add("P_AMPLE_TASK ", OracleType.VarChar).Value = SAMPLE_TASK;
                orclCMD.Parameters.Add("P_TSK_TYPE", OracleType.VarChar).Value = TSK_TYPE;
              orclCMD.Parameters.Add("P_PRG", OracleType.VarChar).Value = PRG;
                orclCMD.Parameters.Add("P_TYPE_STD", OracleType.VarChar).Value = TYPE_STD;
                orclCMD.Parameters.Add("P_QUALITY", OracleType.VarChar).Value = QUALITY;
                orclCMD.Parameters.Add("P_SAMPLE_DATE", OracleType.DateTime).Value = SAMPLE_DATE;
                orclCMD.Parameters.Add("P_SAMPLE_TIME", OracleType.VarChar).Value = SAMPLE_TIME;
                orclCMD.Parameters.Add("P_SID1", OracleType.VarChar).Value = SID1;
                orclCMD.Parameters.Add("P_SID2", OracleType.VarChar).Value = SID2;
                orclCMD.Parameters.Add("P_SID3", OracleType.VarChar).Value = SID3;
                orclCMD.Parameters.Add("P_SID4", OracleType.VarChar).Value = SID4;
                orclCMD.Parameters.Add("P_SID5", OracleType.VarChar).Value = SID5;
                orclCMD.Parameters.Add("P_SID6", OracleType.VarChar).Value = SID6;
                orclCMD.Parameters.Add("P_SID7", OracleType.VarChar).Value = SID7;
                orclCMD.Parameters.Add("P_SID8", OracleType.VarChar).Value = SID8;
                orclCMD.Parameters.Add("P_SID9", OracleType.VarChar).Value = SID9;
                orclCMD.Parameters.Add("P_SID10", OracleType.VarChar).Value = SID10;
                orclCMD.Parameters.Add("P_SAMPLE_TYPE", OracleType.Number).Value = SAMPLE_TYPE;
                orclCMD.Parameters.Add("P_C_2", OracleType.Double).Value = C_2;
                orclCMD.Parameters.Add("P_SI1", OracleType.Double).Value = SI1;
                orclCMD.Parameters.Add("P_MN3", OracleType.Double).Value = MN3;
                orclCMD.Parameters.Add("P_S", OracleType.Double).Value = S;
                orclCMD.Parameters.Add("P_P_1", OracleType.Double).Value = P_1;
                orclCMD.Parameters.Add("P_CU5", OracleType.Double).Value = CU5;
                orclCMD.Parameters.Add("P_CR1", OracleType.Double).Value = CR1;
                orclCMD.Parameters.Add("P_PB", OracleType.Double).Value = PB;
                orclCMD.Parameters.Add("P_SN2", OracleType.Double).Value = SN2;
                orclCMD.Parameters.Add("P_TI4", OracleType.Double).Value = TI4;
                orclCMD.Parameters.Add("P_CE", OracleType.Double).Value = CE;
                orclCMD.Parameters.Add("P_MO2", OracleType.Double).Value = MO2;
                int i = orclCMD.ExecuteNonQuery();

And my here is code for oracle procedure code

CREATE OR REPLACE Procedure SPKFIL_HPT_SPECTRO
(
  P_SAMPLE_TASK  VARCHAR2,
  P_TSK_TYPE     VARCHAR2,
  P_PRG          VARCHAR2,
  P_TYPE_STD     VARCHAR2,
  P_QUALITY      VARCHAR2,
  P_SAMPLE_DATE  DATE,
  P_SAMPLE_TIME  VARCHAR2,
  P_SID1         VARCHAR2,
  P_SID2         VARCHAR2,
  P_SID3         VARCHAR2,
  P_SID4         VARCHAR2,
  P_SID5         VARCHAR2,
  P_SID6         VARCHAR2,
  P_SID7         VARCHAR2,
  P_SID8         VARCHAR2,
  P_SID9         VARCHAR2,
  P_SID10        VARCHAR2,
  P_SAMPLE_TYPE  NUMBER,
  P_C_2          NUMBER,
  P_SI1          NUMBER,
  P_MN3          NUMBER,
  P_S            NUMBER,
  P_P_1          NUMBER,
  P_CU5          NUMBER,
  P_CR1          NUMBER,
  P_PB           NUMBER,
  P_SN2          NUMBER,
  P_TI4          NUMBER,
  P_CE           NUMBER,
  P_MO2          NUMBER )
As
cnt number;
Begin
  select nvl(count(P_SAMPLE_TYPE),0) into cnt from KFIL_HPT_SPECTRO where SAMPLE_TYPE=P_SAMPLE_TYPE;
  if cnt=0 then  
   insert into KFIL_HPT_SPECTRO (SAMPLE_TASK,TSK_TYPE,PRG,TYPE_STD,QUALITY,SAMPLE_DATE,SAMPLE_TIME,SID1,
                                SID2,SID3,SID4,SID5,SID6,SID7,SID8,SID9,SID10,SAMPLE_TYPE,C_2, SI1, MN3,S,p_1, CU5,CR1, PB, SN2,TI4, CE, MO2)
                                Values
                                (P_SAMPLE_TASK,P_TSK_TYPE,P_PRG,P_TYPE_STD,P_QUALITY,P_SAMPLE_DATE,P_SAMPLE_TIME,P_SID1,P_SID2,P_SID3,P_SID4,
                                P_SID5,P_SID6,P_SID7,P_SID8,P_SID9,P_SID10,P_SAMPLE_TYPE,P_C_2,P_SI1,P_MN3,P_S,P_P_1,P_CU5,P_CR1,P_PB,P_SN2,
                                P_TI4,P_CE,P_MO2);
  else
    Update KFIL_HPT_SPECTRO set SAMPLE_TASK = P_SAMPLE_TASK,TSK_TYPE = P_TSK_TYPE,PRG = P_PRG ,TYPE_STD  = P_TYPE_STD  ,QUALITY = P_QUALITY ,
                                SAMPLE_DATE = P_SAMPLE_DATE ,SAMPLE_TIME = P_SAMPLE_TIME ,SID1  = P_SID1  ,SID2  = P_SID2  ,SID3  = P_SID3  ,
                                SID4  = P_SID4  ,SID5  = P_SID5  ,SID6  = P_SID6  ,SID7  = P_SID7  ,SID8  = P_SID8  ,SID9  = P_SID9  ,
                                SID10 = P_SID10 ,C_2 = P_C_2 , SI1  = P_SI1 , MN3  = P_MN3 ,S = P_S ,p_1 = P_P_1 , CU5  = P_CU5 ,CR1 = P_CR1 ,
                                PB = P_PB  , SN2  = P_SN2 ,TI4 = P_TI4 , CE = P_CE  , MO2  = P_MO2 
                                Where  SAMPLE_TYPE = P_SAMPLE_TYPE ;

  end if;

end;

Getting ORA-01036 error code when ExecuteNonQuery() is executed. Please help me for this and thnx in advance

Upvotes: 2

Views: 195

Answers (1)

Veverke
Veverke

Reputation: 11358

The very first parameter in the oracle commmand you are building, refers to a non existing biding variable.

Your .net oracle command object has to refer to the biding variables as you specify them in your actual sql. This is not happening for P_SAMPLE_TASK.

orclCMD.Parameters.Add("P_AMPLE_TASK ", OracleType.VarChar).Value = SAMPLE_TASK;

In the line above you are telling .net to put the value of SAMPLE_TASK into the binding variable "P_AMPLE_TASK" while executing the sql.

Your sql, on the other hand, refers to a P_SAMPLE_TASK (you're missing the S).

VALUES (
    P_SAMPLE_TASK

and

SET SAMPLE_TASK = P_SAMPLE_TASK

That's one error. There may be others :)

Upvotes: 1

Related Questions