Today's Oracle gem of the day was not learned from an O'Reilly book, an online forum, or even an Oracle reference manual. No, I'm pleased (and also dismayed) to announce that today's Oracle gem was gained through the loudest, but not always best, teacher: experience.
Today's Oracle gem is this: when you SELECT into a PL/SQL record, order matters.
I was under the misguided impression that when you SELECTed into a record, Oracle dutifully matched up each column to the eponymous field of your record and populated it. Boy was I wrong! I'm working on the largest SQL statement I've ever seen in my life, and unfortunately I am just now testing after getting most of it written. I got some gripe about converting characters to numbers.
Odd, I thought. I cross-referenced the types of every field in the record and the statement, starting with the main table involved; everything matched. I stripped out all the fields from associated tables, commented out half the structure of my record, and tested. No problem. Then I uncommmented the first field and join condition. Immediately everything blew up. "Ah!" I thought, "It's not the types of the fields, it's something stupid I did in the join condition!" I commented the field back out and tested with the join condition but pulling no fields from that table. Everything worked, proving in fact that the problem was the single field I was pulling out of that table.
Turns out my record definition and my SELECT statement have all the fields in a completely different order. The orderings are logical in each case, but will not work together. Even when I got the statement to run on just the fields from one table, I probably had countless mismatches doing who knows what to my data. I'm just glad I found it now.
Remember, folks, test early and often! Preferably earlier than me...
Meanwhile, I'm considering bidding on this. The interesting part is the A/V cables. I'm actually planning on getting an original system and trying my hand at hacking it to produce composite video. Probably be fun, but I'd probably rather get one premade.