monline

Sometimes you've to reverse engineer a database (or an app using a database) and all you've is some piece of data that is used in whatever function(ality) you're looking into. Eg. you see some message/text on a screen of the app and you've to figure out where and how it is used. For this you've to first find the table and the row where that data is stored. Here comes this little PL/SQL procudure handy.

Sometimes you've to reverse engineer a database (or an app using a database) and all you've is some piece of data that is used in whatever function(ality) you're looking into. Eg. you see some message/text on a screen of the app and you've to figure out where and how it is used. For this you've to first find the table and the row where that data is stored. Here comes this little PL/SQL procudure handy.

There's a very good how to on installing this particular Oracle RDBMS version on Ubuntu 11.10 here. However Ubuntu is not Debian, there're a few differences. Mostly you've to skip a few steps:

Siebel Tools makes it not easy to create a list of all locked objects. You've to do it for each potential object type and unlocking multiple objects is not easy either. Eg. you can only unlock objects that you've locked. If some developer left an object locked, you either know her/his password or you don't. In the latter case you've take over the user and log in with it to unlock the objects.

The APT repository key of Oracle's OSS site has expired on 4th September (as probably many Debian/Ubuntu maintainers have found out by now). An apt-get update gave me the following error message:
The Oracle help page at Ubuntu's wiki contains the URL for the currently valid key.

The linked page describes it very well. Just a quick note for myself in case the original post disappears:

Up til now I believed that all Oracle database connections are firewall-crippled by the well known SQLNET setup: client connects to server's 1521 TCP port (the TNS Listener), the server starts a DB process to handle the connection and tells the client to disconnect from TNS Listener and connect to a newly opened, random port. Ie. you've to open all ports on the server for traffic coming from the client. However today I've monitored with Wireshark a connection being estabilished through an SSH tunnel from an Oracle client (running PL/SQL Developer) to a 9iR2 database server and only a single connection was used the whole time!

Found a small bug in 10gR2.

A few days ago David Litchfield published a detailed analysis of the Oracle vulnerability he has found in February this year. The title says: "Lateral SQL Injection: A New Class of Vulnerability in Oracle". Finding the bug was a nice catch, but actually the feature leading to it is a pretty trivial source of problems, namely automatic datatype conversions.

I strongly discourage everyone from running Java in the JVM of Oracle 9i databases. I had two serious issues with it in a production environment (btw. fully patched to the latest patchset) and would never suggest to use Java in an Oracle PL/SQL session ever again.