Flutterby™! : Oracled 2012-12-08 19:32:52.168529+00

Oracled

2012-12-08 19:32:52.168529+00 by meuon 3 comments

When dealing with a lot of entities on the net, you are impressed with how web savvy they are, how they "get it' and make it easy to download and install their products, And then there are entities like Oracle. It took some weird rituals to download and install their "InstantClient" for Linux. partially because I am on a slow connection. Cached cookies for "accepting terms", an extra login procedure, slow downloads and login timeouts had me downloading a file, and then clearing my browser to go through the steps again and again. That I could not do a wget or use other methods to get these files (CLI server only) from behind a firewall easily turned this into an exercise in frustration. That there were no decent 'readme' files for installation, and poor instructions on their website (for Ubuntu/Debian)... made me really appreciate the effort most of the FOSS world does to make their stuff easy to install.

Some notes: You might need to do an "apt-get install libaio-dev", and instead of screwing with environment variables and such wierdies so sqlplus and PHP's oci8 client works in Apache, Ijust copied the lib* files to /usr/lib. Bad practice for supporting multiple Oracle versions but works really well. Then a 'pecl install oci8' worked beautifully.

I needed/wanted to use the PHP Oracle Connector, although I could have kludged it up in Perl. I don't know if Perl would have required the same stuff, but my assumption is "yes", because the Oracle libraries are closed source.

In this case, the organization isn't that large, and Oracle is inside of a meter makers facade of an AMI (smart meter, two way communications) system. The thing that is apparantly standard to Oracle, but through me for a loop is that tables are user specific. In this case (actual logins slightly munged to protect the clueless due to NDA's): If I login as: sqlplus admin/password@192.168.0.10/dbsys and look at the CDATA_ELEC table, I get bogus test data. If I login as sqlplus acme/password@192.168.0.10/dbsys I get the real data. The clue was looking at: select owner, table_name from all_tables and realizing that the table names were duplicated with different owners. That within a 'listener' aka 'session id' different credentials can see different tables with the same name without another designator like database name twists my head around. I get it, but it took a while to figure out. "sqlplus" is Oracle's crude command line, with poor line editing and output options compared to MySQL's or other common SQL tools.

What I wish is I had learned to become an Oracle wizard. It's an arcane and obfuscated discipline where mediorce wizards are paid well, and grand masters are very well rewarded within an ecosystem designed for maximum monetary compensation (for Oracle and it's accolytes) for mediocre usability without an investment in special potions and secret knowledge.

Done right, it's probably an incredible database system/ecosystem. Implemented poorly and not documented or supported in an embedded system where SQLite or MySQL is more apropos, it sucks. Dealing with an insane database schema (>2000 tables) where common words are spelled multiple wrong ways with a remote Chinese DBA that didn't understand how those permissions worked either (gave us access to the wrong data views) is just a pain.

#Comment Re: made: 2012-12-10 16:32:43.047939+00 by: Dan Lyke [edit history]

I don't know what it takes to get a DBI connection to Oracle, but if it was between downloading software from Oracle or using supported tools from the Open Source community, I'd figure out how to get a DBI connection to it working, and then use Perl's DBI::Shell to give yourself a command line attachment.

I don't know which organizations you're working with, but I'm always amazed at either how many levels of idiocy there are between the person talking to you and the person who can actually connect you to the database, or how uninterested in helping a customer everyone in the organization is, if the organization is large enough to be running Oracle.

Now if only I could figure out the listener name for the production database,.. without hacking the main server, They gave me a listener name for a test system. Futzed, wasted two days on something very simple.