Whither catproc?April 25th, 2007 — ddelmoli
I had an interesting conversation yesterday with one of my folks who was attempting to install APEX into an Oracle 9i database (you know who you are).
One thing I bet Oracle is starting to have to deal with is the fact that a lot of existing databases began life as a 7.3 or 8.1 system and have been upgraded and patched up into current versions — with the end result that they’re probably a missing a few things. We were among many who encountered the issue of upgrading a database that “was originally” created as 32-bit into a 64-bit 10.2.0.3 version. I gather that the outcry over that was pretty loud.
Anyway, my friend’s installation of APEX into 9i ran into a problem that DBMS_XMLDOM was missing and came to me for help after scouring Metalink and the Internet to little or no avail.
So I got to show off my spelunking skills by digging through the scripts in rdbms/admin.
A long time ago, we all used to create databases from scratch using scripts, so we got used to running catalog.sql and then catproc.sql (to install the PL/SQL option), and then other scripts like catrep.sql (to install the replication option). I used to tell new Oracle consultants that after attending classes and reading manuals, the best way to learn about the database was to read through sql.bsq and catalog.sql.
I started off by grep-ing for DBMS_XMLDOM in all of the .sql files in rdbms/admin, just to make sure we had some file somewhere which would re-create it if we needed it. Found it in dbmsxmld.sql (a pretty obvious name for it). I then grep-ed for dbmsxmld.sql to see what called it and came up with catxdbst.sql. The file names made sense to me, as the dbms file was the base package create and the cat file was the caller — usually catalog.sql and catproc.sql called other cat files to install their stuff.
I kept going just to see where I’d end up and grep-ed for catxdbst.sql and found it called from catqm.sql. Still no catalog.sql or catproc.sql, but I’m nothing if not persistent, so I kept going and found this in catproc.sql:
Rem XDB Schema creation
Rem Comment this out until catqm.sql is finalized.
I guess it never got finalized
At this point it was pretty clear that we had the necessary code, it was probably just a matter of properly installing it. The “cowboy” in me wanted to fire up sqlplus as sysdba and just run catqm.sql — but since other people actually use this database, I suggested we open a TAR (ooops! I mean an SR), to have support walk us through the steps.
Later, out of curiousity, I wandered through the Oracle 9i doc set to see how to manually install XDB / XMLDB. A search on DBMS_XMLDOM listed the XML Developer’s Guide with 16 matches, so I figured it was in there somewhere. What’s interesting to me is that the installation instructions are in Appendix A. 26 chapters on how to use it, but the installation is an afterthought. I know, it’s automatically installed, so no need to have that up front. But there was enough complexity in the installation section that I’m glad I suggested we open the TAR on the installation.