{"id":84120,"date":"2019-04-30T16:18:05","date_gmt":"2019-04-30T16:18:05","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=84120"},"modified":"2021-07-14T13:06:51","modified_gmt":"2021-07-14T13:06:51","slug":"oracle-for-absolute-beginners-problem-solving-as-a-new-developer","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/oracle-for-absolute-beginners-problem-solving-as-a-new-developer\/","title":{"rendered":"Oracle for Absolute Beginners: Problem-Solving as a New Developer"},"content":{"rendered":"

Hi. \u00a0My name is David. If you\u2019ve heard of me, there\u2019s a good chance it\u2019s from my Oracle for Absolute Beginners series<\/a> which I write for Simple Talk. (Either that, or you got here from googling the famous NFL sportsman<\/a> who has the same name as me. \u00a0Poor guy, I bet journalists get us mixed up and ask him Oracle questions after every touchdown! \u201cYou played well today \u2026 but can you explain Oracle\u2019s implementation of JSON?\u201d)<\/p>\n

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? \u00a0That\u2019s like teaching someone the alphabet and expecting them to turn into Shakespeare!<\/p>\n

If you are an absolute beginner, you do not just need to learn the what;<\/em> you also need someone to teach you how<\/em> to become a developer. What tools do you need? \u00a0How do you use them? Also, when you are assigned a task, how do you go about attacking it?<\/p>\n

Most importantly, what do you do when you run into your first hiccup? Because no matter how well you\u2019ve learned your lessons, once you start writing, running, and maintaining code, you will run into problems; you will fail.<\/p>\n

The boxer Mike Tyson once said that everyone has a plan \u2026 until they get punched in the face.<\/p>\n

I need to train you on what to do when Oracle punches you in the face.<\/p>\n

I spend more time looking at error messages than I do looking at my wife. Every developer does. \u00a0I 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. \u00a0Trust me; you will feel that way too.<\/p>\n

However, I don\u2019t feel that way now, and you don\u2019t have to either. That\u2019s because I\u2019ve learned the simple steps to follow when my Oracle code breaks down and begins to pelt me with errors.<\/p>\n

It is time, dear absolute beginner, that you do, too.<\/p>\n

Debugging<\/h2>\n

Switch on debugging<\/a>; the messages you receive will walk you through your code. In other words, you\u2019ll 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\u2019ve found the line of code that is erroring. Still not sure what I mean? Don\u2019t worry too much about it now; I\u2019ll give you an example a little later.<\/p>\n

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.<\/p>\n

On the other hand, messages within a conditional statement will only be output if the code wanders down that branch of the statement. \u00a0Look at the following, for example:<\/p>\n

IF 1 = 2 THEN\r\n\u00a0\u00a0\u00a0<output debug message>\r\nELSE\r\n\u00a0\u00a0\u00a0\u00a0<output other debug message>\r\nEND IF;\r\n<\/pre>\n
The first debug message will never be output since 1 will never be equal to 2. (Despite whatever the old Spice Girls song<\/a> said.)<\/p>\n
If your code was not written with instrumentation<\/a> already baked in, there\u2019s no need to panic. (And in case you were wondering what instrumentation<\/em> means, in this instance, it\u2019s just a long-winded way of saying debugging<\/em>.) \u00a0As long as you\u2019re working in a development or test environment, you can probably add some debugging now, even if only temporarily.<\/p>\n
Do you know about dbms_output.put_line<\/code>? No? It\u2019s a godsend. It\u2019s a standard procedure that enables you to send messages from within PL\/SQL. It\u2019s great for debugging.<\/p>\n
Here\u2019s how you\u2019d use it. (This is that example that I promised you a few paragraphs ago.)<\/p>\n
BEGIN\r\n\u00a0\u00a0\u00a0dbms_output.put_line('start of the procedure');\r\n\u00a0\u00a0\u00a0dbms_output.put_line('before insert statement');\r\n\r\n\u00a0\u00a0\u00a0INSERT INTO table\u2026\r\n\r\n\u00a0\u00a0\u00a0dbms_output.put_line('after insert statement');\r\nEND;<\/pre>\n
 <\/p>\n
When you run your procedure, your messages will be printed to the screen. If, for example, you receive the start of procedure<\/em> message, and the before insert statement<\/em> one, but not the after insert statement<\/em> message, then you know that the error is in your insert statement.<\/p>\n
It\u2019s really that simple.<\/p>\n
Obviously, there\u2019s 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\u2019s a lot you can do with debug messages (as a matter of fact, I\u2019ve written a whole article on the subject<\/a>), 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.<\/p>\n
Oh, one last thing. If you\u2019re running your code using SQL Plus, and you want to use dbms_output.put_line<\/code>, you\u2019ll need to call the following statement before executing your code:<\/p>\n
 <\/p>\n
SET SERVEROUTPUT ON;<\/pre>\n
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\u2019ll sit there for five minutes wondering why you\u2019re not getting any debug messages, and then you\u2019ll realise what you\u2019ve done. You\u2019ll feel like a fool. We\u2019ve all been there.<\/p>\n
If your dbms_output.put_line<\/code> messages were meant to be temporary, do remember to remove them when you\u2019re done.<\/p>\n
Excellent debugging should take the guesswork out of error hunting. When I use dbms_output.put_line<\/code>, I use it liberally – the way an 8-year-old puts ketchup on his French fries. Everywhere.<\/p>\n
Exceptions<\/h2>\n
If your code is not working as expected, but you don\u2019t have errors screaming at you, there\u2019s a chance you have \u00a0When Others <\/em>\u00a0exception-handling in place that is holding a pillow over your errors\u2019 mouths and shutting them up. You do not need When Others<\/em> exception handling, but if you must have it, then re-raise your errors. \u00a0Alternatively, you can use our friend dbms_output.put_line<\/code> and print out one of the following: SQLERRM<\/code> or SQLCODE<\/code>. \u00a0SQLERRM<\/code> will give you the error message; SQLCODE<\/code> will strip out the verbiage and just give you the error number.<\/p>\n
 <\/p>\n
BEGIN \r\n\r\n\u00a0\u00a0\u2026\r\nEXCEPTION\r\n\u00a0\u00a0WHEN OTHERS THEN\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0dbms_output.put_line('The error is '||SQLERRM);\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0dbms_output.put_line('The error code is '||SQLCODE);\r\nEND;<\/pre>\n
Take a break<\/h2>\n
If you still can\u2019t 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. \u00a0If you smoke, go and have a cigarette. I don\u2019t smoke, so I hide in an empty meeting room and spend five minutes scrolling through Twitter. Now that I think of it, that\u2019s probably worse for me than smoking!<\/p>\n
Look at your code with fresh eyes<\/h2>\n
Several simple tricks will help you achieve this. The very best one is to speak to someone. I\u2019ve 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.<\/p>\n
There are other ways of doing this. \u00a0Sometimes I skip the code and read the comments<\/a> instead. \u00a0Sometimes I get a notepad and write it all out as pseudocode.<\/p>\n
I try to force myself to eliminate assumptions. \u00a0It is always that line of code that you think will never fail that is the source of the error.<\/p>\n
Google<\/h2>\n
There is nothing new under the sun; no matter how obscure your problem is, someone else has encountered it before – and they\u2019ve probably blogged about it too!<\/p>\n
\nIf you\u2019re looking for quick answers, there are a number of reliable sites.<\/p>\n