If there is one thing that drives computer programmers crazy it’s ineffective product documentation and examples that are either poor or simply don’t work. My latest beef is with Oracle 9i database documentation.
The first problem was last week and to be honest I don’t remember the exact details. When the weekend comes I purge by brain of everything so I restart fresh as a daisy on Monday. I’m fairly certain it had something to do with index only tables and the example copied verbatim from the book did not work. Using deductive reasoning I figured out what I was doing wrong, tried something and fixed the problem. The other problem dealt with bind variables on EXECUTE IMMEDIATE statements in PL/SQL code.
I have two functions that will select from different tables depending upon what fiscal year is requested. Don’t ask, it’s an ERP; we had no input on table design. It was simple SQL so nothing extraordinary was required. EXECUTE IMMEDIATE seemed like the logical choice. Using bind variables instead of text substitution in the query string is supposed to be a better performer (one copy in the SGA, techno-babble, yada yada). I modeled my SQL after the most realistic example in the book. When I executed I got a bind variable not defined error.
I tried replacing “:1” with the bind variable names like “:i_lo_id”; same problem. I knew they worked because a function above it was working perfectly. Maybe it was because I was using more than one? I looked at the Oracle example again after giving up on my code. All of their bind variables were numeric. There were no examples for VARCHAR2 or DATE types. I wish my brain would have regressed back to my embedded SQL in COBOL days, then I would have realized you do not need quotes on a character field when binding it to an elementary item; simply use something like :WS-CHAR-VALUE. My mind locked into the examples in the manual and it took the weekend (where I forget everything I did the week before) to reset my brain and find an excerpt from an O’Reilly book that pointed out type conversions on bound variables.
Long story short: I wasted a good couple of hours on my virgin experience with bind variables in EXECUTE IMMEDIATE PL/SQL statements. All it would have taken was a good example showing the most common data types: VARCHAR2, NUMBER and DATE. Thank goodness for the Internet and others who have documented things better.
I figure I’ve seen crummy, unrealistic examples in over twelve computer languages and half dozen APIs. I’ve had friends who suffered long hours with Borland, Microsoft and IBM support trying to figure out why something straight out of the book that should be working wasn’t. Microsoft’s Windows Scripting documentation is chock full of examples that simply don’t work.
It gets tiring after a while and it doesn’t get any easier.