When I connect to an Oracle 11 database using Oracle.DataAccess (v 4.0.30319 ) a simple query (Select * from DifferentSchema.myView) returns about 300 rows with the C# code below:
OracleConnection conn = new OracleConnection(); conn.ConnectionString = "User Id=myUserid;Password=myPass;Data Source=myConnectionStr;"; conn.Open(); String OraSql = "Select TO_CHAR(Employee_Number) from APPS.myView"; // String OraSql = "SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') FROM DUAL"; OracleCommand cmd = new OracleCommand(OraSql, conn); OracleDataReader dr = cmd.ExecuteReader();
When I change it to be with OracleManagedDataAccess (v4.0.30319), then the query
appears to run, because there is no Ora-nnnn exception code, BUT the data reader results view is empty. 'Enumeration yielded no results"
I know that I have connected , because when I change the OraSql to query SYSDATE from Dual , then it returns a result in cmd.ExecuteReader();
When I change the SQL to be invalid code, then it objects to it in cmd.ExecuteReader();.
Similarly, if I change the password to 'myPASSXXXFAIL' then the process throws an exception at
conn.Open();
What else can I try to make this work?
( FYI I am looking at Oracle Managed Data Access - because a UAT deployment to a 2012 server is having issues with the installation of an Oracle client )
dba grant privileges Oracle requires specific select privileges be granted on VIEWs since MyUSerID is not the VIEW owner. APPS schema is the view object owner.
grant select on apps.myview to myUserid
verify Employee_number is NUMBER data type and not a VARCHAR2 or other text data type.
Member
11 Points
106 Posts
Problem executing SQL with Oracle Managed Data Access
Oct 24, 2016 02:25 PM|Richard Scannell|LINK
When I connect to an Oracle 11 database using Oracle.DataAccess (v 4.0.30319 ) a simple query (Select * from DifferentSchema.myView) returns about 300 rows with the C# code below:
OracleConnection conn = new OracleConnection();
conn.ConnectionString = "User Id=myUserid;Password=myPass;Data Source=myConnectionStr;";
conn.Open();
String OraSql = "Select TO_CHAR(Employee_Number) from APPS.myView";
// String OraSql = "SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') FROM DUAL";
OracleCommand cmd = new OracleCommand(OraSql, conn);
OracleDataReader dr = cmd.ExecuteReader();
When I change it to be with OracleManagedDataAccess (v4.0.30319), then the query appears to run, because there is no Ora-nnnn exception code, BUT the data reader results view is empty. 'Enumeration yielded no results"
What else can I try to make this work?
( FYI I am looking at Oracle Managed Data Access - because a UAT deployment to a 2012 server is having issues with the installation of an Oracle client )
Contributor
3422 Points
1333 Posts
Re: Problem executing SQL with Oracle Managed Data Access
Oct 25, 2016 11:49 PM|Lannie|LINK
dba grant privileges Oracle requires specific select privileges be granted on VIEWs since MyUSerID is not the VIEW owner. APPS schema is the view object owner.
grant select on apps.myview to myUserid
verify Employee_number is NUMBER data type and not a VARCHAR2 or other text data type.