Invalid index 0 for this OracleParameterCollection with Count=0

Question

public void BeginTransaction()
{
    try
    {
        this._keepalive = true;
        if (_oracleConnection.State != ConnectionState.Open)
            _oracleConnection.Open();
            //_oracleConnection.Autocommit = false;
        this._transaction = this._oracleConnection.BeginTransaction(IsolationLevel.ReadCommitted);
    }
    catch (Exception ex)
    {
        _hasError = true;
        _ErrorMessage = ex.Message + "::" + ex.StackTrace;
    }
}

public void CommitTransaction()
{
    try
    {
        this._transaction.Commit();
        this._keepalive = false;
    }
    catch (Exception ex)
    {
        _hasError = true;
        _ErrorMessage = ex.Message + "::" + ex.StackTrace;
    }
}

public void RollbackTransaction()
{
    try
    {
        this._transaction.Rollback();
        this._keepalive = false;
    }
    catch (Exception ex)
    {
        _hasError = true;
        _ErrorMessage = ex.Message + "::" + ex.StackTrace;
    }
}

public string ExecuteSPNonQuerySingleReturnValue(string storedProcName, object[] parameterValues, string outParameterName, bool useTransaction = false)
{
    _hasError = false; _ErrorMessage = "";
    string result = "";
    try
    {
        if (_oracleConnection.State == ConnectionState.Closed)
            _oracleConnection.Open();
        if (_oracleConnection.State == ConnectionState.Open)
        {
            OracleCommand objOraCommand = new OracleCommand();
            objOraCommand.Connection = _oracleConnection;
            objOraCommand.CommandText = storedProcName;
            objOraCommand.CommandType = CommandType.StoredProcedure;
            if (useTransaction == true)
                objOraCommand.Transaction = this._transaction;
            OracleCommandBuilder.DeriveParameters(objOraCommand);
            for (int i = 0; i < parameterValues.Length; i++)
            {
                //objOraCommand.Parameters.Add(new OracleParameter(parameterNames[i], OracleType.VarChar)).Value = (parameterValues[i] == null) ? DBNull.Value : parameterValues[i];
                // It threw exception over here. Below this line.
                objOraCommand.Parameters[i].Value = (parameterValues[i] == null) ? DBNull.Value : parameterValues[i];
                //objOraCommand.Parameters.AddWithValue(parameterNames[i], (parameterValues[i] == null) ? DBNull.Value : parameterValues[i]);
            }
            objOraCommand.ExecuteNonQuery();
            result = objOraCommand.Parameters[outParameterName].Value.ToString();
        }
    }
    catch (Exception ex)
    {
        _hasError = true;
        _ErrorMessage = ex.Message;
    }
    finally
    {
        if (_oracleConnection.State == ConnectionState.Open && _keepalive == false)
        _oracleConnection.Close();
    }
    return result;
}

I am getting exception at this line.

objOraCommand.Parameters[i].Value = (parameterValues[i] == null) ? DBNull.Value : parameterValues[i];

Does anyone know what is the problem? It was working fine without transaction. This method started to give error right after adding transaction.

I am using built .Net oracle client library.

using System.Data.OracleClient;

You haven't added any parameters yet you try to accesss them. Index 0 is the first element. An empty parameter collection though has no parameters — Panagiotis Kanavos, Oct 28 '16 at 13:39
This is the procedure i am calling PROCEDURE CHCCC_STSS (PSSS IN VARCHAR2, PCCC IN VARCHAR2,varReturnValue OUT VARCHAR2); and i am passing proper values to those parameter. — Yogi Bear, Nov 02 '16 at 05:11
Please check any ADO.NET tutorial on how to use parameters, specially out parameters. — Panagiotis Kanavos, Nov 02 '16 at 08:17
@PanagiotisKanavos Thanks for your demoralizing comment! I have tried out 100s of website and tutorials before posting it here. — Yogi Bear, Nov 07 '16 at 10:15
Did you [check this similar SO question](http://stackoverflow.com/questions/290652/get-output-parameter-value-in-ado-net). ADO.NET is the same whether you use the Oracle or SQL Server provider. This means that any tutorial on using parameters with SQL Server is equally valid for other providers. — Panagiotis Kanavos, Nov 07 '16 at 11:27
Also, the documentation explains how to call commands with parameters in [Configuring Parameters and Parameter Data Types](https://msdn.microsoft.com/en-us/library/yy6y35y8(v=vs.110).aspx). — Panagiotis Kanavos, Nov 07 '16 at 11:29
@PanagiotisKanavos: This code is working fine without transaction. It gives me an error when I add transaction. Try it once it will increase your knowledge! — Yogi Bear, Nov 08 '16 at 13:20

Bucketcode · Answer 1 · 2016-10-28T13:42:21.480

0

using objOraCommand.Parameters.Add() instead of objOraCommand.Parameters[i].Value = xxxx. And parameterValues should be OracleParametertype. more check this page

edited Oct 28 '16 at 13:42

answered Oct 28 '16 at 13:37

Bucketcode

441
2
13

PROCEDURE CHCCC_STSS (PSSS IN VARCHAR2, PCCC IN VARCHAR2,varReturnValue OUT VARCHAR2); ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'CHCCC_STSS' ORA-06550: line 1, column 7: PL/SQL: Statement ignored This is the error i got. – Yogi Bear Nov 02 '16 at 05:01

score 0 · Answer 2 · answered Oct 28 '16 at 13:53

Basically there is no parameter in Parameters and you are trying to access its index, that is why its giving error. you should add parameter like objOraCommand.Parameters.Add()and you you want to try to access the value at objOraCommand.Parameters[i].Value and first add parameter to that location like as you do in list and array. and this error is not at all related to Transaction, my only advice would be to use Transaction properly not like this complicated code.

Invalid index 0 for this OracleParameterCollection with Count=0

2 Answers2