ORA-01036 error in MS Oracle Provider

  • Thread starter Thread starter sibi_revi
  • Start date Start date
S

sibi_revi

Hi,
I am trying to execute a stored procedure through MS Oracle Provider.

I am getting an error stating "ORA-01036: illegal variable
name/number". I have checked for all the parameter names and parameter
types. They look to be fine. I have set the command type to stored
procedure. Please help me on this.


Thanks
REVI


//Details of the issue


OracleParameter [] iParam = new OracleParameter[13];


iParam[0] = new OracleParameter("P_PS_ID",Orac­leType.Number,11);
iParam[0].Direction = ParameterDirection.Input;
iParam[0].Value = projectid;


iParam[1] = new OracleParameter("P_PO_ID",Orac­leType.Number,11);
iParam[1].Direction = ParameterDirection.Input;
iParam[1].Value = P_PO_ID;


iParam[2] = new
OracleParameter("P_PS_PROBLEM"­,OracleType.VarChar,4000);
iParam[2].Direction = ParameterDirection.Input;
iParam[2].Value = P_PS_PROBLEMSTATEMENT;


iParam[3] = new OracleParameter("P_PO_DESC ",OracleType.VarChar,255);
iParam[3].Direction = ParameterDirection.Input;
iParam[3].Value = P_PO_DESC ;


iParam[4] = new
OracleParameter("P_PO_DELIVERA­BLE",OracleType.VarChar,4000 );
iParam[4].Direction = ParameterDirection.Input;
iParam[4].Value = P_PO_DELIVERABLE;


iParam[5] = new
OracleParameter("P_PO_ASSUMPTI­ONS",OracleType.VarChar,4000);
iParam[5].Direction = ParameterDirection.Input;
iParam[5].Value = P_PO_ASSUMPTIONS;


iParam[6] = new OracleParameter("P_PO_SCOPE",O­racleType.VarChar,4000
);
iParam[6].Direction = ParameterDirection.Input;
iParam[6].Value = P_PO_SCOPE;


iParam[7] = new
OracleParameter("P_PO_OUTOFSCO­PE",OracleType.VarChar,4000);
iParam[7].Direction = ParameterDirection.Input;
iParam[7].Value = P_PO_OUTOFSCOPE;


iParam[8] = new
OracleParameter("P_PO_ALTERNAT­IVES",OracleType.VarChar,4000)­;
iParam[8].Direction = ParameterDirection.Input;
iParam[8].Value = P_PO_ALTERNATIVES;


iParam[9] = new OracleParameter("P_IORU_FLAG",­OracleType.VarChar,5);
iParam[9].Direction = ParameterDirection.Input;
iParam[9].Value = P_IORU_FLAG;


//add the additional parameter P_VERNO above the OUT_ERR"
iParam[10] = new OracleParameter("P_PSVERNO",Or­acleType.Number,10);
iParam[10].Direction = ParameterDirection.Input;
iParam[10].Value = P_PSVERNO;


iParam[11] = new OracleParameter("P_POVERNO",Or­acleType.Number,10);
iParam[11].Direction = ParameterDirection.Input;
iParam[11].Value = P_POVERNO;


iParam[12] = new OracleParameter("OUT_ERR",Orac­leType.VarChar,2000);
iParam[12].Direction = ParameterDirection.Output;
iParam[12].Value = null;


OracleCommand cmd = new OracleCommand(ConnectionString­);
cmd.Parameters.Add(iParam[0]);
..
..
..
cmd.Parameters.Add(iParam[12])­;
OracleConnection connection = new OracleConnection();
connection.Open();
cmd.Connection = connection;
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = CommandText //StoredProcName
cmd.ExecuteNonQuery();


///Stored Procedure
PROCEDURE INS_PROJECT_STATEMENTS (
P_PS_ID IN Number,
P_PO_ID IN Number,
P_PS_PROBLEM IN VARCHAR2,
P_PO_DESC IN VARCHAR2,
P_PO_DELIVERABLE IN VARCHAR2,
P_PO_ASSUMPTIONS IN VARCHAR2,
P_PO_SCOPE IN VARCHAR2,
P_PO_OUTOFSCOPE IN VARCHAR2,
P_PO_ALTERNATIVES IN VARCHAR2,
P_IORU_FLAG IN VARCHAR2,
P_PSVERNO IN NUMBER,
P_POVERNO IN NUMBER,
OUT_ERR OUT VARCHAR2
)
IS
BEGIN
UPDATE PROJECTSUMMARY
SET PS_PROBLEMSTATEMENT=P_PS_PROBL­EM,
VERNO = VERNO + 1
WHERE PS_ID=P_PS_ID
AND VERNO=P_PSVERNO ;
IF SQL%ROWCOUNT = 0 THEN
OUT_ERR:= Pkg_Errors.ERR_VERNO ;
END IF ;
IF P_IORU_FLAG='I' THEN
INSERT INTO PROJECTOBJECTIVES (
PO_ID,
PO_PS_FK,
PO_DESC,
PO_DELIVERABLE,
PO_ASSUMPTIONS,
PO_SCOPE,
PO_OUTOFSCOPE,
PO_ALTERNATIVES
)
VALUES (
P_PO_ID,
P_PS_ID,
P_PO_DESC,
P_PO_DELIVERABLE,
P_PO_ASSUMPTIONS,
P_PO_SCOPE,
P_PO_OUTOFSCOPE,
P_PO_ALTERNATIVES
);
ELSE
UPDATE PROJECTOBJECTIVES
SET PO_DESC=P_PO_DESC,
PO_DELIVERABLE=P_PO_DELIVERABL­E,
PO_ASSUMPTIONS=P_PO_ASSUMPTION­S,
PO_SCOPE=P_PO_SCOPE,
PO_OUTOFSCOPE=P_PO_OUTOFSCOPE,
PO_ALTERNATIVES=P_PO_ALTERNATI­VES,
VERNO=VERNO + 1
WHERE PO_PS_FK=P_PS_ID
AND VERNO=P_POVERNO ;
IF SQL%ROWCOUNT = 0 THEN
OUT_ERR:= Pkg_Errors.ERR_VERNO ;
END IF ;
END IF;
EXCEPTION
WHEN OTHERS THEN
OUT_ERR:=SQLERRM;
END INS_PROJECT_STATEMENTS;
 
On 15 Aug 2005 11:34:15 -0700, (e-mail address removed) wrote:

¤ Hi,
¤ I am trying to execute a stored procedure through MS Oracle Provider.
¤
¤ I am getting an error stating "ORA-01036: illegal variable
¤ name/number". I have checked for all the parameter names and parameter
¤ types. They look to be fine. I have set the command type to stored
¤ procedure. Please help me on this.
¤
¤
¤ Thanks
¤ REVI
¤
¤
¤ //Details of the issue
¤
¤
¤ OracleParameter [] iParam = new OracleParameter[13];
¤
¤
¤ iParam[0] = new OracleParameter("P_PS_ID",Orac­leType.Number,11);
¤ iParam[0].Direction = ParameterDirection.Input;
¤ iParam[0].Value = projectid;
¤

The only thing I can see which might be tripping you up is the size parameter for OracleType.Number.
I would omit this parameter.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Hi Paul,
Thanks a lot for the reply. The problem was with an additional
space in the parameter name.

Thanks
REVI
 
Back
Top