Vivek Agarwal’s Portal/Java Blog

An IBM Gold Consultant’s weblog about IBM, Lotus, WebSphere, J2EE, IT Processes, and other IT technologies

Getting past issues with reverse engineering an Oracle schema with Visio on Vista

Posted by Vivek Agarwal on December 14, 2007

Yesterday, I had the need to reverse engineer an Oracle schema in Microsoft Visio on my now not-so-new Vista laptop. While I had the Oracle JDBC drivers copied onto my Vista laptop, I did not have the Oracle client installed. I first tried to install the Oracle 9i client, because the Oracle schema was in a Oracle 9i database instance. The Oracle 9i client installer warns about incompatibility with Vista and it certainly lived up to the billing. I saw several other users with postings about issues. I saw one obvious issue with folder permissions and changed ownership/permissions on the Oracle client install folder, but that did not resolve the issue all the way. While trying to configure an ODBC data source (only for reverse engineering the Oracle schema) , I ran into errors that had again been encountered by many. The first error was “The setup routines for the oracle in orahome92 odbc driver could not be loaded due to system error code 126”. Clicking ok on this error resulted in a secondary error alert – “Could not load the setup or translator library”. At this point, rather than spend more cycles on the issue, I switched to reverse engineering the schema using the eclipse plugin – Clay. Clay worked just fine for reverse engineering the schema (took all of 5 minutes), but the issue with it was that it did not allow me to move the foreign key connectors to make them route better. At this point, I was back looking at the Visio route and the solution was extremely simple!

All I had to do was –

  • Uninstall the Oracle 9i client (more on this later)
  • Install an Oracle 10g client that was supported for the Vista platform; download the client from here. I had to make sure to install the Oracle ODBC driver in this step.
  • Launch the Oracle 10g Net Manager
    • Create an Oracle TNS listener
    • Configure a net service name for the Oracle schema that I was trying to reverse engineer. I had to check the checkbox for Use Oracle8 Release 8.0 Compatible Identification so that I could specify the SID. I tested the service to ensure I had it right so far.
  • Create an ODBC data source where I specified Oracle in OraClient10g_home1 as the driver. When I did this, I got a few alerts for an unsupported operation, but it still seemed to work and brought up the dialog window for Oracle ODBC Driver Configuration. In this dialog, the key thing was to specify the correct net service name that I had configured earlier and that was all it took to get the Oracle ODBC driver to work.
  • Next step was to launch Visio again, and reverse engineer using the ODBC data source that I had set up.
  • And I had my desired reverse engineered Oracle schema in Visio!

All it took was the right Oracle client for Vista and me stumbling my way through the ODBC setup which does not come naturally to me! Now, you are probably wondering about my choice for my database modeling tool – yes, I am cheap – no Rational Data Architect for me! 🙂

PS: Uninstalling the 9i client for me did not work with the version of the Oracle universal installer that was installed on my Vista laptop. I ended up running the 9i client installer again to uninstall my 9i client. There must be several other solutions but this was the easiest one for me.

One Response to “Getting past issues with reverse engineering an Oracle schema with Visio on Vista”

  1. Los Angeles Engineer…

    […]Getting past issues with reverse engineering an Oracle schema with Visio on Vista « Vivek Agarwal’s Portal/Java Blog[…]…

Sorry, the comment form is closed at this time.

%d bloggers like this: