Carlitrosss Carlitrosss - 6 days ago 8
Vb.net Question

ODP.net + VB = Table not found

I am new to Oracle and I have just installed Oracle 10g XE,and its ODAC package for .NET.
I am making a simple connect-and-get-table app in VB,however,it always throws a "Table not found" error.

I created the "Test" table in Oracle Home (the web admin thing) and here is the code I'm using in VB :

Dim oraCmd As New OracleCommand("Select * From Test")
oraCmd.Connection = oraCon

oraCon.Open()

oraCmd.ExecuteReader()
'Reader code supressed


EDIT When I try the same query in Database Home, it works.

Answer

If you're logging in using a different user in oracle, and want to read from a table under a different user, you should fully qualify your table name. If your table is created under a user named "TEST_USER", then query your table as "Select * From TEST_USER.test"

This works for me when logged in under the same username of which the table was created:

    private const string connString = "DATA SOURCE=//server:port/service_name;PASSWORD=pswd;USER ID=user_name;";

    static void Main(string[] args)
    {
        OracleConnection conn = new OracleConnection(connString);
        conn.Open();

        OracleCommand cmd = new OracleCommand("select * from test", conn);

        // This would also work if the username I used to login was "TEST_USER".
        // OracleCommand cmd = new OracleCommand("select * from TEST_USER.test", conn);

        cmd.ExecuteReader();

        conn.Close();
    }

The other possible issue is that you named your table with lowercase letters as "Test", instead of it being named "TEST" in the database. In that case, you'll need to reference the table with quotes around it as such:

    static void Main(string[] args)
    {
        OracleConnection conn = new OracleConnection(connString);
        conn.Open();

        OracleCommand cmd = new OracleCommand("select * from \"Test\"", conn);
        cmd.ExecuteReader();

        conn.Close();
    }

Or you could also, rename your table in oracle if case is the issue and you want to resolve it this way:

ALTER TABLE "Test" RENAME TO TEST;