MATCH_RECOGNIZE with Tom Kyte

Not using Oracle for work lately, I'm not up on all the latest features as much as I'd like to be...But one 12c feature I've been checking out lately is row pattern matching, aka MATCH_RECOGNIZE. Tom Kyte has a good demonstration on his column (from before his sad retirement)...

EDIT: the link is dead now. Perhaps this asktom link will help: https://asktom.oracle.com/ords/asktom.search?tag=match-recognize-examples-sql-pattern-matching

Oracle installation text rendering issue

Here's an issue I encountered when installing Oracle on OpenSUSE Linux. Just leaving this here as kind of a bookmark... (This is someone else's post and their environment is different from mine.) https://community.oracle.com/thread/3992606

Oracle 12cR2: Real-Time Materialized Views

Oracle 12cR2 is available for download - woohoo! One feature I'm excited about is the real-time materialized view, which allows a stale MV to be used by query rewrite. It sort of applies the changes from the underlying tables in real time. I was going to write up a demonstration of the feature, but a fellow named Uwe Hesse has already provided a very nice one on his blog. Check it out!

Problem with JDBC driver for Oracle 12c

Oracle has changed some of the behavior of its JDBC driver for 12c. If autocommit is enabled and an explicit commit is execute, it blows up.

java.sql.SQLException: Could not commit with auto-commit set on

OK, fair enough - it doesn't make sense to commit with auto-commit on. Comments I've read state that the JDBC spec demands this behavior, and Oracle is finally catching up. The earlier JDBC drivers don't  throw this error. I don't believe any other JDBC drivers (MS, etc.) throw this error!

Our application needs to be fixed now.

Silly little problem installing Oracle on EC2

My boss was attempting to install Oracle 12c on an EC2 instance running Linux but it was failing mysteriously, so he handed it off to me, the Database Guy (tm). The logs reported this error:

"ORA-21561: OID generation failed"

Wha'? After some head scratching and googling I learned this can happen if the host name you’re installing on is not resolvable. Boss said he did not enable DNS on the VPC (Virtual Private Cloud - an Amazon thing). I took a look at the Oracle install script (that was apparently generated by Amazon) and found that it’s getting the host name via a curl call, to retrieve meta-data Amazon provides for your EC2 instance: something like ORACLE_HOSTNAME=`curl -s http://xxx.xxx.xxx.xxx/latest/meta-data/local-hostname/`

One option would be to get rid of that call and set ORACLE_HOSTNAME to localhost. Another would be to add the EC2 host to /etc/hosts. I opted for the latter (I wasn’t convinced that was the only place that needed the host) then installation worked. (Yay!)