ORACLENERD

OEL: VirtualBox Guest Additions

2009-07-09T22:16:00.004-04:00

Thankfully, I managed to get Oracle (the Database) installed on Oracle (the Enterprise Linux). No easy task for a rookie.

Like most people, I don't like the 1024x768 default resolution, so I tried to install the VirtualBox Guest Additions. I've tried like 22 times now. Here are the 2 different errors I would find:

[root@cent-1 VBOXADDITIONS_2.2.4_47978]# /bin/bash ./VBoxLinuxAdditions-x86.run
Verifying archive integrity... All good.
Uncompressing VirtualBox 2.2.4 Guest Additions for Linux installation
.....................................................................
.....................................................................
.....................................................................
...............
VirtualBox 2.2.4 Guest Additions installation
This system does not seem to have support for OpenGL direct rendering.
VirtualBox requires Linux 2.6.27 or later for this.  Please see the log
file /var/log/vboxadd-install.log 
if your guest uses Linux 2.6.27 and you still see this message.

/var/log/vboxadd-install.log looks like this:

Installing VirtualBox 2.2.4 Guest Additions, built Fri May 29 19:13:34 CEST 2009

Testing the setup of the guest system

Could not find the Linux kernel header files - 
the directories /lib/modules/2.6.18-128.e15/build/include 
and /usr/src/linux/include do not exist.
Giving up due to the problems mentioned above.

I googled "OpenGl direct rendering" and found this entry. The suggestion was to add "x11" to the end like this:

/bash/bin ./VBoxLinuxAdditions-x86.run x11

Tried that out and I got a different error, but one that matched what the log says, something like "install kernel headers" blah blah blah. Greek.

I tried using apt-get...it apprently doesn't exist on OEL (or Red Hat derivatives). Ubuntu has dpkg...which doesn't work in OEL. yum! Let's try yum. Where do I find the kernel headers?

Online?

No.

I wonder if they exist on the iso images?

So I load up Enterprise-R5-U3-Server-i386-disc1.iso, navigate to the Server directory:

(Yes, it's a picture. I can't copy out of the guest yet...shut up).

Let's try kernel-headers (couldn't be that obvious could it?)

[root@oracledb Server]# rpm -ivh kernel-headers-2.6.18-128.e15.i386.rpm
warning: kernel-headers-2.6.18-128.e15.i386.rpm:  Header V3 DSA signature:  
NOKEY, key ID 1e5e0159
Preparing...               ################################### [100%]
    package kernel-headers-2.6.18-128.e15.i386.rpm is already installed
[root@oracledb Server]#

With a tried and true method, I select the next one, kernel-devel. I'm not typing this one up. I guess I should put the name here, in case someone is googling for it: kernel-devel-2.6.18-128.e15.i686.rpm

That one installed. Unmount the OEL disc, remount the VBOX Guest Additions, /bin(g)/bash(bang) ./VBOXADDITIONS_2.2.4_47978(boom) and I'm done. Now I just need to reboot to see if it really works.

Why can't it just work?

Stories of LC

2009-07-08T23:18:00.003-04:00

LC being Little Chet. And no that is not some perverted reference...

He said something pretty funny the other day, which reminded me of a bunch of little stories about him. Since I post so many stories about Kate, I figured it's about time I do one on him.

Policy
I get a cup of ice cream the other night and he immediately wants some too. Mom asks if he has had any that day, and he replies, "Yes."

"But there is no policy on ice cream."

Me: "No What?"

LC: "No policy."

Kris and I look at each other and just start laughing. He's 6 years old and he's talking about policy? He's been hanging out with his mother way too much.

To make sure we knew that he knew what he was talking about we asked him what he meant.

LC: "Well, I can have 2 popsicles a day. But there's no limit on ice cream."

Wow.

The Letter R
In Pre-K 4 a couple of years ago, his teacher asked the class to say words that began with the letter R.

The teacher started them off, "Rapunzel."

"Rabbit!"

"Rainbow."

"Chet, do you have one?"

"Craphead"

Sea World
After a day at Sea World with my parents and 2 small kids, we naturally gravitated to the Hospitality Suite. For those who don't know, Anheuser Busch used to own Busch Gardens and Sea World. Both parks, or both companies as there are more than one park for each, serve free beer. There is a two beer limit.

Anyway, Kris takes this opportunity to spend some time with Chet and she walks him over to the Clydesdales (while I drink her beer).

Mom and son, holding hands...a perfect picture.

"Mom, that's the biggest penis I've ever seen."

All the people around them began laughing...OMG.

Jackass
I believe this happened in 2006, while in Gainesville. I would drive Chet to school, St. Patrick's Catholic school. This was always a concern of mine because I have never been too careful with my choice of words around him.

My old boss had once shared a story with me about her daughter, at this same school, saying JFC (Jesus F$#%*&% Christ). I always thought the same would happen to me.

It never did. He had amazing control (besides the craphead comment above) at school.

At school.

Not at Publix though.

We're shopping one day with him sitting in the basket when this woman comes out of the aisle and gets in front of us.

"Move that F$#%*&% Cart!"

It was about all I could do to maintain composure. He was 3, maybe 4 at the time. Thankfully, the woman did not hear him. If she had, I probably would have fallen down from laughing so hard.

However, I managed to compose myself and give him a stern lecture about how inappropriate that was.

I couldn't wait to get home to tell Kris.

The First Word
I've probably talked about this before. Yes, I have. Here. The similarity between my name and shit. I've heard it all before...I actually used to introduce myself referencing Weird Science.

Me: "Hi, I'm Chet"

Them: "Ted?"

Me: "Chet"

Them: "Jeff"

Repeat this a few more times.

Finally I'd say, "You know, "Chet" like the guy from Weird Science?"

Them: "Ohhhhh...yeah, the big pile of shit!"

Me:...

That reference is only useful with people of a certain age. Twenty-somethings don't really get it.

Anyway, I always thought I could get away with teaching Chet that for his first word. Chet. Shit. Pretty close right? I can't remember if he actually said it for his first word...it's a funny story none-the-less.

I know I'm not the only one with funny child stories. Anyone care to share?

Oracle Concepts: The Data Dictionary

2009-07-08T17:33:00.003-04:00

I have come to rely pretty heavily on the Data Dictionary as I don't typically use a GUI. This stems from the fact that in my first professional IT job (reporting off of Oracle), I was given a tnsnames.ora file and something called SQL*Plus. I came from Microsoft Access...I wanted pretty pictures of my database objects. My first year was spent asking how to find the tables in a schema (schema? what's that?), how to view the SQL that made up a view, and how to view the source code.

Find Tables

SELECT * FROM user_tables;

View Text

SET LONG 100000
SELECT text FROM user_views WHERE view_name = :your_view;

View Source

SELECT line, text 
FROM user_source 
WHERE name = :package_name
  AND type = :package_spec_or_package_body;

Needless to say it wasn't fun.

Without pain there is no progress.

What is the Data Dictionary?

...data dictionary, which is a read-only set of tables that provides information about the database. A data dictionary contains:

* The definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on)
* How much space has been allocated for, and is currently used by, the schema objects
* Default values for columns
* Integrity constraint information
* The names of Oracle Database users
* Privileges and roles each user has been granted
* Auditing information, such as who has accessed or updated various schema objects
* Other general database information

Not completely read-only as I found out once. What's the point? None really. I just like being able to copy and paste the documentation. OK, that's only partially true.

All the information that you retrieve via your nice GUI interface comes from these tables. SQL Developer, JDeveloper, Toad, etc. I believe some even have an option to view the SQL being submitted to the database. If you haven't used (knowingly) the Data Dictionary before, check it out. You can easily view all 651 views (10gR2) by issuing:

SELECT * FROM dictionary;

Happy hunting.

Oracle: Getting Started

2009-07-08T00:56:00.006-04:00

A friend of mine recently expressed interest in learning Oracle.

Here's what I sent him:

Start here:

Oracle Concepts Guide - http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/toc.htm

You don’t have to read everything, but it’s probably the best place to start. I’d be more than happy to answer some of the more “inane” questions (How do I do this? How do I connect? Etc).

Oracle Documentation (List of Books) - http://www.oracle.com/pls/db111/portal.all_books

Don’t be overwhelmed by that. It’s ridiculous how much stuff there is out there. I list some books out by discipline:

DBA
--  2 Day DBA - http://download.oracle.com/docs/cd/B28359_01/server.111/b28301/toc.htm
----  Easiest place to start (after the Concepts Guide)
--  Oracle Database Administrator’s Guide - http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/toc.htm
----  Definitely getting into the nitty gritty stuff here.
Developer
--  PL/SQL Language Reference - http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/toc.htm
----  That should help you get started with PL/SQL if you so choose

For either role, the bible is probably the SQL Language Reference manual: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/toc.htm

Usually what I do, is just download the software and try to get it running. It’s super easy on Windows, a bit more difficult on *nix environments (I just recently installed Oracle on Oracle Enterprise Linux, my first go at a non-Windows environment).

The documentation above is for 11g, the latest version. You can get the software here: http://www.oracle.com/technology/software/products/database/index.html I’d suggest installing EE, might as well go full boat. Plus, it doesn’t really make a difference resource wise. If you have 1.5 to 2GB RAM, you should have plenty. You can run an instance at about 512MB.

It requires an account (free) and they do not spam.

Also, try Oracle Database XE, a free (as in beer) standalone edition built off of the 10.2 kernel. Application Express is used as the front end (a pretty sweet GUI tool).

http://www.oracle.com/technology/software/products/database/xe/index.html

Hope that doesn’t scare you off too much. ;)

Any other pointers? I know there are tons of books so link them up if you feel they are worthy.

Classic: Application Developers vs. Database Developers II

2009-07-07T17:15:00.001-04:00

The original (with all the fun comments) can be found here. Originally posted on December 9, 2008. This is the "followup" to yesterday's post.

You can read the first article here. My application developer friend, Mr. M, emailed me and another fine gentleman this little blurb recently:

Mr. M:

OH YEAH BABY!!! TEN TIMES FASTER!!!! YEAH!!!!!!!!

Hey seriously, what a tub of shit Oracle is. Where does this myth come from that it's such a great platform? Their client tools suck balls and it's generally just a pain in the ass to work with from a developer's point of view. But devs for some reason are under this impression that from thew server perspective it's rock solid and performant. Well, it may be solid, but it's a fucking turd. Our dba here - definitely an Oracle guy - has been tasked with looking into moving us onto an oss db. He basically refuses to even look at MySQL, stating that it's a mickey mouse worthless pile of shit (can't really argue with him there lol), so that basically leaves Postgres. So it's been a few weeks now, and he will basically admit now that Postgres completely waxes Oracle as far as performance goes. LOL We run RAC in production too. He's looking at failover, replication, blah blah blah now, we'll see what the verdict on that is. Oh, and Oracle AQ? That's a worthless pile of shit too. Why do they tack a fucking message queue that doesn't play nice with jms onto the fucking database, which is probably already overworked? Oh wait, that's right, they're in the business of selling per cpu licenses! Cocksuckers.

This was prompted by a recent Oracle email blast about the Exadata storage system/Warehouse.

As I did before, I'll just put the email here.

Me:

Agreed, their client tools aren't all the great. Which ones are you using?

I use SQL*Plus (naturally), SQL Developer and JDeveloper. The latter 2 tie in very nicely with Subversion. With JDeveloper, when I want to try out Java, it's got a pretty good suite of tools.

Oracle starting out catering to businesses, Microsoft started with the consumer. Oracle has made pretty good strides in making their software more usable while Microsoft has made theirs more scalable.

I haven't used AQ a whole lot and definitely not with Java. I do know that it's billed as JMS compliant.

Postgres has it's place and so does Oracle. It is a great platform if you know how to leverage it. PL/SQL is a 3GL (or is it 4) and you can write entire applications using it...and if you do that right, an incredibly robust and scalable application at that.

Mr. M:

"It is a great platform if you know how to leverage it. PL/SQL is a 3GL (or is it 4) and you can write entire applications using it...and if you do that right, an incredibly robust and scalable application at that."

NO!!! NO!!! NOOOOO!!!

I want to beat people like you who say this with a ball pean hammer. There are only a select few individuals on this earth who can write and read application logic written in SQL. AVOID THIS ANTI-PATTERN AT ALL COSTS! What is it with you f_cking database guys??? Why do you always want to stuff as much crap into the db as possible?

DUDE! SQL IS A PILE OF SHIT FOR EXPRESSING APPLICATION LOGIC!!!

It's fine for querying and manipulating sets of data, in a relational database. But it is a worthless sack of shit for expressing application logic!

I'm having to dig through this f_cking abortion right now because some Oracle f_ckhead thought "you can write entire applications using it!" Blog that, mofo!

This was followed by a package he had been working on. I wouldn't say it was the greatest, but it wasn't all bad either.

Me:

goodness gracious.

"DUDE! SQL IS A PILE OF SHIT FOR EXPRESSING APPLICATION LOGIC!!!"

disagree (naturally). It's incredibly easy to do, you just don't know how yet...and it seems even the Oracle professionals out there don't either.

I'll tell you this, the crazier the SQL or PL/SQL needed to derive and manipulate data the poorer the design. Start with a good design and it all becomes very simple.

Of course note the use of "naturally" in my lexicon. Thanks Jake.

Mr. M:

well dude, we are back to our old discussion - you arguing that procedural sql code is perfectly fine for building apps, and by extension, that the last 20 years of computer science have basically been a misguided lost journey down the meandering, fruitless trail of oop. um.....no. select this from that. otherwise keep that sql crap caged up where it belongs.

btw, do the db guys here suck? seriously. i'm not competent enough to judge. (to be fair, apparently that crap i sent you is fairly old stuff, but still....)

Me:

I would say, based on limited knowledge of software development, that the OOP movement was started because the database (specifically Oracle) was not mature enough to do what was needed. Plus, I seem to recall that the OOP movement was supposed to have solved all the world's problems by now.

It's further propogated due to the needs you specified in our discussion that day at WellCare (i.e. performance). I still believe that if application developers better understood how a DB works (Oracle, MySQL, SQLServer, etc) they would end up writing less code. Database constraints alone force you to write less (and better) code simultaneously ensuring good data.

The code that I did look at (first 1000 lines or so) isn't great.
1. With all those IF THEN ELSE statements it's telling me that there's probably a better way to store the data. Looks like they're missing an attribute that should be contained with a track.
2. using Object (PL/SQL) types to store data in the way they seem to be doing it is not the best way. Again, probably a design issue.
3. When you do something like this:
   UPDATE pb_album_metadata
      SET primary_digital_flag = 0
    WHERE album_id IN (SELECT b.album_id
                       FROM (SELECT a.album_id AS album_id,
                                MAX(a.album_id) OVER (PARTITION BY a.standard_upc) AS latest_album_id
                             FROM pb_album_metadata a
                             WHERE a.standard_upc = g_album_tab(1).standard_upc ) b
                       WHERE b.album_id <> b.latest_album_id )
         AND primary_digital_flag <> 0;
They should probably have considered end_date as an attribute of the album metadata. While analytic functions are pretty cool, they're more for analyzing (OLAP) and not OLTP environments.

That's for starters and without table definitions...

Me (again):

oh yeah...and PL/SQL is/was built on top of ADA, FYI.

Mr. M:

"I still believe that if application developers better understood how a DB works (Oracle, MySQL, SQLServer, etc) they would end up writing less code. Database constraints alone force you to write less (and better) code simultaneously ensuring good data."

Huh? What are we not understanding? What would be an example of a constraint that would force us to write less and better code?

Me:

CHECK, NOT NULL (same as CHECK) and FOREIGN KEY constraints all fit into that category.

Instead of having to check if a value is NULL when submitting it to the database, just submit and let the database throw an error, which you'll conveniently capture in your Exception block. Same goes for CHECK constraints, columnA should be either one of three values (test, test1, test3), you won't have to check that in your application code, just catch the exception. FKs insure good data (proper relationships).

A different perspective. If you are going to pay for it, use that damn thing. If you don't care, don't do it. But over the course of an "enterprisey" application, you'll end up writing more code and make maintenance that much more difficult (did I do it there?). Just those constraints will force you and the teams of application developers to put good data into the database.

You can still do it in the application of course (form validation is a good place)...

Mr. M:

Ahh, jeez dude, I wasn't sure if you were referring to the literal "constraint" or not.

Dude, even f_cksticks like ~~redacted~~ I think have a decent enough understanding of when and how to use db constraints. It's when you get into things like cursors or cost plans of subselects and anonymous tables (i think that's the name for it - where I say select something from (select some other crap). Then we defer to db gurus like yourself.

But dude....."you won't have to check that in your application code, just catch the exception".......uh, don't ever repeat that off our little email group. And definitely don't go posting that on your blog. F_ck me man, it's a damn good thing we keep you db folks caged up in that rdbms box....

Me:

So we've had this discussion at work...for a high transaction system, do Java/C/etc handle exceptions well or what?

Why is it bad to deal with exceptions rather than coding to avoid them?

I highly doubt even ~~redacted~~ understood database constraints...him and his cohorts believed all database software would be commoditized and MySQL would be king in short order.

Mr. M:

"for a high transaction system"

Or for any system really....

To use your example of check constraints (is this value one of....) or not null constraints, checking these rules in the Java code and in the database code would seem to violate DRY. But we do that alot, and it is acceptable in certain cases. For instance, we also probably violate DRY if we're checking this same rule say in Javascript on the front end. But we accept this tiny violation of DRY because it tends to make for a better user experience and as a performance gain too, for we avoid the round trip to the server. Now, what your advocating here is close to the same thing. You're basically saying, don't check for the not null constraint in Java code, just go ahead and hit the database, let the database throw back an exception to the Java code, presumably correct the problem, and then make another roundtrip to the database again. Dude, what are you thinking?!? This to say nothing of the fact that this also could be considered a violation of Fail Fast, and a violation of Defensive Programming - what happens if the dba forgot to add the not null constraint in production?

Dude, listen to this guy. For a "high transaction system" basically you want to treat the database, as much as you can, as just a dumb data holder. A f_cking dumpster, that you just throw sh_t into and pull shit back out, using no vendor-specific features if at all possible.

Again, for we've had this discussion, but even in everyday apps, not just on Wall Street, the database is the bottleneck. And the database is the hardest layer to scale. So given those facts, you should design your app to do as little work in the database as possible.

I was laughing at this point because the link above points to one of our consulting architects (I'm not really sure what his role is at this point).
Me:

i agree in any application that you want to minimize the number of round trips...

shocker...he's one of our architects. he's spot on in many instances, but...

database is the bottleneck because people don't know how to write SQL. I'll certainly concede the wall street applications (for the time being anyway), but the rest of us with what we do on a daily basis...Oracle will scale far beyond the demands they place. When that bottleneck shows up, 9 times out of 10 some dumb-ass c#/java guy thought he could write a better query than i. besides, what's the idiot doing anything but a simple join anyway? probably poor data model to start with...and we're right back where we started (sort of).

Mr. M:

"database is the bottleneck because people don't know how to write SQL.....some dumb-ass c#/java guy thought he could write a better query than i."

Dude, I'll grant you, people don't know how to write SQL, myself included. But that's not always why the database is the bottleneck. I think it's safe to say that's not even the majority of the reason. Yes, there are apps written by people who were just idiots, and they needlessly pummel the database, but that's definitely not the majority of scenarios. At my work the database is the bottleneck, and we run RAC in production. It's my understanding that even with RAC, there is a limit to how much you can scale that out. But any layer up from the database we are basically unlimited in how much we can scale that out. So it's very easy to stick another Apache box in front, or fire up another Weblogic server. But I can't do that with the database. We have 24 Weblogic servers for ~~redacted~~. The database is the bottleneck. And we don't have shitty sql code in the app. In fact, we have very few hand-written queries anywhere in the app. Persisting something to a database is really a low-level concern that as an application developer I shouldn't even have to be bothered with, except for the rare corner cases where the persistence abstraction I'm using is just a little bit too generic to handle things effectively. And we don't use these ORMs because we don't know how to write sql. To effectively use an ORM tool requires a deeper understanding of sql and databases than simply being able to write solid SQL code. (Not saying Java devs who use ORMs know SQL better than a dba, just that it requires better sql skills than is required of a Java dev to simply write JDBC/SQL.) Now, before you try to tell me that my ORM library isn't able to write as efficient of sql code as a dba of your caliber, keep in mind that ORM tools are pretty advanced. They're able to intelligently do things like batch sql updates, and let you apply transactional semantics much easier than with raw jdbc/sql. But the overwhelming reason developers have so thoroughly adopted ORM is because Structured Query Language is such a nasty piece of shit for expressing application logic. SQL is a declarative, procedural language. It's totally unsuited for writing application logic! This, more than anything else, is why organizations and dev teams should seek to restrict what is handled within a relational database as much as possible - because the programming interface to it is a fucking ancient backward dying dinosaur.

Mr. V (note, not Mr. M):

My 2 canadian cents:
The polyglot approach "... use different languages for different domain ..."
Database was developed to manipulate data and should remain there.
General purpose language was developed to encapsulate logic and should remain in that domain.
You should not use DB to encapsulate business logic (in my opinion) no more than you would use HTML to create complex logic.

While Java, C#, Python, etc are described as General Purpose languages, they, too, are really domain-constrained. Their expressiveness are confined (and should be) to express low-level, tersed, explicit, verbose, and repetive logic (if that makes any sense). Languages such as these are more suitable for low-level abstraction on top of which, richer, more expressive languages can be built. We are seeing this now with the emergence of languages on the JVM (and arguably on the .Net's CLR).

I think SQL as we know will take a back seat and a smart developer somewhere will create a new domain-specific language that lives on the VM and will push the SQL expression out of the RDBMS and closer to the code that needs it. We are not there yet, but Microsfot is trying (see LINQ and all ORM papers). This is not to say that there won't be isntances where tuning in the SQL-native language won't be necessary. However, 80 to 90% of simple CRUD cases will be handled closer to the logic code that uses the data code.

Again, that's my 2 canadian cents... I could go on. But I have a meeting with ~~redacted~~.

Mr. V, I believe, is a little bit more sensible. Mr. M on the other hand is just trying to rile (sp?) me up.

Me:

Someone will probably create something like that, but it still gets at the heart of one of my arguments, many developers don't know how to use a database thus will go to any means to circumvent it. Embrace it I say.

Ultimately for me, it comes down to simplicity. I can write an application using PL/SQL that will scale very well for 90% of the solutions. Of course that doesn't include the "fancy" javascript/Ajax stuff needed for the UI. In my ever so humble opinion, this is a much better solution for a business in the long run.
1. You're more likely to change the middle tier than the database tier. Java, asp, Ruby, what's next?
2. Fewer moving parts means lower cost of ownership, even with the CPU costs. One person, one, was able to build and maintain a 350 page website. Be hardpressed to do that with the more expressive languages.

I think all of us are purists and very passionate about what we do. It's probably the main reason we get along. I thoroughly enjoy these conversations because it does force me to think...and that's always a good thing.

Mr. V:

Haaa chet.
You sorta made my point than diverged away. Building an app in all PL/SQL is dangerous. It's no safer than me building an app in all Java. I can build very limited app in all Java. The moment I need to interact with other domain (UI, data, low-level native code, etc), I have to switch to something that is closer to what I am trying to do. If I need to create UI, I will pick a ui-centric environment, when I need to talk to DB, I will pass connect to a db and send in my SQL, and so forth. I will use Java as the glue to bring it all togher.

In the end, we may be saying the same thing, but using different accent. O well.

And that's where it ended. I must say it's always fun. Mr. M and Mr. V are both very smart individuals and I highly respect what they do. We have different perspectives...but I think they listen, if only a little, as I listen to them. Their voices creep up on me especially now...which is definitely a good thing.

Classic: Application Developers vs. Database Developers

2009-07-06T21:17:00.002-04:00

One of my favorite "articles" of all time. I love these types of conversations, in the DB or the Middle Tier? For the vast majority of us, the Database will do just fine. As I've learned more about the data grids and the like, there are trade-offs, which aren't often discussed. One way or another you lose data (say you decide to only UPDATE one time instead of 60). Originally posted on February 20, 2008. Enjoy.

It started innocently enough with this article. I sent it out to about 20 colleagues.

The best line from the article:
"Jerry: "Yeah, databases cause lots of headaches. They crash all the time, corrupt data, etc. Using text files is better."

One of my more recently arrived colleagues (I'll call him Mr. M) replied to everyone with this statement:

"Kind of funny actually, databases are less and less important at the large investment banks, where they basically load everything up into a data grid across a several hundred node cluster. Writing to the db is way too slow."

This started a day long exchange of emails. What follows is the entire thread (up until my last post tonight).

Me:
"I would just argue that they don’t necessarily know how to write to databases. I would however love to see benchmarking done on both methods. Would be an interesting test..."

Mr. M:
"Well, my understanding is they just can’t scale out the db enough. Even something like Oracle RAC won’t work. And outside of the military, these are probably the top 1% of programmers in the world building this stuff."

Me:
"A benchmark would be the only way I would believe it.

If you said the top 1% of database developers tried it and failed, I would be more likely to agree.

My experience is that application developers != database developers. Different type of thinking involved."

Mr. M:
"'A benchmark would be the only way I would believe it.'

Do you need a benchmark before you would believe in-memory retrieval is faster than disk retrieval? Essentially, this is what we’re talking about.

'If you said the top 1% of database developers tried it and failed, I would be more likely to agree. My experience is that application developers != database developers. Different type of thinking involved.'

Why? It’s an issue to do with application performance not simply database performance. Database concerns are a subset of application concerns, essentially a specialization, requiring less encompassing knowledge. ;)

From the article you linked to (http://www.watersonline.com/public/showPage.html?page=432587)

"Better data management is the answer, says Lewis Foti, manager of high-performance computing and grid at The Royal Bank of Scotland (RBS) global banking and markets. "For very large compute arrays, the key issue is data starvation and saturation. This problem requires data grids with high bandwidth and scalable, parallel access,
...
Banks are learning that data management in a distributed grid environment is very different from online transaction processing. "With so many data sources, distribution channels, demands for aggregation and analytics, surges in data volumes and complex dynamics between the flows, we need to manage 'data in motion' and give up the notion that data is somehow stored. It's dynamic, not static," says Michael Di Stefano, vice president and architect for financial services at GemStone Systems
...
There is even some debate over how small a unit of work can be put on today's grids. Di Stefano at GemStone, for example, says, "One client has gone from 200 trades per second in a program trading application to more than 6,000 trades per second. This shows what the technology can do."

Yep, the writing is on the wall. Oracle knows it too.

http://www.google.com/search?hl=en&q=oracle+buys+tangosol&btnG=Google+Search"

Me:
"Good points. If it is in-memory it would be faster. I have not had the pleasure to work on such a system.

I do disagree with the database concerns being a subset of application concerns. The data drives the app. We’re probably getting religious at this point (or am I)."

Mr. M:
"‘The data drives the app.”

Exactly, but who’s to say where the data comes from or in what format? My application data may reside completely in xml files, or maybe I get it from some third party web services a la the en vogue “mashup.” Heck, I may not even need to worry about a database anymore…. http://www.amazon.com/gp/browse.html?node=16427261 The database is only one particular concern of the overall application. And it’s the application that matters. Data is useless if it just sits on a disk somewhere. It’s the ways in which the application lets the users view and manipulate the data that adds value to the business.

Yep, definitely a different type of thinking between application developers and database developers."

Me:
"Definitely religious now.

Applications come and go, data stays the same. Think Green Screens, EJBs, Ruby…what’s next?"

Mr. M:
"'Applications come and go'

Exactly. Businesses are not static, nor are the markets they compete in. Changing applications are a function of changing business processes and changing markets.

'data stays the same.'

Nonsense. Otherwise UPDATE would not be an SQL reserved word. If you mean database technology stays the same, well, I’m more inclined to agree with that.

'Think Green Screens, EJBs, Ruby...what’s next?'

Whatever comes along to let the business more effectively respond to current market realities. Application platforms have evolved much faster than database platforms have. They’ve had to, their sphere of operation is much broader than that of databases, this is only natural, they deal with much broader concerns than do databases. Databases in the internet era function in essentially the same role they did in the era of dumb terminals. Clearly application platforms have evolved orders of magnitude more. Hence the statement, database concerns are a subset of application concerns.

Here’s a simple test….if I take some business application and I’m forced to throw away one or the other, either the database or the appl- wait a second, it doesn’t even make sense to finish it, does it? The business can live without the database. I could do all kinds of things with the data, I could stick it anywhere. The business can’t live without the application though. Another way to look at is, what do the business users look at, test, approve, and use? The database? Of course not, they look at the application. They could care less whether the data sits on disk in an RDBMS, xml, or flat files."

Me:
"We obviously violently disagree.

Without the database (and I use database and data interchangebly), the business could no longer function. The app is meaningless. How would you contact your customer? You couldn’t find it.

'Exactly. Businesses are not static, nor are the markets they compete in. Changing applications are a function of changing business processes and changing markets.'

Poorly designed applications…that is all."

A Feisty Colleague:
"Using data and database interchangeably is incorrect. A database is a mechanism for data storage. XML data sets and flat files are mechanisms for data storage, too. So is a file cabinet, because, the data doesn’t have to be electronic, it could be … gasp! … on paper, and the application to use that data would be hands for holding the paper and a pencil to update and add data to the page."

Me:
"No it isn’t. I take into account xml files, flat files, web services (but not paper, unless it’s scanned) and all that. It would be consumed by the database and then accessed by the application via SQL.

(that’s for Mr. M and the feisty one)"

At which point someone forwarded the home page for Oracle's TimesTen In-Memory Database.

Me:
"A database on/in the mid-tier...Perfect!"

Mr. M:
"Implicit acknowledgment that disk IO operations that come with traditional database access simply can’t match the performance of in-memory data access (a point which you previously were unconvinced of but now seem perfectly accepting of the idea once you see it’s got Oracle’s imprimatur on it).

Of course, why any application developer would want to program against an SQL interface if they weren’t forced to is beyond me. It is orthogonal to the programming model of most application platform languages.

Surely Oracle recognize this fact too or they wouldn’t be buying Tangosol and other data grid technologies. Of course, most of those products are far more technically advanced than TimesTen or anything Oracle has in that space.

Incidentally, it’s illustrative to note that Coherence and other products like it were for the most part designed and built by application programmers. The development of all these products is pretty much driven by the needs of the large investment banks on Wall Street. These trading applications simply had too many concurrent transactions to use an RDBMS (a problem quite a number of public domains now share, most famously google.com, nope, no RDBMS there, yet miraculously there is still data). The database just simply would not scale to such a degree. So the application developers, by necessity, came up with an alternate solution that did work, a fully transactional cache of data replicated across a cluster with node numbers in the thousands, and no relational model whatsoever to speak of. A perfect example of how database concerns are only one, sometimes small, concern amongst many that application developers must be aware of and ready to solve."

Me:
"Like you said initially, the top 1%.

Many of us will never touch a system like this.

I will certainly concede that it is faster (still would love to see benchmarking though), but that still leaves 99% of the applications out there that do not require that kind of performance."

Me (again):
"And don’t forget, I use data and database interchangeably. Applications are nothing without the data right?

As to the object/relational impedance mismatch...well, more people that don’t know how to work in sets. Looping is what they understand. I understand the application side more than you seem to give me credit for.

I’m not saying applications aren’t important, they are. Data (databases) and applications go hand in hand. If the application went away though, they could still access their data via SELECT statements (yes, via an application client tool), however painful that may be. Applications make retrieving data that much easier for our users.

If anyone wants to unsubscribe from this mailing list, just let us know. This is fun for me (I’m guessing Mr. M too)."

Needless to say it was a fun day. It didn't get [too] personal. More than anything I'm happy to have an equally passionate colleague.

Besides, he claims he was just fracking around with me. ;)

SQL: SYS_CONTEXT

2009-07-05T23:02:00.002-04:00

In my experience so far, I've seen very few places where SYS_CONTEXT is used. It is typically used in conjunction with CONTEXTs and also Virtual Private Database (VPD).

Oracle has a built in namespace called USERENV and their is a wealth of information you can retrieve from there. I wrote up a quick view to demonstrate the use of SYS_CONTEXT:

SELECT
  SYS_CONTEXT( 'USERENV', 'ACTION' ) action,
  SYS_CONTEXT( 'USERENV', 'AUTHENTICATION_METHOD' ) authentication_method,
  SYS_CONTEXT( 'USERENV', 'BG_JOB_ID' ) bg_job_id,
  SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' ) client_identifier,
  SYS_CONTEXT( 'USERENV', 'CLIENT_INFO' ) client_info,
  SYS_CONTEXT( 'USERENV', 'CURRENT_SCHEMAID' ) current_schemaid,
  SYS_CONTEXT( 'USERENV', 'CURRENT_SCHEMA' ) current_schema,
  SYS_CONTEXT( 'USERENV', 'CURRENT_USER' ) current_user,
  SYS_CONTEXT( 'USERENV', 'DB_DOMAIN' ) db_domain,
  SYS_CONTEXT( 'USERENV', 'DB_NAME' ) db_name,
  SYS_CONTEXT( 'USERENV', 'DB_UNIQUE_NAME' ) db_unique_name,
  SYS_CONTEXT( 'USERENV', 'HOST' ) host,
  SYS_CONTEXT( 'USERENV', 'INSTANCE' ) instance,
  SYS_CONTEXT( 'USERENV', 'INSTANCE_NAME' ) instance_name,
  SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) ip_address,
  SYS_CONTEXT( 'USERENV', 'ISDBA' ) isdba,
  SYS_CONTEXT( 'USERENV', 'LANG' ) lang,
  SYS_CONTEXT( 'USERENV', 'LANGUAGE' ) language,
  SYS_CONTEXT( 'USERENV', 'NETWORK_PROTOCOL' ) network_protocol,
  SYS_CONTEXT( 'USERENV', 'NLS_CALENDAR' ) nls_calendar,
  SYS_CONTEXT( 'USERENV', 'NLS_CURRENCY' ) nls_currency,
  SYS_CONTEXT( 'USERENV', 'OS_USER' ) os_user,
  SYS_CONTEXT( 'USERENV', 'SERVER_HOST' ) server_host,
  SYS_CONTEXT( 'USERENV', 'TERMINAL' ) terminal
FROM dual

Which yields the following:

ACTION:  NULL
AUTHENTICATION_METHOD:  PASSWORD
BG_JOB_ID:  NULL
CLIENT_IDENTIFIER:  NULL
CLIENT_INFO:  NULL
CURRENT_SCHEMAID:  88
CURRENT_SCHEMA:  CJUSTICE
CURRENT_USER:  CJUSTICE
DB_DOMAIN:  NULL
DB_NAME:  testing
DB_UNIQUE_NAME:  testing
HOST:  cdj-laptop
INSTANCE:  1
INSTANCE_NAME:  TESTING
IP_ADDRESS:  192.168.1.4
ISDBA:  FALSE
LANG:  US
LANGUAGE:  AMERICAN_AMERICA.WE8MSWIN1252
NETWORK_PROTOCOL:  tcp
NLS_CALENDAR:  GREGORIAN
NLS_CURRENCY:  $
OS_USER:  chet
SERVER_HOST:  oracledb
TERMINAL:  UNKNOWN

Basically, these are global variables that Oracle stores on the current session/user.

You can create a context for just about anything you want. I've used them in the past along with table based security in a stateless environment. In essence, I create a record in a table with a SESSION_ID, I then store that in the context (global variable) and I use that to reconnect to the database each time.

I promise to have an example of using it in the near future.

Learning From Failure

2009-07-02T00:21:00.005-04:00

I think I began reading The Daily WTF about 4 years ago. I don't miss or skip a post.

I remember this one time, probably about the time I began reading the site, I had to automate a process to move files from one server to another. Originally, I had tried to create a network drive (yes, it was Windows) on the database server so that I could just use a simple Java class to read the directory and then load the files via DBMS_LOB.

I had ultimately decided on a service, but I didn't know how to write one for Windows. Then I found the Java Service Wrapper which would allow me to write the guts in Java and then install it on Windows as a service. Perfect.

Now that I had that settled, I had to figure out how to detect when a file was read to be moved. I decided on a looping mechanism, to check every minute or so, to see if a file was available. It looked something like this (I'm a tad rusty, so bear with me):

package project1;

import java.util.Date;

public class Class1
{
  public static void main(String[] args)
  {
    Class1 class1 = new Class1();
    Date d = new Date();
    long l = d.getTime() + 1000000000;
    String s = String.valueOf( l );
    
    for ( int i = 0; i < l; i++ )
    {
      //some sort of MOD "wait" here, then check for the file
    }
  }
}

It wasn't pretty, but it seemed to work.

Then I got a call from the server admin.

SA: "You've got something running on this machine that's spiking the CPU."

Me: "Really? I can't think of anything."

SA: "Well, take a look and see if you can find anything."

Me: "10-4"

Sure enough, go into Task Manager and there's java.exe hogging up all the CPU. WTF?

I just ran this on my machine and you can see the CPU start to spike:

Off to Google to see what I can find. During my research, I found mention of a small method called Thread.sleep(long). So I replaced my brilliant add 1000000 to the current date with Thread.sleep(6000), or whatever equals 60 seconds. Problem solved.

A short time later I read a post on The Daily WTF about the same exact problem (I can't find the exact post for the life of me). The "victim" did the exact same thing I did. The solution was the Thread.sleep() call.

Me = FAIL

One more short example.

Over beers, a friend (see last entry) of mine and I were discussing the failure of the North Korean missile launch. I said, "Idiots." He said, "They're going to learn a lot more from that failure than they would have had it suceeded."

Spoken like a true engineer I guess.

The point? You learn by trying. You learn my doing. You learn by failing. Whether you realize it or not, you learn. (Well, some people don't, but that's another post). If you're reading here though, that probably means you have a passion for what you do. That means that you are trying. You are learning (maybe not here specifically ;).

Here's to trying and failing and hopefully trying and succeeding.

Constraints: ENABLE NOVALIDATE

2009-06-29T23:45:00.002-04:00

Yesterday while perusing the Concepts Guide, I stumbled across the ENABLE NOVALIDATE keywords for the definition of a Foreign Key constraint. I've always known it was there, just never used it, or thought to use it.

It can be a big benefit while working on a legacy system.

Suppose you have a table, T_CHILD:

CREATE TABLE t_child
(
  child_id NUMBER(10)
    CONSTRAINT pk_childid PRIMARY KEY,
  soon_to_be_parent_id NUMBER(10)
);

INSERT INTO t_child
  ( child_id,
    soon_to_be_parent_id )
SELECT
    rownum,
    TRUNC( dbms_random.value( -9999, -1 ) )
FROM dual
  CONNECT BY LEVEL <= 10;

This table has been around for quite some time. You decide that you would like to constrain the values in the SOON_TO_BE_PARENT_ID column. First, here's the data that exists:

CJUSTICE@TESTING>SELECT * FROM t_child;

  CHILD_ID SOON_TO_BE_PARENT_ID
---------- --------------------
         1                -5560
         2                -1822
         3                -2499
         4                -7039
         5                -8718
         6                -1019
         7                -9997
         8                -9553
         9                -4477
        10                -1458

Now I'll create a table that will contain the values I want to constraint SOON_TO_BE_PARENT_ID to, call it a lookup or reference table.

CREATE TABLE t_parent
(
  parent_id NUMBER(10)
    CONSTRAINT pk_parentid PRIMARY KEY
);

I'll populate it with some data:

INSERT INTO t_parent( parent_id )
SELECT rownum
FROM dual
  CONNECT BY LEVEL <= 10;

CJUSTICE@TESTING>SELECT * FROM T_PARENT;

 PARENT_ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

Now I'll add the constraint that references the PARENT_ID column of T_PARENT

ALTER TABLE t_child
  ADD CONSTRAINT fk_parentid
  FOREIGN KEY ( soon_to_be_parent_id )
  REFERENCES t_parent( parent_id )
  ENABLE
  NOVALIDATE;

and rename the column to PARENT_ID:

ALTER TABLE t_child RENAME COLUMN soon_to_be_parent_id TO parent_id;

What will this do? I should no longer be able to enter a value into T_CHILD.PARENT_ID that does not exist in T_PARENT, but it will ignore anything that already exists.

INSERT INTO t_child
  ( child_id,
    parent_id )
VALUES
  ( 11,
    11 );

INSERT INTO t_child
*
ERROR at line 1:
ORA-02291: integrity constraint (CJUSTICE.FK_PARENTID) violated - parent key not found

Perfect! Now I'll add a value that does exist in T_PARENT.

INSERT INTO t_child
  ( child_id,
    parent_id )
VALUES
  ( 11,
    10 );

1 row created.

Win!

This is just another reminder why you must read the Concepts Guide. By the way, I found the quote I was looking for from Mr. Kyte (h/t @boneist)

"...if you simply read the Concepts Guide...and retain just 10%..., you’ll already know 90% more than most people do"

Classic: DBMS_APPLICATION_INFO

2009-06-29T17:44:00.002-04:00

I've decided to post a few "classics" from long before anyone paid attention...as opposed to now, when 30 people pay attention. I originally posted this on 12/02/2007 at 10:04 PM which you can find here.

Instrumentation has something that I have come to rely on fairly heavily. I believe I first read about it on asktom, but the one that really spurred me on was this post on instrumentation on his personal blog.

Initially, I couldn't really wrap my head around instrumentation. I don't know why it was so difficult; I had a similar problem with sessions when I first started my career. I look back now and it just seems so obvious.

Now that I am doing datawarehouse work, nothing is fast. Fast to me is now one hour to load 30 or 40 million records. No more split second queries for me.

We currently use no tools. It's straight PL/SQL. Instrumentation of the code is ideal. Actually, it's more instrumentation to aid monitoring. The tool most easily used is provided by Oracle in the DBMS_APPLICATION_INFO package.

There are three subprograms that I use most, SET_MODULE, SET_ACTION and most importantly SET_SESSION_LONGOPS. I hadn't started using it until this year, I mainly stuck to the first two. SET_SESSION_LONGOPS is now part of my procedure/function template I've created in JDeveloper.

What it allows you to do is set a row in the v$session_longops view (I know it's not actually putting the row in the view...it's the underlying table, but I digress). You can then monitor how your job is doing.

Here's an example:

dbms_application_info.set_session_longops
  ( rindex => g_index,
    slno => g_slno,
    op_name => 'GETTING MEMBER DATA',
    sofar => 0,
    totalwork => l_table.COUNT + 1,
    target_desc => 'GETTING MEMBER DATA' );

g_index and g_slno are global variables in the package. l_table is a PL/SQL TABLE OF VARCHAR2.

Now you can monitor the progress of your job in v$session_longops!

Here's the query I use:

SELECT 
  username,
  sid,
  serial#,
  TO_CHAR( start_time, 'MM/DD/YYYY HH24:MI:SS' ) start_ti,
  time_remaining rem,
  elapsed_seconds ela,
  ROUND( ( sofar / REPLACE( totalwork, 0, 1 ) ) * 100, 2 ) per,
  sofar,
  totalwork work,
  message,
  target_desc
FROM v$session_longops
WHERE start_time >= SYSDATE - 1 
ORDER BY start_time DESC

Now you too can sit for hours and watch your job move incrementally forward!

But seriously, it does help tremendously to know where a job is at. You can further use the SET_MODULE and SET_ACTION calls to see a specific point in the processing (inside a loop).

Here's the code in context:

PROCEDURE get_member_data
IS
  l_exists INTEGER;
  TYPE table_of_lobs IS TABLE OF VARCHAR2(3);
  l_table TABLE_OF_LOBS := TABLE_OF_LOBS( 'COM', 'ORG' );
  l_count INTEGER := 0;
BEGIN
  --check to see if there is enrollment data, if not, move on
  SELECT COUNT(*)
  INTO l_exists
  FROM members
  WHERE rownum < 2;
  
  IF l_exists = 1 THEN--data exists, truncate and reload
  
    g_index := dbms_application_info.set_session_longops_nohint;
      
    EXECUTE IMMEDIATE 'TRUNCATE TABLE member_stg';

    g_audit_key := p_audit.begin_load
                    ( p_targettable => 'MEMBER_STG',
                      p_loadsource => 'MEMBER_SOURCE',
                      p_loadstatus => 'PRE',
                      p_loadprogram => 'GET_MEMBER_DATA',
                      p_commenttext => 'INSERT' );

    dbms_application_info.set_session_longops
      ( rindex => g_index,
        slno => g_slno,
        op_name => 'GETTING MEMBERS',
        sofar => 0,
        totalwork => l_table.COUNT + 1,
        target_desc => 'GETTING MEMBERS' );

  FOR i IN 1..l_table.COUNT LOOP
    l_count := l_count + 1;

    INSERT INTO member_stg
    SELECT *
    FROM members;

    g_total_rows_affected := g_total_rows_affected + sql%rowcount;

    COMMIT;

    dbms_application_info.set_session_longops
      ( rindex => g_index,
        slno => g_slno,
        op_name => 'GETTING MEMBERS',
        sofar => l_count,
        totalwork => l_table.COUNT + 1,
        target_desc => 'GETTING MEMBERS' );
  
  END LOOP;

  p_audit.end_load
    ( p_auditkey => g_audit_key,
      p_loadstatus => 'SUC',
      p_rowsuccess => g_total_rows_affected );
  
  gather_table_stats
    ( p_tablename => 'MEMBER_STG',
      p_schemaname => 'MYHOME' );

  dbms_application_info.set_session_longops
    ( rindex => g_index,
      slno => g_slno,
      op_name => 'GETTING MEMBERS',
      sofar => l_count + 1,
      totalwork => l_table.COUNT + 1,
      target_desc => 'GETTING MEMBERS' );

  END IF;

EXCEPTION
  WHEN others THEN
    p_audit.failed_load
      ( p_auditkey => g_audit_key,
        p_comments => SQLCODE || ' ' || SQLERRM );
    RAISE;

END get_member_data;

Oracle Concepts: Data Integrity Rules

2009-06-29T01:17:00.004-04:00

I'm reading through the Concepts manual again as mentioned on last week.

I'm going to make a small effort to post some of the key concepts here over the next couple of weeks. If you've read through the Concepts Guide before, this can serve as a brief refresher. If not, good, you're exposed to something new.

Data Integrity Rules

This section describes the rules that can be applied to table columns to enforce different types of data integrity.

Null rule: A null rule is a rule defined on a single column that allows or disallows inserts or updates of rows containing a null (the absence of a value) in that column.

Unique column values: A unique value rule defined on a column (or set of columns) allows the insert or update of a row only if it contains a unique value in that column (or set of columns).

Primary key values: A primary key value rule defined on a key (a column or set of columns) specifies that each row in the table can be uniquely identified by the values in the key.

Referential integrity rules: A referential integrity rule is a rule defined on a key (a column or set of columns) in one table that guarantees that the values in that key match the values in a key in a related table (the referenced value).

Referential integrity also includes the rules that dictate what types of data manipulation are allowed on referenced values and how these actions affect dependent values. The rules associated with referential integrity are:

* Restrict: Disallows the update or deletion of referenced data.
* Set to null: When referenced data is updated or deleted, all associated dependent data is set to NULL.
* Set to default: When referenced data is updated or deleted, all associated dependent data is set to a default value.
* Cascade: When referenced data is updated, all associated dependent data is correspondingly updated. When a referenced row is deleted, all associated dependent rows are deleted.
* No action: Disallows the update or deletion of referenced data. This differs from RESTRICT in that it is checked at the end of the statement, or at the end of the transaction if the constraint is deferred. (Oracle Database uses No Action as its default action.)

Complex integrity checking: A user-defined rule for a column (or set of columns) that allows or disallows inserts, updates, or deletes of a row based on the value it contains for the column (or set of columns).

Reading on past the brief section to the Constraint States I found this nugget:

ENABLE NOVALIDATE means that the constraint is checked, but it does not have to be true for all rows. This allows existing rows to violate the constraint, while ensuring that all new or modified rows are valid.

This is a great tool for legacy systems. You have data in the column(s) that you can't really do anything with, but you want to insure that all future data that goes in that particular column(s) matches the parent key.

Of course the ideal is to somehow clean the data up, but you don't always have that option. This is a good first step towards to overall cleanup of your legacy system.

APEX: URL Syntax

2009-06-28T22:59:00.003-04:00

This is more for my own edification than anything. I always forget which place the ClearCache inhabits.

f?p=App:Page:Session:Request:Debug:ClearCache:itemNames:PrinterFriendly

With numbers so I don't have to count every time:

f?p=App1:Page2:Sess3:Requ4:Debug5:Cache6:items7:printer8

f?p=1:2:3:4:5:6:7:8

Link to the documentation is here.

Oracle Concepts

2009-06-26T21:07:00.002-04:00

Years and years of reading AskTom, if there's one thing that I really come away with, it's read. RTFM. Specifically though, he's telling everyone to read the Concepts Guide. Paraphrasing:

If you can get a good look at the Concepts Guide by sticking your head...

That's not the one.

If you read the Concepts Guide you'll have 10% of the knowledge that 90% don't have.

Nope.

If 90% of the people would read 10% of the Concepts Guide, they'd be able to program in C...?

Seriously, I need help.

If you do know the saying I'm talking about, please share. I can't seem to find it anywhere.

Ultimately it doesn't matter what was said. I'm reading the Concepts Guide again, browsing through it really, trying to find something new. Then I noticed this:

I know it's a "feature" and all, but does it have to be so prominently displayed? Couldn't it be buried in the Appendix of the PL/SQL Guide or something? This is especially important since Mr. Kyte is telling all the new people to read the Concepts Guide.

Perhaps the new people figure it out, the ones that have read the Concepts Guide anyway. I've run across too many "experienced" database developers that can't create apps without them. My guess is that they have barely hit any of the documentation, let alone the Concepts Guide.

A NULL Observation, III

2009-06-25T23:03:00.003-04:00

Part I here.

Part II here.

OK, we have a winner. Coskan Gundogar suggested in the comments, that using the MODIFY clause of the ALTER TABLE statement should work. Let's see:

desc t_null

Name                    Null?    Type
----------------------- -------- ----------------
COL1                             VARCHAR2(30)

CJUSTICE@TESTING>ALTER TABLE T_NULL DROP CONSTRAINT nn_col1_tn;

Table altered.

CJUSTICE@TESTING>ALTER TABLE t_null MODIFY ( col1 CONSTRAINT nn_col1_tn NOT NULL );

Table altered.

CJUSTICE@TESTING>@DESC T_NULL
Name                    Null?    Type
----------------------- -------- ----------------
COL1                    NOT NULL VARCHAR2(30)

Voila!

Much better. The question still remains as to what's going on in the background. Until now, I had never thought that white space mattered in anything Oracle. Very strange.

Maybe I can get Miladin to dig through the internals and see what's really going on.

A NULL Observation, II

2009-06-25T00:06:00.006-04:00

Shoot me, I couldn't let this go. Plus, I needed a reason to test drive my new sandbox.

The part about the space had me a little perplexed:

CJUSTICE@TESTING>SELECT
  2    table_name,
  3    constraint_name,
  4    constraint_type,
  5    search_condition,
  6    status
  7  FROM user_constraints
  8  ORDER BY table_name;

TABLE_NAME           CONSTRAINT_NAME      C SEARCH_CONDITION     STATUS
-------------------- -------------------- - -------------------- --------
T_NOT_NULL           NN_COL1_TNN          C "COL1" IS NOT NULL   ENABLED
T_NOT_NULL_CHECK     NN_COL1_TNNC         C  "COL1" IS NOT NULL  ENABLED
T_NULL               NN_COL1_TN           C  col1 IS NOT NULL    ENABLED

Here's the DDL that created that constraint:

ALTER TABLE t_not_null_check DROP CONSTRAINT nn_col1_tnnc;

ALTER TABLE t_not_null_check
  ADD CONSTRAINT nn_col1_tnnc
  CHECK ( "COL1" IS NOT NULL );

Note the white space there. I like putting a space after a parenthesis as I believe it makes it slightly easier to read.

With that in mind, watch this:

ALTER TABLE t_not_null_check DROP CONSTRAINT nn_col1_tnnc;

ALTER TABLE t_not_null_check
  ADD CONSTRAINT nn_col1_tnnc
  CHECK ("COL1" IS NOT NULL);

Note that I removed the space between the "(" and the """. Here's what it looks like:

SELECT 
  table_name, 
  constraint_name, 
  constraint_type, 
  search_condition
FROM user_constraints
ORDER BY table_name;

TABLE_NAME       CONSTRAINT_N C SEARCH_CONDITION
---------------- ------------ - ------------------------------
T_NOT_NULL       NN_COL1_TNN  C "COL1" IS NOT NULL
T_NOT_NULL_CHECK NN_COL1_TNNC C "COL1" IS NOT NULL
T_NULL           NN_COL1_TN   C "COL1" IS NOT NULL

CJUSTICE@TESTING>desc t_not_null_check
 Name    Null?   Type
 ----------------------- -------- ----------------
 COL1      VARCHAR2(30)

The leading space is gone now. Null? still shows up as NULL. I would assume that most GUI apps get their data from USER/ALL/DBA_TAB_COLUMNS, so let's take a look:

SELECT 
  table_name, 
  column_name, 
  nullable
FROM user_tab_columns
ORDER BY table_name;

TABLE_NAME       COLUMN_NAME                    N
---------------- ------------------------------ -
T_NOT_NULL       COL1                           N
T_NOT_NULL_CHECK COL1                           Y
T_NULL           COL1                           Y

How about USER_TAB_COLS?

SELECT 
  table_name, 
  column_name, 
  nullable
FROM user_tab_cols
ORDER BY table_name;

TABLE_NAME       COLUMN_NAME                    N
---------------- ------------------------------ -
T_NOT_NULL       COL1                           N
T_NOT_NULL_CHECK COL1                           Y
T_NULL           COL1                           Y

Nothing to see there.

So, there is a difference between defining a NOT NULL constraint using either the NOT NULL or CHECK syntax. In USER_CONSTRAINTS, there are 4 distinct constraint types:
1.  P = PRIMARY
2.  U = UNIQUE
3.  R = REFERENCE or FOREIGN KEY
4.  C = CHECK

What's the lesson here? Well, if doing analysis, you can't just depend on using the DESCRIBE command from SQL*PLus to see what is required and what is not. Of course you can't depend on that for everything. Interesting "problem" none-the-less.

Update
Part III (the final solution) is here.

Kate: The TV Star

2009-06-24T21:48:00.002-04:00

A couple of weeks ago a colleague of mine said she was enrolling her girls in the school across the street from work.

School? What school?

It's the Blossom Montessori School for the Deaf.

Your girls aren't deaf.

No they aren't, but they've opened up the summer program to all children.

Interesting. All I could think about was I might get to drive Kate to school. WIN! My friend later brought over some paperwork and I passed that on to Kris.

The next day, Kris called and was thoroughly impressed. They knew what apraxia was, unlike her local public school.

Kate has been in the ELP program for about a year and a half. ELP stands for Early Learning Program. It's designed for kids who, for one reason or another, have fallen behind. I think it falls between regular classes and Special Education (think Down's Syndrome).

Anyway, they recently provided us with her year-end report. It's not required though because the end of the year is really December. On the report, they crossed out (literally) the December date and hand-wrote the current date (June something). On top of that injustice (pun intended), they stated that Kate had made no progress since the last review.

Seriously, why would you do that? Why would you even entertain the idea of making @ktjustice mad? Mad is an understatement actually.

Needless to say, we signed Kate up for Blossom.

Kate signs, a little bit. Please, More, All Done. I don't think we pursued that hard because of her hands. She's been in school for 2 weeks now and seems to enjoy it. I think she signed Daddy this evening (poor Mommy).

Today they took a field trip to the Clearwater Aquarium and lo and behold, a news crew was there!

(I was having problems getting this to display, so just click-through if it doesn't show up).

Kate shows up at 1:02...with her back to everone else...standing, while everyone else is sitting.

Watch this at Tampa Bay Online

A NULL Observation

2009-06-24T17:24:00.006-04:00

As I've been doing a lot of analysis lately, I've found a slightly annoying "problem."

I typically use JDev or SQLDev to see details on a table, more than just a DESCRIBE from SQL*Plus can give me anyway.

This "problem" relates to how NULL columns are displayed, both via DESCRIBE and the previously mentioned tools.

First up, my favorite, the table definition with inline constraints.

CREATE TABLE t_not_null
(
  col1 VARCHAR2(30)
    CONSTRAINT nn_col1_tnn NOT NULL
);

Let's see how it looks in a SQL Worksheet (Columns):

Constraints:

SQL*Plus (DESCRIBE):

CJUSTICE@TESTING>desc t_not_null
 Name                    Null?    Type
 ----------------------- -------- ----------------
 COL1                    NOT NULL VARCHAR2(30)

Now I'll create a different table, this time instead of using NOT NULL, I'll use the CHECK syntax.

CREATE TABLE t_not_null_check
(
  col1 VARCHAR2(30)
    CONSTRAINT nn_col1_tnnc CHECK ( col1 IS NOT NULL )
);

Columns:

Nullable = Yes? Hmmm...

Constraints:

DESCRIBE

CJUSTICE@TESTING>desc t_not_null_check
 Name                    Null?    Type
 ----------------------- -------- ----------------
 COL1                             VARCHAR2(30)

Interesting, it doesn't show up in the "Null?" column like it did with the NOT NULL syntax used above.

Now I'll create a table with no inline constraint defined.

CREATE TABLE t_null
(
  col1 VARCHAR2(30)
);

I know (famous last words) I can't use the NOT NULL syntax in an out-of-line constraint:

ALTER TABLE t_null
  ADD CONSTRAINT nn_col1_tn NOT NULL;
  ADD CONSTRAINT nn_col1_tn NOT NULL
                            *
ERROR at line 2:
ORA-00904: : invalid identifier

So I use the CHECK syntax:

ALTER TABLE t_null
  ADD CONSTRAINT nn_col1_tn
  CHECK ( col1 IS NOT NULL );

Table altered.

Columns:

Constraints:

DESCRIBE

CJUSTICE@TESTING>desc t_null
 Name                    Null?    Type
 ----------------------- -------- ----------------
 COL1                             VARCHAR2(30)

Very odd.

Let's look at the dictionary:

SELECT
  table_name,
  constraint_name,
  constraint_type,
  search_condition,
  status
FROM user_constraints
ORDER BY table_name;

TABLE_NAME           CONSTRAINT_NAME      C SEARCH_CONDITION     STATUS
-------------------- -------------------- - -------------------- --------
T_NOT_NULL           NN_COL1_TNN          C "COL1" IS NOT NULL   ENABLED
T_NOT_NULL_CHECK     NN_COL1_TNNC         C  col1 IS NOT NULL    ENABLED
T_NULL               NN_COL1_TN           C  col1 IS NOT NULL    ENABLED

Interesting, I wonder if the fact that it's not UPPERcased and in quotes?

CJUSTICE@TESTING>DROP TABLE t_not_null_check;

Table dropped.

Elapsed: 00:00:00.04
CJUSTICE@TESTING>CREATE TABLE t_not_null_check
  2  (
  3    col1 VARCHAR2(30)
  4      CONSTRAINT nn_col1_tnnc CHECK ( "COL1" IS NOT NULL )
  5  );

Table created.

DESCRIBE

CJUSTICE@TESTING>DESC T_NOT_NULL_CHECK
 Name                    Null?    Type
 ----------------------- -------- ----------------
 COL1                             VARCHAR2(30)

OK, Null? is still...NULL.

CJUSTICE@TESTING>SELECT
  2    table_name,
  3    constraint_name,
  4    constraint_type,
  5    search_condition,
  6    status
  7  FROM user_constraints
  8  ORDER BY table_name;

TABLE_NAME           CONSTRAINT_NAME      C SEARCH_CONDITION     STATUS
-------------------- -------------------- - -------------------- --------
T_NOT_NULL           NN_COL1_TNN          C "COL1" IS NOT NULL   ENABLED
T_NOT_NULL_CHECK     NN_COL1_TNNC         C  "COL1" IS NOT NULL  ENABLED
T_NULL               NN_COL1_TN           C  col1 IS NOT NULL    ENABLED

Why is there that extra space in front of "COL1" IS NOT NULL?

I ended my investigation there as it seems to be a waste of time. I just found it interesting that there was a difference in how you defined a NOT NULL constraint and whether or not it would show up in the DESCRIBE command. Anyone out there notice this before?

Read Part II here.

Part III (the final solution) is here.

Virtual Box, OEL, 11g, ApEx

2009-06-23T22:37:00.003-04:00

Finally.

After many months and many attempts, I have managed to install a database on a non-windows platform. I think I deserve a little credit. ;)

In January, I began installing Ubuntu Intrepid Ibex on all of my home computers. What this meant for me is that I was often without one because I screwed something up. I even got so far as to install Oracle XE on Ubuntu which took 2 blog posts.

Just before COLLABORATE 09, I installed Jaunty Jackalope on my laptop. Then I got the crazy idea to install Oracle on Ubuntu. I ended up having to wipe the hard drive and start fresh. I have no doubt that it (Oracle) can be installed on Ubuntu, it just takes someone much more experienced than I.

Armed with a fresh install, I was going the virtual route. Inspired, I believe, by this comment (Aman Sharma).
1.  Install ~~Sun's~~ Oracle's VirtualBox
2.  Download the latest version of Oracle Enterprise Linux (v5)

I think that's the point I realized my first installation of Jaunty was hosed, and by hosed I mean unrecoverable by me. I couldn't see the 2nd partition, the one with all the room.

3.  Create OEL virtual machine
4.  Follow this guide to get it ready to install the Oracle database (seriously? Why does OEL not come ready for the database server?)

4a.    There's no mention of opening up the firewall in that article. It's probably obvious to most...
5.  Connect to Oracle from the host machine
6.  ApEx? Which version am I running? 3.0? That won't do. Download ApEx 3.2
7.  Install ApEx
8.  Configure Embedded PL/SQL Gateway for port 80
8a.    Ports below 1024 are reserved for privileged processes...Configure Embedded PL/SQL Gateway for port 8080
9.  Connect from host machine.

9a. Login to ApEx.

10. WIN!

PL/SQL: Split Key-Value Pairs

2009-06-23T17:00:00.002-04:00

In a continuation from a previous post, PL/SQL: Split URL Parameters, I give you the key/value splitting! (cheese, I know...I'm bored).

In that previous post, I was taking a URL string and splitting it based on the delimiter, which is typically the ampersand &. In ApEx it is the colon :. I'd take a wild guess and say there are others, but I'm not going to look them up. An example string looks like this:

param1=value1¶m2=value2

Etc. Etc. Etc.

The output of that split looks like this:

param1=value1
param2=value2

Now you need that string parsed. Instead of just a string being returned however, you'd like to know the name of the parameter as well, thus key/value. Desired format looks like this:

KEY         VALUE
----------- -----------
param1      value1
param2      value2

First up, I'll create the SQL objects (user defined types):

CREATE TYPE r_key_value AS OBJECT
(
  key VARCHAR2(100),
  value VARCHAR2(100)
)
/

CREATE TYPE t_key_value AS TABLE OF r_key_value
/

I could easily do this using PL/SQL tables, but I have future plans.

DECLARE
  l_string VARCHAR2(1000) := 'param1=value1¶m2=value2';
  l_table T_KEY_VALUE := T_KEY_VALUE();
  l_delimiter VARCHAR2(5) := '&';
  l_delimiter_length INTEGER := LENGTH( l_delimiter );
  l_key VARCHAR2(100);
  l_value VARCHAR2(100);
  l_keyvalue VARCHAR2(200);
  l_counter INTEGER := 0;
  l_new VARCHAR2(4000);
  l_start INTEGER := 1;
  l_end INTEGER := 0;

This will eventually turn into a Function, but I'm just doing some smoke testing now to get the logic right. More extensive testing will be performed in the future with SQLUnit.

BEGIN
  IF SUBSTR( l_string, -1, 1 ) <> l_delimiter THEN
    l_string := l_string || l_delimiter;
  END IF;

  l_new := l_string;
  p( L_NEW );

I still haven't figured out the best way to grab the last token without appending the delimiter onto the end. It feels like a kludge, but for now, it works.

  LOOP
    l_counter := l_counter + 1;
    l_end := INSTR( l_new, l_delimiter, 1 );
    l_keyvalue := SUBSTR( l_new, 1, l_end - 1 );
    EXIT WHEN l_keyvalue IS NULL;
    
    l_table.EXTEND(1);
    l_key := SUBSTR( l_keyvalue, 1, INSTR( l_keyvalue, '=' ) - 1 );
    l_value := SUBSTR( l_keyvalue, INSTR( l_keyvalue, '=' ) + 1 );
    l_table(l_counter) := R_KEY_VALUE( l_key, l_value );
    l_start := l_start + ( l_end + ( l_delimiter_length - 1 ) );
    l_new := SUBSTR( l_string, l_start );
  END LOOP;

Regular expressions would be the best fit here. Until I learn them (yes, I hear you, "Isn't this the perfect opportunity?"), I'm going to do it the hard way.

Finally, just printing out to the console so I can see the results.

  p( 'table counter:  ' || l_table.COUNT );
  FOR I IN 1..l_table.COUNT LOOP
    p( 'key:    ' || l_table(i).key );
    p( 'value:  ' || l_table(i).value );
    p( '' );
  END LOOP;

Run it and I get the following:

CJUSTICE@TESTING>@C:\TEMP\S
table counter:  2
key:    param1
value:  value1
key:    param2
value:  value2

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07

Now I just need to wrap this up into package form and I'm all done. That will be another post with the unit tests provided.

Update
So after rereading this post, I realized that I just confused it with the previous post on splitting URL strings. Probably the best solution would be to combine these 2 functions, or at least pipe out the key/value pairs from the main function (previous post). Did I catch a niner in there?

Data Grids for Database Developers

2009-06-16T20:34:00.003-04:00

Back at COLLABORATE, I met one Patrick Peralta, member of the Coherence team (reason #109 to go to conferences).

I spent about 2 hours with Mr. Peralta that day and had quite a bit of fun. I learned a couple of new tricks (JRockit for one) and got a slightly better understanding of Coherence.

I emphasized "slightly" because I still don't quite know how to use it. Caching data, I get that part. I guess I do understand it from a high-level perspective...it's the where part that I haven't quite grasped yet.

One thing I asked Mr. Peralta to do for me was to right it up, blog it. Show me (us) how and when to use it. Through twitter, he's shared with me some links. Specifically this one on how to define a Data Grid.

Finally (he did not mention that he likes to procrastinate), he's written something up, An Introduction to Data Grids for Database Developers.

Smarter Caching

An obvious (or maybe not so obvious depending on who you ask) first step in scaling a database application is to cache as much as you can. This is fairly easy to do if you have a single app server hitting a database. It becomes more interesting however as you add more app servers to the mix. For instance:

* Is it OK if the caches on your app servers are out of sync?
* What happens if one of the app servers wants to update an item in the cache?
* How do you minimize the number of database hits to refresh the cache?
* What if you don’t have enough memory on the app server to cache everything?

It's a very interesting technology. I would highly encourage you to click through and give it a go. Leave comments here or abroad.

Oracle's New INSERT Syntax

2009-06-16T17:00:00.005-04:00

Not really Oracle's...just mine.

I'm busy debugging

  VALUES
  *
ERROR at line 19:
ORA-00947: not enough values

I manually count the columns and they're the same. Run it again.

  VALUES
  *
ERROR at line 19:
ORA-00947: not enough values

I then cut and paste both "halves" of the INSERT statement into excel so I can get a row-by-row compare.

Everything looks good, go!

  VALUES
  *
ERROR at line 19:
ORA-00947: not enough values

WTF?

I still haven't figured it out, but that excel picture gave me an idea...

I tend to put one column on each line, for large tables, this takes up quite a bit of vertical space. I've seen others put columns (and values) on the same line. Just looks ugly to me. Here's what this table looks like:

INSERT INTO my_table
  ( id,
    create_date,
    update_date,
    col1,
    col2,
    col3,
    col4,
    col5,
    col6,
    col7,
    col8,
    col9,
    col10,
    col11,
    col12,
    col13,
    col14 ) 
VALUES 
    seq.nextval,
    SYSDATE,
    SYSDATE,
    'A',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING',
    'SOMETHING' );

Wouldn't it be cool if you could do something like this though?

INSERT INTO my_table
  ( id => seq.nexval,
    create_date => SYSDATE,
    update_date => SYSDATE, 
    col1 => 'A',
    col2 => 'SOMETHING',
    col3 => 'SOMETHING',
    col4 => 'SOMETHING',
    col5 => 'SOMETHING',
    col6 => 'SOMETHING',
    col7 => 'SOMETHING',
    col8 => 'SOMETHING',
    col9 => 'SOMETHING',
    col10 => 'SOMETHING',
    col11 => 'SOMETHING',
    col12 => 'SOMETHING',
    col13 => 'SOMETHING',
    col14 => 'SOMETHING' );

1. You'd save space.
2. It would be easier to read and
3. It would be easier to debug

Thoughts?

update:
I did solve my problem, I was missing the opening parenthesis in the VALUES clause.

PL/SQL: Split URL Parameters

2009-06-16T00:01:00.004-04:00

I've always had this phobia of anything to do with strings. Might be because I find it hard to think that way...and by "that way" I have no idea what I'm talking about.

Whenever I come across this problem, my first instinct is to go through every character and build a string, something like this:

DECLARE
  l_string VARCHAR2(100) := 'THIS_LITTLE_STRING';
  l_character VARCHAR2(1);
BEGIN
  FOR i IN 1..LENGTH( l_string ) LOOP
    l_character := l_character || SUBSTR( l_string, i, 1 );
  END LOOP;
END;
/

Of course that means I have to go through the loop at least 18 times. How could I loop less?

Well, I would need to know the delimiter first, in this case, it will be the underscore character:

DECLARE
  l_string VARCHAR2(100) := 'THIS_LITTLE_STRING';
  l_character VARCHAR2(1);
  l_delimiter VARCHAR2(1) := '_';
BEGIN
...

OK, that helps. So what can I do with that?

DECLARE
  l_string VARCHAR2(100) := 'THIS_LITTLE_STRING';
  l_new_string VARCHAR2(100) := l_string;
  l_token VARCHAR2(30);
  l_character VARCHAR2(1);
  l_delimiter VARCHAR2(1) := '_';
BEGIN
  LOOP
    EXIT WHEN l_new_string IS NULL;
    l_token := SUBSTR( l_new_string, 1, INSTR( l_new_string, '_', 1 ) );
...

OK, now I remember what I always get confused. There seems to be this endless stream of SUBSTR and INSTR involved. It's difficult to follow...for me anyway. So here's what I've come up with for now. I am aware of regular expressions and the DBMS_UTILITY.COMMA_TO_TABLE procedures, but for some reason, I like to reinvent the wheel. No, not really, I just find it challenging. More challenging would be for me to start using regular expressions...Anyway, here goes my attempt at splitting a URL string:

DECLARE
  TYPE my_table IS TABLE OF VARCHAR2(100);
  l_table MY_TABLE := MY_TABLE();
  l_string VARCHAR2(1000) := 'TESTING=YES&&BOLLOCKS=SOMETHING&&&testing=DF';
  l_keyvalue VARCHAR2(100);
  l_delimiter VARCHAR2(5) := '&&';
  l_delimiter_length INTEGER := LENGTH( l_delimiter );
  l_counter INTEGER := 0;
  l_new VARCHAR2(4000);
  l_start INTEGER := 1;
  l_end INTEGER := 0;
BEGIN
  IF SUBSTR( l_string, -1, 1 ) <> l_delimiter THEN
    l_string := l_string || l_delimiter;
  END IF;

  l_new := l_string;

  LOOP
    l_counter := l_counter + 1;
    l_end := INSTR( l_new, l_delimiter, 1 );
    l_keyvalue := SUBSTR( l_new, 1, l_end - 1 );
    EXIT WHEN l_keyvalue IS NULL;
    l_table.EXTEND(1);
    l_table(l_counter) := l_keyvalue;
    l_start := l_start + ( l_end + ( l_delimiter_length - 1 ) );
    l_new := SUBSTR( l_string, l_start );
  END LOOP;
  p( 'table counter:  ' || l_table.COUNT );
  FOR I IN 1..l_table.COUNT LOOP
    p( 'string:  ' || l_table(i) );
  END LOOP;
END;
/

CJUSTICE@TESTING>@T
table counter:  3
string:  TESTING=YES
string:  BOLLOCKS=SOMETHING
string:  &testing=DF

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

Voila! My own URL String Tokenizer.

Now I just have to write something that will split up the key/value pairs...

SQLUnit: Database Unit Testing

2009-06-11T22:24:00.003-04:00

What feels like a thousand years ago I used SQLUnit to do create and perform unit tests on database stored procedures. Lots of them. To the point where that's almost all I was doing. I both hated it and loved it. Hated it because it was boring, tedious, mind-numbing work. Loved it because it gave me confidence in the code I was writing and, once all the tedium was done, could be done quickly. A small change could be immediately tested to see it's effect.

Something else happened too, I began to write code thinking about the tests. Every single line of code was a potential test. If I do this, how will I test it? If I do that, how will I test it? Where do the tests end? You could theoretically test forever...and then I would inevitably fall down that rabbit hole.

Like I was saying, it changed the way I wrote code. Test Driven Development without writing the tests first (which I can hardly stand). That was a good thing because I began to anticipate certain errors. On an INSERT statement with a sequence generated Primary Key, should I trap the exception in code and give it my own error number? Or just let the ORA-00001 propagate up? I just let the Oracle error come up, no need to create my own for that. There's something seriously wrong if you get an error in that case and the whole world should know about it.

Speaking of exceptions, use them, throw them, don't trap them (mostly) and do something else. Log it and then RAISE it. I think it's good for every single developer to know when something is messed up. I've spent far too much time trying to debug something because all the errors are being trapped and processing just continues. I've been lucky on some occasions to have error logging, at least...

What is SQLUnit?

SQLUnit is a regression and unit testing harness for testing database stored procedures. An SQLUnit test suite would be written as an XML file. The SQLUnit harness, which is written in Java, uses the JUnit unit testing framework to convert the XML test specifications to JDBC calls and compare the results generated from the calls with the specified results.

Unfortunately it has not been worked on in almost 3 years, but I'd say it's in pretty good shape. There's support for Procedures, Functions, Ref Cursors and User Defined Types (though I haven't tried this out yet). The current version is 5.0. The last version I worked on consistently was 1.3 I believe. A colleague (who now works for Oracle) from my early days of SQLUnit testing even contributed some Oracle specific items.

I used it to build the database for CABEZE, my first, unsuccessful, try at my own business. It was nice because I was building it from scratch, so I could build all the test data along with it (no testing with production data...though there wasn't really much of that anyway), set up the database (create test data), run the tests and then tear it down back to it's original state (empty). Unfortunately the systems I've worked on since weren't empty and testing with production, or semi-production (cleansed) data was the only viable alternative.

Which brings me to now. I'm trying to reacquaint myself with the tool and writing test cases for various procedures. I ran into an issue create test cases because all of our credit card numbers were scrambled. Everything failed with an Invalid Card Number. Barnacles.

Why not create a routine that would generate "real" credit card numbers, more specifically, numbers of the appropriate length that had a check digit? So credit cards use the Luhn formula which is supposed to prevent certain transposition errors.

The Luhn algorithm will detect any single-digit error, as well as almost all transpositions of adjacent digits. It will not, however, detect transposition of the two-digit sequence 09 to 90 (or vice versa). It will detect 7 of the 10 possible twin errors (it will not detect 22 ↔ 55, 33 ↔ 66 or 44 ↔ 77).

With CABEZE, I had written my own PL/SQL card number generator (and validator), but I didn't blog at the time and seem to have lost that code. So I tried writing it again.

Since I'm using SQLUnit, what a great way to demonstrate the power of the tool. It's a somewhat complex (to me anyway) formula, so writing up tests for it should help me to figure it out. Here's the code to create a check digit based on the Luhn formula.

CREATE OR REPLACE
FUNCTION create_check_digit( p_card_number IN NUMBER ) RETURN NUMBER
IS
  TYPE t_digits IS TABLE OF INTEGER;
  l_table T_DIGITS := T_DIGITS();
  l_count INTEGER := 0;
  l_num INTEGER;
  l_digit INTEGER;
  l_odd INTEGER := 0;
  l_even INTEGER := 0;
  l_sum INTEGER := 0;
  l_check_digit INTEGER;
BEGIN
  IF p_card_number IS NULL THEN
    raise_application_error( -20001, 'you must provide a card number' );
  END IF;

  FOR i IN REVERSE 1..LENGTH( p_card_number ) LOOP
    l_count := l_count + 1;
    l_table.EXTEND(1);
    l_table( l_count ) := SUBSTR( p_card_number, i, 1 );
  END LOOP;

  FOR i IN 1..l_table.COUNT LOOP
    l_digit := l_table(i);
    IF MOD( i, 2 ) > 0 THEN
      l_num := l_digit * 2;
      IF l_num > 9 THEN
        FOR i IN 1..LENGTH( l_num ) LOOP
          l_odd := l_odd + SUBSTR( l_num, i, 1 );
        END LOOP;
      ELSE
        l_odd := l_num;
      END IF;
      p( 'odd:  ' || l_odd );
    ELSE
      l_even := l_digit;
    END IF;
    l_sum := l_sum + l_odd + l_even;
    p( 'l_sum:  ' || l_sum );
    l_odd := 0;
    l_even := 0;
  END LOOP;
  l_check_digit := ABS( ( CEIL( MOD( l_sum / 10, 10 ) ) * 10 ) - l_sum );
  p( 'check digit:  ' || l_check_digit );
  p( 'l_sum:  ' || l_sum );
  p( p_card_number || l_check_digit );
  RETURN l_check_digit;
END create_check_digit;
/

I have no doubt it could be done easier, specifically using regular expressions. This is just my first go at it...so don't give me a hard time...any other solutions are welcome in the comments. ;)

Here's the output of my final test run:

[sqlunit] *** Running SQLUnit file: p_cc.xml
[sqlunit] Getting connection(DEFAULT)
[sqlunit] Setting up test...
[sqlunit] Running test[1]: PASSING NULL (125ms)
[sqlunit] Running test[2]: VALID CARD NUMBER (4992739871) (15ms)
[sqlunit] Running test[3]: VALID CARD NUMBER (4012888888881881) (16ms)
[sqlunit] Running test[4]: VALID CARD NUMBER (4111111111111111) (0ms)
[sqlunit] Running test[5]: VALID CARD NUMBER (4222222222222) (15ms)
[sqlunit] Running test[6]: RANDOM (1) NUMBER (5) (0ms)
[sqlunit] Running test[7]: RANDOM (2) NUMBER (55) (0ms)
[sqlunit] Running test[8]: RANDOM (3) NUMBER (557) (16ms)
[sqlunit] Running test[9]: RANDOM (4) NUMBER (5579) (0ms)
[sqlunit] Running test[10]: RANDOM (5) NUMBER (65579) (0ms)
[sqlunit] Running test[11]: RANDOM (14) NUMBER (12345678965579) (16ms)
[sqlunit] Running test[12]: RANDOM NUMBER (5498975) (0ms)
[sqlunit] Tearing down test...

I was able to run a variety of different tests in seconds. A previous test run looked like this:

[sqlunit] *** Running SQLUnit file: p_cc.xml
[sqlunit] Getting connection(DEFAULT)
[sqlunit] Setting up test...
[sqlunit] Running test[1]: PASSING NULL (109ms)
[sqlunit] Running test[2]: VALID CARD NUMBER (4992739871) (109ms)
[sqlunit] Assertion "outparams-equal" failed (6(NUMERIC) != 1(NUMERIC) at outparams[0])
[sqlunit] *** expected:
[sqlunit] 
[sqlunit]   6
[sqlunit] 
[sqlunit] *** but got:
[sqlunit] 
[sqlunit]   1
[sqlunit] 
[sqlunit]
[sqlunit] Running test[3]: VALID CARD NUMBER (4012888888881881) (0ms)
[sqlunit] Running test[4]: VALID CARD NUMBER (4111111111111111) (0ms)
[sqlunit] Running test[5]: VALID CARD NUMBER (4222222222222) (0ms)
[sqlunit] Assertion "outparams-equal" failed (2(NUMERIC) != 0(NUMERIC) at outparams[0])
[sqlunit] *** expected:
[sqlunit] 
[sqlunit]   2
[sqlunit] 
[sqlunit] *** but got:
[sqlunit] 
[sqlunit]   0
[sqlunit] 
[sqlunit]
[sqlunit] Running test[6]: RANDOM NUMBER (5498975) (0ms)
[sqlunit] Tearing down test...
[sqlunit] sqlunit-ant: SQLUnit Tests Failed: In file: p_cc.xml, tests: 6, failures: 2, errors = 0
[sqlunit] SQLUnit Tests Failed: In file: p_cc.xml, tests: 6, failures: 2, errors = 0

I could then identify the problem, fix it, and run the tests within seconds. How cool is that?

That particular run helped me realize that if I passed in a string of numbers that was even, it would give me the wrong result. If you look at Visa for instance, it's typically 16 digits long with the final digit being the check digit. You start at the rightmost digit before the check digit and go backwards. That was the impetus behind putting the digits into the PL/SQL TABLE OF INTEGERS up above.

I'll post more on creating validly formatted credit card numbers later, I just wanted to use it as an example for SQLUnit.

Try it. Use it. Let me know how it goes. Maybe we can get some Java guy to get it started up again.

UPDATEs in OLTP: A Design Flaw?

2009-06-08T00:25:00.003-04:00

This one has been on my mind for the past year or so...

Should you do UPDATEs in an OLTP environment?

The answer is maybe, or it depends, of course.

When I ask this question I ask it in relation to INSERTs. There is a difference between creating a record and updating a record. I think it comes down to a design decision.

For instance, I once used a table to track sessions (web sessions, using Application Context in the database to "reconnect"), it was defined something like the following:

An Acceptable Time to use UPDATE
A session defaulted was created with a 30 minute window. Each page the user would hit would update the END_TIME to SYSDATE + 30 Minutes. If they had no activity for 30 minutes, the END_TIME would be less then the current time (SYSDATE) and they would be logged out. This table did exactly what I needed, UPDATEs worked perfectly here.

What if I wanted to track page hits though? Could I do that with the current table? I could possibly add PAGE_ID or something I suppose, but then I would have to write this "complicated" query to find the first START_TIME and then compare that with the last END_TIME. Kind of changes the meaning of the table right?

An Unacceptable Time to use UPDATE
You have a table of scheduled transactions or some sort of recurring billing.

You use this table by having a job that looks at the DATE_TO_POST, takes those and attempts to post them. If there is a problem, you update the DATE_TO_POST column to the next date/time (based on rules somewhere) and you increment the TIMES_POSTED counter. That doesn't sound so bad right? If you don't have any processing or billing failures, it's not. But if you do, you lose some valuable data, in my opinion.

First, get rid of the TIMES_POSTED column. You don't need it. Create a record for every single transaction you have posted. This obviously changes the meaning of the table and will force you to change your code.

Here's why it's good though (to remove the UPDATE): You now have a single record for every attempt. Your reporting off of this becomes much easier and is performed with SQL. With the UPDATE, you have to maintain some PL/SQL code ( TIMES_POSTED + 1). With the INSERT, you don't perform a calculation at all.

A slightly different example and one that may be more familiar to everyone:

This kind of structure is more suited for a data warehouse. The columns have been pivoted to make reporting easier. In an ideal environment, this wouldn't exist on the OLTP server. However we don't all work in ideal environments and this type of structure is often employed. I pretty much hate it (in OLTP, not DW). The volume and complexity of code needed to maintain this type of table is large. There are all sorts of computations that must be performed and then someone, like me, has to come in and maintain it. I always vote for scrapping it in favor of the following type structure(s):

Now you have INSERT statements as opposed to one or more UPDATE statements. Reporting tools can then handle the pivoting or whatever else you want to do to the data.

UPDATEs in an OLTP are not a flaw, but when about to write one, think of the implications. Is that what you really want to do? Wouldn't you rather just do an INSERT and be done with it?

Am I a New Media Douchebag?

2009-06-03T21:16:00.003-04:00

I'll continue after you've viewed the video...

Someone called me a New Media Douchebag today...jokingly of course. (I hope...)

I posted the video to Twitter and Kris Rice didn't think I complained enough to meet the 3rd requirement. I suggested that I'd be the Susy Sunshine of the New Media Douchebags...or something like that.

I've definitely embraced the idea of the New Media or Social Media or whatever you want to call it. It seems kind of natural to me. I've always been the social butterfly at work, the "nice" IT guy and I love to talk. My old manager struggled with whether this was good or bad. He saw the good of building solid relationships with business types but also the amount of time. I'm sure he wasn't the first manager to be concerned about my talking.

I've become an advocate of the New Media as well.

I think everyone and their mother should blog.

I think everyone should use Twitter.

I created a group on LinkedIn for ORACLENERD(s).

I scored a pass to COLLABORATE 09.

I received detailed instructions on migrating my rpd (OBIEE) file to production from Christian Berg.

I'm sure there are others, but I can't think of them right now.

This write about this crap all the time now.

I've even discussed doing some sort of presentation on the value of New/Social media.

Am I a New Media Douchebag? Should I relinquish ORACLENERD to someone more deserving? Tell me what you think. Some guy at work said I come off as "Extreme" (I think he meant as a future Exadata team member) in my database ramblings...so I can take it.