5

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 :).

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
ale
  • 11,636
  • 27
  • 92
  • 149
  • Is the table in the schema that you are accessing (you may need to fully qualify it) or does it have a synonym? – Ollie Aug 26 '11 at 15:06
  • Do you use EBS (Entreprise Business Suite ) ? If ye, you may have to set your session to be able to see some tables/views/datas... – Luc M Aug 26 '11 at 15:11
  • 1
    VOTE TO CLOSE PLEASE :)... MY ORACLE HAD SHORTERNED MY TABLE NAMES WITHOUT ME KNOWING! mantis_bug_table was m_bug_t :S – ale Aug 26 '11 at 15:35
  • 2
    I don't think Oracle would have arbitrarily shortened your table name... More likely your DBA gave you a synonym that was shorter or someone else changed your table name. – Ollie Aug 26 '11 at 15:43
  • 1
    @alemaster IMHO just because one of the assumptions in your question was incorrect doesn't necessarily mean that it should be closed. Someone may have the same assumption and I believe that my answer and Justin's covers that. Furthermore someone may choose this question in the future to give the canonical answer for ORA-00942. See [The Wikipedia of Long Tail Programming Questions](http://blog.stackoverflow.com/2011/01/the-wikipedia-of-long-tail-programming-questions/) – Conrad Frix Aug 26 '11 at 19:06
  • Hi sorry for the caps in the above post.. I was exhausted when I wrote it. I meant that my DBA had shorterned them without me knowing.. you guessed correctly :p. Thanks guys. – ale Aug 27 '11 at 11:04

3 Answers3

10

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'
Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
3
  • 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.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
-1

You should point scheme in connection string like:

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

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

Andrej
  • 7,474
  • 1
  • 19
  • 21