OraException from Command.ExecuteReader with SELECT DISTINCT — oracle-tech

    Forum Stats

  • 3,715,830 Users
  • 2,242,890 Discussions
  • 7,845,632 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

OraException from Command.ExecuteReader with SELECT DISTINCT

306801
306801 Member Posts: 3
Hello

I am trying to fill an OraDataReader object from a OraCommand object.
It appears that whenever the statement includes a SELECT DISTINCT, the command fails (even though the SQL statement works fine when executed e.g. in TOAD)

Dim Conn As OraConnection = New Oracle.DataAccess.Client.OraConnection("[Valid connection string]")
Conn.Open()
Dim Command As OraCommand = New OraCommand()
Command.CommandType = CommandType.Text
Command.CommandText = "select DISTINCT nvl(SUBFAMILY_COMPETENCY_ID, 0) SUBFAMILY_ID, nvl(NAME, ' ') NAME, FAMILY_COMPETENCY_ID FAMILY_ID " & _
" from TAXONOMY_RELATIONAL, COMPETENCY " & _
" where TAXONOMY_RELATIONAL.CL_ID = :1 " & _
" AND COMPETENCY.cl_id (+) = TAXONOMY_RELATIONAL.cl_id and COMPETENCY.language_id (+) = :2 " & _
" and COMPETENCY.COMPETENCY_ID (+) = TAXONOMY_RELATIONAL.SUBFAMILY_COMPETENCY_ID " & _
" order by FAMILY_ID, SUBFAMILY_ID"
Command.Parameters.Add("@CL_ID", context.Session!CLID)
Command.Parameters.Add("@LANGUAGE_ID", context.Session!LANGUAGEID)
Dim DataReader As OraDataReader = Command.ExecuteReader(CommandBehavior.CloseConnection)

An Oracle.DataAccess.Client.OraException is raised when executing the last statement, but no further details are given in the error message. The stack trace begins as follows
[OraException]()
Oracle.DataAccess.Client.OraException.HandleErrorHelper(Int32 errCode, OraConnection conn, IntPtr opsErrCtx, IntPtr opsSqlCtx, Object src, String procedure) +639
Oracle.DataAccess.Client.OraCommand.ExecuteReader(+737)
Oracle.DataAccess.Client.OraCommand.ExecuteReader(CommandBehavior behavior) +8
...


The exception disappears as soon as I remove the DISTINT keyword from my SQL statement.

Is this a bug or am I doing something wrong?

Thanks for help,

Bernt Fischer

Comments

This discussion has been closed.