C# OracleParameter

CountingCrows

Hello all,

I have some code that attempts to gather table statistics for a given Oracle schema and table using the GATHER_TABLE_STATS procedure. Anyone know how to set the estimate_percent parameter?

command.Parameters.Add(new OracleParameter("estimate_percent", "10"));

Also tried

Decimal est_per = 10;
var param = new OracleParameter("estimate_percent", OracleDbType.Decimal)
{
Value = est_per,
Direction = ParameterDirection.Input,
};
command.Parameters.Add(param);

Both methods result in error - ORA-20001: 10 is an invalid identifier.

The Oracle datatype for estimate_percent is number - https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_stats.htm#i1036461

awec

A quick google suggests ORA-20001 is a application specific error code, which means your application's business logic is throwing this error.

You will probably have to get help from someone internally, because we won't know enough to give answers here. estimate_percent, GATHER_TABLE_STATS - we have no idea what these are.

CountingCrows

awec wrote: »

A quick google suggests ORA-20001 is a application specific error code, which means your application's business logic is throwing this error.

You will probably have to get help from someone internally, because we won't know enough to give answers here. estimate_percent, GATHER_TABLE_STATS - we have no idea what these are.

GATHER_TABLE_STATS is a built in Oracle function - https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_stats.htm#i1036461 The error code ORA-20001 = Invalid input value.

I can execute the procedure correctly by leaving out estimate_percent parameter so pretty sure that's where the issue is.

awec

Google, you failed me! :mad:

Sorry, I have no idea in that case.

cython

Have you tried something like this?

command.Parameters.Add(new OracleParameter("estimate_percent", OracleType.Int32)).Value = 10;

You might need to tinker with the exact OracleType member to specify (https://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracletype%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396), and in the interest of transparency (I'm not a C# dev ) it's based on the only answer here

CountingCrows

Accidently stumbled across the solution. Oracle was referencing the parameters in sequential order, not by name. 'estimate_percent' is the 4th parameter, so I simply added the 3rd parameter 'partname' and it worked.