OracleDataReader GetValue throws InvalidOperationException

Question

There is at least one other thread with this title or one very close to it but it was not quite like my specific implementation and I couldn't find my answer in it. I have this code

<code>
List<string> lsRes = new List<string>();
OracleConnection conn = getOracleConnection();
try
{
    string sqlQuery = "select CTS.SEQ_BATCH_ID.nextval from dual connect by 
level <= " + seqCnt;
    conn.Open();
    OracleCommand cmd = new OracleCommand(sqlQuery, conn);
    cmd.CommandType = CommandType.Text;
    OracleDataReader dr;
    dr = cmd.ExecuteReader();
    while (dr.Read())
        lsRes.Add(dr.GetValue(0).ToString());
}
catch(exception e)
{ 
</code>

I'm getting my InvalidOperationException on dr.GetValue(0). Any idea why? Sequence should be returning a number or numbers, I'm converting it/them to a string/strings, Should I be using GetOracleValue? There are no precompiler or compilation errors just in runtime.

user1811001 · Answer 1 · 2019-03-13T19:44:22.540

0

I'm not sure which language that code is, but I think the issue could be the CONNECT BY clause. I doubt you can use that to do what you want out of that sequence. A sequence will return one value per execution. If you only have one value, there is nothing to CONNECT.

Edit: Whoops wasn't aware of the CONNECT BY trick for a sequence. So the SQL should run fine as long as you have permissions. Not sure what language that code you are using is, but I don't think this should be tagged as Oracle.

edited Mar 13 '19 at 19:44

answered Mar 13 '19 at 19:17

user1811001

23
3

https://stackoverflow.com/questions/8292199/query-several-nextval-from-sequence-in-one-statement – pchancey Mar 13 '19 at 19:31
@pchancey Huh. I wasn't aware of that. Neat. In that case, I would suspect it's not an issue with Oracle, but the code that calls the Oracle. – user1811001 Mar 13 '19 at 19:41
Should be tagged as OracleDataReader but I lack the permissions to add that tag, language is c# – pchancey Mar 13 '19 at 19:49

score 0 · Answer 2 · answered Mar 13 '19 at 21:21

I didn't solve my problem with OracleDataReader but I did manage a work around.

List<string> lsRes = new List<string>();
OracleConnection conn = getOracleConnection();
try
{
    DataTable dtValue = new DataTable("ID");
    string sqlQuery = "select  CTS.SEQ_BATCH_ID.nextval from dual connect by level <= 
    " + seqCnt;
    conn.Open();
    OracleCommand cmd = new OracleCommand(sqlQuery, conn);
    cmd.CommandType = CommandType.Text;
    OracleDataAdapter orada = new OracleDataAdapter(cmd);
    orada.Fill(dtValue);
    orada.Dispose();
    cmd.Dispose();

    if (dtValue != null && dtValue.Rows != null && dtValue.Rows.Count > 0 )
        foreach (var item in dtValue.Rows)
            lsRes.Add(((System.Data.DataRow)item).ItemArray[0].ToString());
}
catch
{

OracleDataReader GetValue throws InvalidOperationException

2 Answers2