Oracle SELECT statement not working - ORA-00942

Question

Hopefully a simple question.

    error_reporting(E_ALL);
    ini_set('display_errors', '1');
    $c = oci_connect('whatmyusrnameis', 'whatmypwdis', 'host');
    if ($c) {
            echo 'connection';

    }
    $s = oci_parse($c, 'select *  from mantis_bug_table');
    oci_execute($s);

The following results in

Warning oci_execute(): ORA-00942: table or view does not exist

but the connection doesn't result in any errors and the DB table does exist and it is not empty.

Any ideas??? Thank you :).

Answer 1

Typically this has one of four possible problems

1. You're not connecting to the database you think you are (probably not the case)
2. You don't have permission to the table (See Justin Cave's answer regarding Grant)
3. You may need to add the owner to the table name e.g. select * from DB_USER.mantis_bug_table (See Justin Cave's answer regarding SYNONYMs if you don't want qualify the tablename)
4. The table really doesn't exist perhaps a spelling error

You can diagnose this by running the following

SELECT * FROM ALL_TABLES WHERE UPPER(table_name) = 'MANTIS_BUG_TABLE'

Answer 2

• What Oracle user owns the table?
• Does the Oracle user that your PHP script connects as have access to this table?
• Is there a public or private synonym for the MANTIS_BUG_TABLE table?

If the table is owned by some other user, you could try fully qualifying the table name

$s = oci_parse($c, 'select *  from owner_of_table.mantis_bug_table');

If the user your PHP script is using doesn't have access to the table, you'll need a DBA or the owner of the table to

GRANT SELECT ON owner_of_table.mantis_bug_table
   TO whatmyusernameis;

If you have access to the table and fully qualifying the table name works but you don't want to have to fully qualify the table name every time, you can create a synonym

CREATE [PUBLIC] SYNONYM mantis_bug_table
   FOR owner_of_table.mantis_bug_table

A public synonym allows all users with access to the table to reference it without using a fully qualified name. A private synonym allows just the owner of the synonym (i.e. whatmyusernameis) to reference the table without a fully qualified table name.