Forum Stats

  • 3,875,275 Users
  • 2,266,903 Discussions
  • 7,912,133 Comments

Discussions

Unexplainable ORA-00904

1053685
1053685 Member Posts: 19
edited Jul 21, 2015 8:04AM in PHP

Hi folks,

I am seriously becoming exasperated with this problem (which may be a bug in the OCI module for PHP):

I'm trying to execute the following query (which is actually part of a larger query, but I tried to create a minimal example) via the oci_parse/oci_execute functions:

SELECT

t_1.ID,

null EXTRACTION_DATE

FROM xyz.ARTICLES t_1

LEFT JOIN xyz.OTHER_DATA t_2 ON t_1.ID = t_2.ID

ORDER BY ID

This gives me the following error at offset 161 (as returned by the oci_error function):

"ORA-00904: : invalid identifier"

First of all, 161 is not a valid offset. And it always says 161, regardless of how I structure the query. Secondly, it works if I select either 123 (or any other number) instead of null as EXTRACTION_DATE. It also works with null if I leave out the LEFT JOIN. This makes absolutely NO SENSE. Also the actual identifier is missing in the error message. Note the two consecutive colons. Please help me! I have no idea what's going on here!!

I'm using oci8-2.0.8 and PHP 5.6.11 with an Oracle 11gR2 Express Edition.

By the way, if I paste the query into SQL Developer, it works (all variations).

Answers

  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,656 Employee
    edited Jul 13, 2015 9:09PM

    Can you post PHP code that reproduces the problem, and the CREATE TABLE statement?

    Did you check using PHP linked with Oracle 12c client libraries?

    Did you check with SQL*Plus (which is closer to PHP OCI8 than SQL*Developer is)?

  • 1053685
    1053685 Member Posts: 19
    edited Jul 14, 2015 3:44AM

    I just remembered that when connecting via PHP, I'm not connected as the owner of the xyz schema. Just tested it with SQL Developer. If not connected as xyz, the same error occurs as in PHP. Must be a weird permission issue. Although I still don't understand why it makes a difference whether I select null or 123.

    (And yes, I tried 12c libraries and 11g libraries.)

  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,656 Employee
    edited Jul 21, 2015 8:04AM

    I'm glad you remembered that: it would have been hard to diagnose via forum posts.

This discussion has been closed.