Fun with Oracle Views

Backstory

Recently I ran into exactly this problem. There was a database view that I had to query and show the results. The code worked on my local machine, it worked on the test-instance, but failed on the integration environment.

ORA-01843: not a valid month

Narrowing it down

I removed more and more expressions from the where-part of the query and in the end it turned out that there was no error as soon as I removed all comparison operations to a certain column.

SELECT * FROM problematic_view;
SELECT * FROM problematic_view WHERE some_date_column < SYSDATE;

The devil is in the details … and the many results

It didn’t take too long after knowing the column that triggered the issue and looking at the view-code to find it. Let me show it to you with an example:

SELECT * FROM order_view;
...
Returned 50 rows in 0,029 seconds. (output from our db-client)
SELECT * FROM order_view WHERE order_date < SYSDATE;
SELECT COUNT(1) FROM order_view WHERE order_date < SYSDATE;
SELECT * FROM order_view WHERE order_date > SYSDATE;
  • Always keep an open mind to what the source of a problem may be.
  • Keep an eye on your SQL view code.
  • Run some COUNT queries with where-clauses on different fields to test views thoroughly.

--

--

Software engineer from Austria. Passionate about software, likes photography, addicted to podcasts and always busy. http://paukl.at

Love podcasts or audiobooks? Learn on the go with our new app.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Paul Klingelhuber

Paul Klingelhuber

Software engineer from Austria. Passionate about software, likes photography, addicted to podcasts and always busy. http://paukl.at