I am having trouble getting certain SQL statements containing Natural Joins to work with OracleDataAdapter.Fill(). I am using Oracle.ManagedDataAccess 4.121.1.0.
To illustrate this, review the following valid SQL statements. All work properly in a SQL editor. However, the last one does not work via ODP.NET when I use the OracleDataAdaper.Fill method. I have not had any trouble using a OracleDataReader with any of these statements.
Works with OracleDataAdapter.Fill
SELECT *
FROM Operation_Settings, Settings
WHERE Settings.Setting_ID = Operation_Settings.Setting_ID
Works with OracleDataAdapter.Fill
SELECT * FROM (Operation_Settings NATURAL JOIN Settings)
Does Not work with OracleDataAdapter.Fill
SELECT * FROM Operation_Settings NATURAL JOIN Settings
Exception Message:
Oracle.ManagedDataAccess.Client.OracleException: ORA-00942: table or view does not exist
Data Access Layer function performing DB lookup and fetch
Public Function GetQueryResults(parameters As Dictionary(Of String, Object), query As String, connectionString As String) As DataTable
Using table As New DataTable
Using conn As New OracleConnection(connectionString)
Using cmd = CreateOracleCommand(query, parameters, conn)
Using adapter As New OracleDataAdapter(cmd)
adapter.Fill(table)
Return table
End Using ' adapter
End Using ' cmd
End Using ' conn
End Using ' table
End Function
I am open to any ideas and will upload any additional info you require.