ODP.NET

[Logging-DEBUG]-8/31/2006 11:16:21 AM
[GetDataReader]
Select Distinct(prj.Name) as PrjName, prj.oid as PrjOid From
SP3DTRAIN_SITE.PRJMGTProjectRoot prj,
SP3DTRAIN_SITE.CORERELATIONDEST ic, SP3DTRAIN_SITE.PRJMGTDatabase pd
Where prj.oid=ic.oidTarget And ic.oid=pd.oid And pd.oid= :plant_oid
[Exception]
Invalid parameter binding
Parameter name: plant_oid

In code I'm doing this.........!!!

This is the line that calls the Request object to add the parameter:

oRequest.AddParameter(param, DAO.DBDataType.DBGuid, plantOID,
ParameterDirection.Input);

Which adds it to an ArrayList in Request class

public void AddParameter( string paramName, DBDataType paramType,
object paramValue, ParameterDirection paramDirection)
{
Parameter buildParameter=null; //a generic parameter.
buildParameter = new Parameter(paramName, paramType,
paramValue, paramDirection);
m_colParameters.Add(buildParameter);
}

then in the main code I'm calling GetDataReader(sql, oConnection,
oRequest);
where the oConnection has the correct data-factory (in this case
Oracle) set.

So in OracleConcreteFactory I do this:

public override DAODataReader ExecuteDataReader(DAORequest request)
{
OracleCommand cmdSQL = new OracleCommand();
OracleConnection conSQL = new OracleConnection();
OracleDataReader drSQL = null;
DAODataReader rdr = new DAOConcDataReader(); //
try
{
if (request.ConnSetting == null) throw new
ArgumentException("Connecting string needs to be set");
conSQL.ConnectionString = request.ConnSetting;
conSQL.Open();
cmdSQL.Connection = conSQL;
cmdSQL.CommandText = request.Command;
cmdSQL.CommandType = request.CommandType;
AddParameters(cmdSQL, request.Parameters);
rdr.ReturnedDataReader =
cmdSQL.ExecuteReader(CommandBehavior.CloseConnection);
return rdr; //this is where it fails
}
catch { throw; }
finally { }

}

private void AddParameters(IDbCommand cmd, ArrayList paramList)
{
OracleParameter sqlParm = null;
foreach (DAORequest.Parameter prm in paramList)
{
sqlParm = new OracleParameter();
sqlParm.ParameterName = prm.ParamName;
sqlParm.OracleDbType = GetDBType(prm.ParamType);
sqlParm.Direction = prm.ParamDirection;
sqlParm.Value = prm.ParamValue;
cmd.Parameters.Add(sqlParm);
}

}

private OracleDbType GetDBType(DBDataType paramType)
{
if (paramType == DBDataType.DBString)
return OracleDbType.Varchar2;
else if (paramType == DBDataType.DBChar)
return OracleDbType.Char;
else if (paramType == DBDataType.DBInteger)
return OracleDbType.Int32;
else if (paramType == DBDataType.DBBit)
return OracleDbType.Single;
else if (paramType == DBDataType.DBDateTime)
return OracleDbType.Date;
else if (paramType == DBDataType.DBDecimal)
return OracleDbType.Decimal;
else if (paramType == DBDataType.DBMoney)
return OracleDbType.Double;
else if (paramType == DBDataType.DBImage)
return OracleDbType.Byte;
else if (paramType == DBDataType.DBGuid)
return OracleDbType.Raw;
else
throw new ArgumentException("OracelDBType value not found for " +
paramType.ToString());

}

Any help is really appreciated.

thanks
Sunit