CODEWITHSUNDEEP
sqloracle-databasejdbc
Jeff Perrin
Jeff Perrin

Reputation: 8094

What would cause this query to stop working?

I'm working with a legacy Java app that's pulling data from Oracle. One of the queries appears to no longer work:

select {hier.*} from ecadmin.dept_hier_cache hier
connect by prior parent_deptid = deptid
start with deptid = '1234';

When I remove the '{}' brackets from around hier.*, everything works as normal. Now, as far as I can tell the app hasn't changed for over a year, so this means a change to Oracle is the most likely culprit. Any ideas on what might have changed? Version upgrade, a setting was changed, something else?

Upvotes: 2

Views: 158

Answers (2)

Doug Porter
Doug Porter

Reputation: 7897

The only thing I see about the curly brace syntax with Oracle and JDBC is here under Embedded SQL92 Syntax. Like Gary says I would not expect right off hand for that query to execute regardless.

But, potentially has the underlying Oracle driver or version of Oracle changed? Have the columns in that table dept_hier_cache changed so that the .* now returns something entirely different than it used to?

Is dept_hier_cache a view? If so has the view or the permissions of the users regarding that view changed?

Upvotes: 0

Gary Myers
Gary Myers

Reputation: 35401

As far as I can tell that never would have worked. It was never valid syntax, and I can't understand why anyone would even try curly brackets in the code.

Assuming you have source control, I'd check whether the code ever had a version without the brackets. If so, I'd suspect that the code got changed in the source but never promoted.

Possibly the string got filtered/cleaned before being executed.

Can you tell when it stopped working and what happened around that date ?

Upvotes: 4

Related Questions