Hi. My name is David. If you’ve heard of me, there’s a good chance it’s from my Oracle for Absolute Beginners series which I write for Simple Talk. (Either that, or you got here from googling the famous NFL sportsman who has the same name as me. Poor guy, I bet journalists get us mixed up and ask him Oracle questions after every touchdown! “You played well today … but can you explain Oracle’s implementation of JSON?”)
I have been mentoring some new developers recently, and it has made me realise that I need to revisit my series. Yes, it does a good job of teaching new Oracle developers the basics of the language, but is that enough? That’s like teaching someone the alphabet and expecting them to turn into Shakespeare!
If you are an absolute beginner, you do not just need to learn the what; you also need someone to teach you how to become a developer. What tools do you need? How do you use them? Also, when you are assigned a task, how do you go about attacking it?
Most importantly, what do you do when you run into your first hiccup? Because no matter how well you’ve learned your lessons, once you start writing, running, and maintaining code, you will run into problems; you will fail.
The boxer Mike Tyson once said that everyone has a plan … until they get punched in the face.
I need to train you on what to do when Oracle punches you in the face.
I spend more time looking at error messages than I do looking at my wife. Every developer does. I used to hate them; they used to scare me. I used to see them as a judgment on me, on my skills as a developer. I used to think that the fact that my code was erroring meant that I had failed, that I was inadequate. Trust me; you will feel that way too.
However, I don’t feel that way now, and you don’t have to either. That’s because I’ve learned the simple steps to follow when my Oracle code breaks down and begins to pelt me with errors.
It is time, dear absolute beginner, that you do, too.
Debugging
Switch on debugging; the messages you receive will walk you through your code. In other words, you’ll get the first debug message first, the second one second, and the third will show up third. Find the juncture where this deviates, and you’ve found the line of code that is erroring. Still not sure what I mean? Don’t worry too much about it now; I’ll give you an example a little later.
One thing that you should bear in mind is that debug messages contained within a loop will be printed once for each iteration of the loop. What that means is that, if your code goes around your loop ten times, your debug message will be output ten times.
On the other hand, messages within a conditional statement will only be output if the code wanders down that branch of the statement. Look at the following, for example:
IF 1 = 2 THEN
<output debug message>
ELSE
<output other debug message>
END IF;
The first debug message will never be output since 1 will never be equal to 2. (Despite whatever the old Spice Girls song said.)
If your code was not written with instrumentation already baked in, there’s no need to panic. (And in case you were wondering what instrumentation means, in this instance, it’s just a long-winded way of saying debugging.) As long as you’re working in a development or test environment, you can probably add some debugging now, even if only temporarily.
Do you know about dbms_output.put_line? No? It’s a godsend. It’s a standard procedure that enables you to send messages from within PL/SQL. It’s great for debugging.
Here’s how you’d use it. (This is that example that I promised you a few paragraphs ago.)
BEGIN
dbms_output.put_line('start of the procedure');
dbms_output.put_line('before insert statement');
INSERT INTO table…
dbms_output.put_line('after insert statement');
END;
When you run your procedure, your messages will be printed to the screen. If, for example, you receive the start of procedure message, and the before insert statement one, but not the after insert statement message, then you know that the error is in your insert statement.
It’s really that simple.
Obviously, there’s a lot more you can do with debugging. If your procedure accepts parameters, output their values in debug messages. Also, if you run a cursor or call a function to populate variables, you should output the new values of those variables in debug messages. So yes, there’s a lot you can do with debug messages (as a matter of fact, I’ve written a whole article on the subject), but at the very heart of it is a simple truth. When you find the spot where your debug messages either terminate abruptly or deviate from the expected path, then you have found the cause of the error.
Oh, one last thing. If you’re running your code using SQL Plus, and you want to use dbms_output.put_line, you’ll need to call the following statement before executing your code:
SET SERVEROUTPUT ON;
This command instructs SQL Plus to print all debug messages to the screen. Throughout your career you will forget SET SERVEROUT ON; a number of times (everyone does!). On those days, you’ll sit there for five minutes wondering why you’re not getting any debug messages, and then you’ll realise what you’ve done. You’ll feel like a fool. We’ve all been there.
If your dbms_output.put_line messages were meant to be temporary, do remember to remove them when you’re done.
Excellent debugging should take the guesswork out of error hunting. When I use dbms_output.put_line, I use it liberally – the way an 8-year-old puts ketchup on his French fries. Everywhere.
Exceptions
If your code is not working as expected, but you don’t have errors screaming at you, there’s a chance you have When Others exception-handling in place that is holding a pillow over your errors’ mouths and shutting them up. You do not need When Others exception handling, but if you must have it, then re-raise your errors. Alternatively, you can use our friend dbms_output.put_line and print out one of the following: SQLERRM or SQLCODE. SQLERRM will give you the error message; SQLCODE will strip out the verbiage and just give you the error number.
BEGIN
…
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('The error is '||SQLERRM);
dbms_output.put_line('The error code is '||SQLCODE);
END;
Take a break
If you still can’t figure out where your error is coming from, take a break. Come back to it in five minutes with fresh eyes. Go make a coffee. If you smoke, go and have a cigarette. I don’t smoke, so I hide in an empty meeting room and spend five minutes scrolling through Twitter. Now that I think of it, that’s probably worse for me than smoking!
Look at your code with fresh eyes
Several simple tricks will help you achieve this. The very best one is to speak to someone. I’ve found that often the simple act of explaining my problem to another developer is all it takes for the solution to come to me. Talking about a problem brings it into clearer focus.
There are other ways of doing this. Sometimes I skip the code and read the comments instead. Sometimes I get a notepad and write it all out as pseudocode.
I try to force myself to eliminate assumptions. It is always that line of code that you think will never fail that is the source of the error.
There is nothing new under the sun; no matter how obscure your problem is, someone else has encountered it before – and they’ve probably blogged about it too!
If you’re looking for quick answers, there are a number of reliable sites.
- Asktom.oracle.com is excellent. It’s run by a guy named Tom Kyte, and he’s been answering Oracle questions on his site for 20 years, if not longer. Sometimes he gets grumpy when he’s asked what he thinks is a silly question, but you don’t have to ask questions, you can just search the archives for the problems that match yours.
- Stack Overflow is this massive resource of questions and answers that cover every aspect of development. The quality of the answers can, sometimes, be variable.
- Oracle Community is a site where Oracle developers ask and answer questions. Some of the best Oracle developers in the world are active members of the community.
- Burleson Consulting is a weird-looking site that is styled like it just stepped out of the 1990s. However, it does often contain useful, clearly-presented information. Even if it looks like it’s out of the 1990s, its SEO must be cutting edge because it always comes near the top of every Google search.
Build your knowledge
Copying and pasting fixes from Stack Overflow might help you at the moment, but the only way to get better at problem-solving is to build your knowledge. There are several great resources for that: Simple Talk is excellent (and it’s not just because I’m on it!). Also, Google a guy named Steven Feuerstein. He’s one of the best PL/SQL writers out there.
Learn from your mistakes
This is often the hardest thing. The number of times I’ve thought “if only I’d put a bit more debugging in this package” or “why on earth didn’t I comment this code?” And then I’d go straight ahead and NOT put in additional debugging or commenting. Don’t be like me. Don’t be like me at all.
Conclusion
It doesn’t matter what platform you use when you code, errors will happen. They don’t mean you are a failure; you need to learn how to use the tools to troubleshoot, debug, and fix the problems. Here, I’ve tried to show you a few techniques that have helped me over the years.
The post Oracle for Absolute Beginners: Problem-Solving as a New Developer appeared first on Simple Talk.
from Simple Talk http://bit.ly/2GULKFu
via
No comments:
Post a Comment