Home / Questions / 376335
Accepted
Richard Víquez Pérez
Asked: 2020-07-26 22:55:11 +0800 CST 2020-07-26 22:55:11 +0800 CST

How to do multiple selects in oracle with c#

  • 772

I am executing this select in oracle from c#:

  select * from ( select  ROWNUM rn, a.* from ( select id,destinatario,titulo,prioridad,consulta,estado,fechaultima,compania,vista 
    from PAZKO.ALARMA_MOVIL where destinatario='xxxxx'  ORDER BY sysdate ) a where  ROWNUM <= 4  ) where rn>= 0;

This works for me without problems. However, when adding one or several more selects, it gives me an error. This is the select that gives an error:

select * from ( select  ROWNUM rn, a.* from ( select id,destinatario,titulo,prioridad,consulta,estado,fechaultima,compania,vista 
from PAZKO.ALARMA_MOVIL where destinatario='xxxxx'  ORDER BY sysdate ) a where  ROWNUM <= 4  ) where rn>= 0;
select * from ( select  ROWNUM rn, a.* from ( select id,destinatario,titulo,prioridad,consulta,estado,fechaultima,compania,vista 
from NEWPAZKO.ALARMA_MOVIL where destinatario='xxxx'  ORDER BY sysdate ) a where  ROWNUM <= 4  ) where rn>= 0;

As you can see, it is the same select but from a different user. If I execute those 2 select together in sqldeveloper it works fine for me as seen in the image below:

img

But if I run it from code in the c# app it gives an error:

Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00911: invalid character at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayDb atMoreInternalBindDML, Boolean& haster) ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64&internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF) at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior) at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken) --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter1.GetResult() at DBConnections.oracle.Select.d__2.MoveNext()

The code is the following:

 using (var comando = new OracleCommand(consulta, conexion))
                    {
                        var reader = await comando.ExecuteReaderAsync();


                        foreach (var item in reader.Cast<DbDataRecord>())
                        {
                           
                            //....
                        }

                    }

The nuget package I use is the Oracle.ManagedDataAccess

c#

1 Answers

  1. Best Answer

    Sequence queries will work in an editor as it allows you to perform multiple queries. But the connection allows you to execute one query at a time, since each one by itself generates a single Dataset.

    As I understand it, what you are trying to do is get the data from both results and merge them into one. For this you can use a single query using UNION (both must have the same result columns)

    select * from ( select  ROWNUM rn, a.* from ( select id,destinatario,titulo,prioridad,consulta,estado,fechaultima,compania,vista 
from PAZKO.ALARMA_MOVIL where destinatario='xxxxx'  ORDER BY sysdate ) a where  ROWNUM <= 4  ) where rn>= 0
UNION
select * from ( select  ROWNUM rn, a.* from ( select id,destinatario,titulo,prioridad,consulta,estado,fechaultima,compania,vista 
from NEWPAZKO.ALARMA_MOVIL where destinatario='xxxx'  ORDER BY sysdate ) a where  ROWNUM <= 4  ) where rn>= 0;

    Otherwise, you must perform each query separately in separate calls.

    • 2