locked
Problem executing SQL with Oracle Managed Data Access RRS feed

  • Question

  • User369345748 posted

    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 ) 

    Monday, October 24, 2016 2:25 PM

All replies

  • User269602965 posted

    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.

    Tuesday, October 25, 2016 11:49 PM