Re: Getting error ORA-00904 invalid identified

Sandra,

Activate a SQL trace (any level) against a database session prior to running this report, and then examine the "raw" trace file for the presence of a line that looks like:

     PARSE ERROR #n: .... err=904

Search in particular for the phrase "err=<err-number>" (i.e. "err=904") and, in the case of a parsing error like ORA-00904, the SQL statement text should appear right after it. In other cases, where the error is occuring subsequent to successful parsing, you might instead see a line like:

     ERROR #n: err=NNN, ...

where "NNN" is the non-leading-zero value of the Oracle error number (i.e. "904" or "942" instead of "00904" or "00942") and "n" is the cursor number, and in this case you might have to search backwards through the file for the phrase "PARSING IN CURSOR #n" to find the SQL text.

I have an AFTER LOGON database-event trigger called "tracetrg.sql" online on my website (http://www.EvDBT.com/tools.htm), in case it is difficult to initiate a SQL trace directly through whatever reporting tool you are using. Just create the trigger, disable it immediately, then work with the developer to re-enable it just before he/she starts their session, then disable or drop the trigger after the session is started and you confirm that you see the ".trc" file in the USER_DUMP_DEST directory.

Hope this helps...

-Tim

Quoting Sandra Becker <sbecker6925_at_gmail.com>:

> SLES10, Oracle 10.2.0.3 on IBMz9, app server is Dell RHEL4, 9i client (32
> bit server), report server Windows/SQL Server 2000
>
> We are trying to test our application against a production size 10g
> database. The actual production database is still 9i and will NOT be
> upgraded to 10g. Most of the application works very well. We do have some
> reports that are throwing the ORA-00904 invalid identifier error and I
> haven't been able to find anything on MetaLink that is of any value yet. I
> did just find out that the reports are running on a Windows server pulling
> the data into SQL Server and doing the processing there before returning the
> results back to the application.
>
> Does anyone have any suggestions where I can look for this? The developer
> who manages the Windows/SQL Server side of things insists it's the 10g
> database and not his side of the house and I have no way to prove/disprove
> his assertion.
>
> Thanks for any help/suggestions.
>
> Sandy
>

--
http://www.freelists.org/webpage/oracle-l