Home » SQL & PL/SQL » SQL & PL/SQL » ora-00928 Please Help! (Oracle Enterprise 9.2.0.1.0; Windows Server 2003 Enterprise SP2)
icon5.gif  ora-00928 Please Help! [message #417015] Wed, 05 August 2009 13:38 Go to next message
codeMonkey
Messages: 10
Registered: August 2009
Junior Member
I am supporting an application and one of its queries recently started giving ora-00928. There has been a recent update to the database and normally I would attribute my problems to the update, but this does not occur in our mirrored development environment. The query uses all tables and no views. If there is something wrong with this, I'm not seeing it. Please Help!

select *  from animal 
LEFT OUTER JOIN
(item_event JOIN scheduled_event
ON item_event.scheduled_event_id = scheduled_event.ID)
ON (animal.animal_id = item_event.animal_id);

ORA-00928: missing SELECT keyword
Re: ora-00928 Please Help! [message #417018 is a reply to message #417015] Wed, 05 August 2009 13:43 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
That query will definitely not work in development, since it is missing the select keyword in the nested query.

You

select *  from animal 


And then you

LEFT OUTER JOIN


it with the nested query

(item_event JOIN scheduled_event
ON item_event.scheduled_event_id = scheduled_event.ID)
ON (animal.animal_id = item_event.animal_id)


And there is no select keyword in the nested query. The nested query by itself must return a result set.
Re: ora-00928 Please Help! [message #417019 is a reply to message #417018] Wed, 05 August 2009 13:50 Go to previous messageGo to next message
codeMonkey
Messages: 10
Registered: August 2009
Junior Member
I'd like to state again that this is not my query and it's actually a small piece of an even larger monster Smile.

Thanks for your very fast reply ThomasG. I hate to disagree with you but I am able to run this on our development server with identical Oracle & OS Version. The production server gives ora-00928.
Re: ora-00928 Please Help! [message #417020 is a reply to message #417019] Wed, 05 August 2009 13:55 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I hate to tell you that I don't believe you, since the query IS missing a select keyword, so there is no way it could run. Razz

Can you post the SQL*PLus session of the development server where you successfully run that query?
Re: ora-00928 Please Help! [message #417026 is a reply to message #417020] Wed, 05 August 2009 14:20 Go to previous messageGo to next message
codeMonkey
Messages: 10
Registered: August 2009
Junior Member
Check out the uploaded screenshot.
  • Attachment: SQLPlus.jpg
    (Size: 35.02KB, Downloaded 135 times)
Re: ora-00928 Please Help! [message #417027 is a reply to message #417026] Wed, 05 August 2009 14:23 Go to previous messageGo to next message
codeMonkey
Messages: 10
Registered: August 2009
Junior Member
Screenshot from production system.
  • Attachment: SQLPlus2.JPG
    (Size: 44.11KB, Downloaded 120 times)
Re: ora-00928 Please Help! [message #417031 is a reply to message #417027] Wed, 05 August 2009 14:37 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You have left me baffled and speechless. More or less. Shocked

I would have expected, only the following to work:

select * from animal 
  LEFT OUTER JOIN
  (select * from item_event 
     JOIN scheduled_event
       ON item_event.scheduled_event_id = scheduled_event.ID
  )
ON (animal.animal_id = item_event.animal_id);


Can you try, and see if that works on production?


Re: ora-00928 Please Help! [message #417035 is a reply to message #417031] Wed, 05 August 2009 14:48 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Just experimented a little, the syntax of the first query indeed seems to be OK in general.

Can you run the parts

select * from animal 


and

select * from item_event 
     JOIN scheduled_event
       ON item_event.scheduled_event_id = scheduled_event.ID


On their own on the production system?
Re: ora-00928 Please Help! [message #417036 is a reply to message #417031] Wed, 05 August 2009 14:54 Go to previous messageGo to next message
codeMonkey
Messages: 10
Registered: August 2009
Junior Member
Your query resulted in "ORA-00904: "ITEM_EVENT"."ANIMAL_ID": invalid identifier" because the outer query doesn't know about the inner query's tables.

Tweaked it slightly and it runs:
select count(*) from animal 
left outer join
(select * from item_event 
  join scheduled_event
  on item_event.scheduled_event_id = scheduled_event.ID
 ) a
ON (animal.animal_id = a.animal_id)


Re: ora-00928 Please Help! [message #417038 is a reply to message #417035] Wed, 05 August 2009 15:23 Go to previous messageGo to next message
codeMonkey
Messages: 10
Registered: August 2009
Junior Member
Both of your queries run as expected.

Assuming the query syntax is OK, what else could be causing this to fail on one machine and not the other? Might there be a patch that addresses it? Being as it's a production system I am hesitant to install patches with sound reasoning for it.
Re: ora-00928 Please Help! [message #417039 is a reply to message #417038] Wed, 05 August 2009 15:29 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, it still IS on version 9.2.0.1.0.

The "1" releases of course usually have a number of bugs that are addressed in the later patch sets. The upgrade to 9.2.0.8.0 at least should be rather painless, if you can arrange for some downtime to do it.

What was that "recent upgrade" you mentioned? Was it an upgrade in the Oracle version, or was it an upgrade of the "application" and thus the table structure?

You could check all_objects/ _tables / etc... if there are any discrepancies between development and production.
Re: ora-00928 Please Help! [message #417040 is a reply to message #417039] Wed, 05 August 2009 15:38 Go to previous messageGo to next message
codeMonkey
Messages: 10
Registered: August 2009
Junior Member
The update was for the application and table structure.

I can take a look at updating but I'm not posative it will help since I don't see the problem on another system with the same version.
Re: ora-00928 Please Help! [message #417041 is a reply to message #417039] Wed, 05 August 2009 15:41 Go to previous messageGo to next message
codeMonkey
Messages: 10
Registered: August 2009
Junior Member
Just did a visual spot check and the tables appear to be the same on both servers. This is what I'd expect since I used the same installer for the update.
Re: ora-00928 Please Help! [message #417043 is a reply to message #417041] Wed, 05 August 2009 15:58 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I did a little digging and it MIGHT be bug 768478

Can you try running

ALTER SESSION DISABLE QUERY REWRITE;
ALTER SESSION set Star_Transformation_Enabled = False;


and then retry the query on production?

Re: ora-00928 Please Help! [message #417045 is a reply to message #417043] Wed, 05 August 2009 16:13 Go to previous messageGo to next message
codeMonkey
Messages: 10
Registered: August 2009
Junior Member
THOMASG IS MY NEW PERSONAL HERO!!!

The query does run with these commands. In fact, it works with just the Star_Transformation_Enabled line. What should my next step be. Is there a way to make this stick? What are the ramifications of turning off Star Transformation?
Re: ora-00928 Please Help! [message #417046 is a reply to message #417045] Wed, 05 August 2009 16:22 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Why, thank you very much. ./fa/2877/0/

The short description of the parameter is here.

The basic options would be to either alter system with that parameter, which changes how the optimizer works somewhat, so the performance might change.

And/Or you could apply the latest patch set, 9.2.0.8, where the bug should be gone.
Re: ora-00928 Please Help! [message #417047 is a reply to message #417046] Wed, 05 August 2009 16:28 Go to previous message
codeMonkey
Messages: 10
Registered: August 2009
Junior Member
I am contacting the app vendor to make sure they support 9.2.0.8 as this is the best solution in my mind.

Thank you again ThomasG, you have been a life saver! Thumbs Up Thumbs Up
Previous Topic: ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index
Next Topic: Accessing remote path using SQL directory
Goto Forum:
  


Current Time: Sat Dec 10 03:15:43 CST 2016

Total time taken to generate the page: 0.10698 seconds