Oracle SELECT statement does not work - ORA-00942

Question

Hope 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); 

Following results in

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

but the connection does not lead to errors, and the database table exists, and it is not empty.

Any ideas ??? Thanks:).

Answer 1

This usually has one of four possible problems.

• You are not connecting to the database that you think (maybe not)
• You do not have permission to the table (see Justin Cave answer regarding Grant)
• You may need to add an owner to the table name, for example. select * from DB_USER.mantis_bug_table (See Justin Cave's answer regarding SYNONYM if you don't want to name tablename)
• The table really does not exist, possibly spelling error

You can diagnose this by doing the following

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

Answer 2

• Which Oracle user owns the table?
• Is the Oracle user associated with your PHP script as having access to this table?
• Is there a public or private synonym for the table MANTIS_BUG_TABLE ?

If the table belongs to another user, you can try to fully determine the name of the table

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

If the user using your PHP script does not have access to the table, you will need a database administrator or table owner

 GRANT SELECT ON owner_of_table.mantis_bug_table TO whatmyusernameis; 

If you have access to the table and the full qualification of the table name works, but you do not want to determine the table name each 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 who have access to a table to refer to it without using a full name. A private synonym allows only the owner of the synonym (i.e., Whatmyusernameis) to refer to the table without the full name of the table.

Answer 3

You must specify the schema in the connection string, for example:

 oci_connect('whatmyusrnameis', 'whatmypwdis', 'host/**YOUR_DB**'); 

See http://www.php.net/manual/en/function.oci-connect.php in the connection_string section