CODEWITHSUNDEEP
c#oracle-databaselinqentity-framework-6
Harish Ninge Gowda
Harish Ninge Gowda

Reputation: 441

SqlQuery returning ora-00911 invalid character although the query generated runs well in Oracle database

I am trying to get the next sequence number from oracle database 11g. I have used different methods to get the data from database it is still saying invalid character. the same query generated runs well on the oracle database. Primary key (sequenceId, sequenceNumber). On each sequenceId there will be a separate counter. I am using generic type TEntity which will be passed by controlerl.

public virtual async Task<BaseResult<int>> GetNextSequenceAsync(string sequenceId)
        {
            var sequenceIdField = typeof(SequenceIdDatabaseFields).GetField(typeof(TEntity).Name).GetValue(null);
            var table = typeof(SequenceDatabaseTable).GetField(typeof(TEntity).Name).GetValue(null);
            var column = typeof(SequenceDatabaseColumn).GetField(typeof(TEntity).Name).GetValue(null);
            //var query = new OracleCommand("SELECT NVL(MAX(" + column  + ") + 1, 1) AS "+ column+" FROM "+ table+" WHERE "+ sequenceIdField+" = '"+ sequenceId + "';");
            var query = "SELECT NVL(MAX(" + column + ") + 1, 1) AS " + column + " FROM " + table + " WHERE " + sequenceIdField + " = '" + sequenceId + "';";
            //var nextValue = int.Parse(query.ExecuteScalar().ToString());
            var nextValue = await _context.Database.SqlQuery<int>(query).FirstAsync().ConfigureAwait(false);
            return GetBaseResult(nextValue, ResultTypes.Ok);
        }

The query evaluates to 

SELECT NVL(MAX(CMNT_SEQ_NBR) + 1, 1) AS CMNT_SEQ_NBR FROM CASE_CMNT WHERE CASE_NBR = '00100283000807215100';

Upvotes: 0

Views: 224

Answers (1)

Harish Ninge Gowda
Harish Ninge Gowda

Reputation: 441

@Marmite Bomber was bang on target. Never even dreamt that this could be the problem. I executed the query after removing the ";". It worked perfectly. 

var query = "SELECT NVL(MAX(" + column + ") + 1, 1) AS " + column + " FROM " + table + " WHERE " + sequenceIdField + " = '" + sequenceId + "'";

Upvotes: 1

Related Questions