1

I've a view named EMP_DETAILS which looks like

CREATE OR REPLACE FORCE VIEW "ABC"."EMP_DETAILS" ("NAME",  "COUNTRY") AS 

  SELECT 
b.NAME,c.COUNTRY
FROM 
ABC.Emp a ,ABC.Emp_Bom b ,ABC.Emp_info c 
    where a.E_ID=b.ID and a.R_ID=c.NR order by 1 asc;

I simply wish to fetch a row from this view and the query for the same looks like

Select * from EMP_DETAILS where NAME = 'xxx' and COUNTRY = 'xxx';  //Query works fine in Oracle SQL Developer

C# code to run this query looks like

        DataSet ds = null;
        OracleDataAdapter adapter = null;
        StringBuilder builder = new StringBuilder();
        builder.Append("SELECT * from EMP_DETAILS where NAME = :xx and COUNTRY = :xxx");

        command = new OracleCommand(builder.ToString());
        command.Parameters.Add("xx", name);
        command.Parameters.Add("xxx", country);
        string tablename = "EMP_DETAILS";

        using (OracleConnection oc = new OracleConnection(CONNECTIONSTRING))
        {
            try
            {
                adapter = new OracleDataAdapter();
                command.CommandType = CommandType.Text;
                command.Connection = oc;
                adapter.SelectCommand = command;
                ds = new DataSet();
                adapter.Fill(ds, tablename); //Code breaks here msg says **No table or view found**
            }
            catch(Exception ex)
            {
                if (ds != null)
                {
                    ds.Dispose();
                    ds = null;
                }
            }
            finally
            {
                if (oc != null)
                {
                    oc.Close();
                    oc.Dispose();
                }
            }
        }

Found that this error is usually caused if you tried to execute a SQL statement that references a table or view that either does not exist, that you do not have access to, or that belongs to another schema and you didn't reference the table by the schema name.

I checked for all these conditions but still couldn't solve the issue. Let me know where am I going wrong.

This question doesn't help me so don't mark it as a duplicate.

Update: I tried to combine the view query with my Select query and it works ! Query looks like

select* from
(SELECT b.NAME, c.COUNTRY FROM 
ABC.Emp a ,ABC.Emp_Bom b ,ABC.Emp_info c 
where a.E_ID=b.ID and a.R_ID=c.NR order by 1 asc)
where NAME LIKE :xx and AREA LIKE :xxx

Let me know why it doesn't work when I create a view and then try to retrieve data from the view.

m_beta
  • 132
  • 15
  • Tablename `xxx`? – jarlh Feb 11 '20 at 08:48
  • @jarlh Tablename is same as the view name i.e `string tablename = "EMP_DETAILS";` I'll update the same in question as well. – m_beta Feb 11 '20 at 10:12
  • Don't you have to specify schema `ABC ` as well? (Or is it default?) – jarlh Feb 11 '20 at 10:15
  • @jarlh Tried specifying schema but of no use. – m_beta Feb 11 '20 at 10:21
  • 1
    Does this answer your question? [SQL Error: ORA-00942 table or view does not exist](https://stackoverflow.com/questions/16129912/sql-error-ora-00942-table-or-view-does-not-exist) – oleksa Feb 11 '20 at 10:42
  • your code should work perfectly fine, verify the data base once, where did you executed view and from which database you are trying to access in connection string. – Venkat Singri Feb 11 '20 at 11:39
  • @oleksa As I mentioned that I went thoroughly for the following points 1. Execute a SQL statement that references a table or view that either does not exist 2.That you do not have access to. 3. That belongs to another schema and you didn't reference the table by the schema name. The mentioned question says to look for these points only which didn't help me. – m_beta Feb 11 '20 at 12:36
  • well, you can check the same query using the same credentials from the Oracle sql client. Your code looks ok so only you can find the solution – oleksa Feb 12 '20 at 08:05

0 Answers0