Oracle PL/SQL Language Pocket Reference- P26: This pocket guide features quick-reference information to help you use Oracle's PL/SQL language. It includes coverage of PL/SQL features in the newest version of Oracle, Oracle8i. It is a companion to Steven Feuerstein and Bill Pribyl's bestselling Oracle PL/SQL Programming. Updated for Oracle8, that large volume (nearly 1,000 pages) fills a huge gap in the Oracle market, providing developers with a single, comprehensive guide to building applications with PL/SQL and building them the right way. . | value as shown below new_rate mortgage_rate down_payment prime_rate mortgage_type The mortgage_rate function was unfortunately setting new_rate to NULL. After examining the function it was clear to me that the only way it could return a NULL was if one of the inputs was NULL. I then used this information as follows 1. I had just examined the global variable holding the mortgage type. That global value was transferred to the mortgage_type variable in an earlier program and passed to my current module so I knew that it was OK. 2. I performed a walk-through of the code and could not identify how any of the other two variables could be NULL. 3. So I inserted a trace statement before and after the call to mortgage_rate. My code now looked like this Inputs TO_CHAR down_payment - TO_CHAR prime_rate new_rate mortgage_rate down_payment prime_rate bank mortgage_type Rate NVL TO_CHAR new_rate Missing 4. I ran the program and no matter what data I entered on the screen my trace statements remained the same Inputs Rate Missing I wracked my obviously overwrought brain what could cause a stored function to return a NULL value I looked at the source code for the function again. There wasn t much to it. Just division and multiplication. How could it return a NULL without a NULL input After two hours of this nonsense I finally said to myself Well you know that you really haven t verified the value of the mortgage_type variable. I knew that it was OK but hey it wouldn t hurt to check -- and if I didn t solve this one soon I would have to actually ask for help. So I modified my trace statement and sure enough the mortgage type was NULL. Turns out that while the global variable held the proper value the previous program did not pass it to the local variable properly. My assumption did me in. Please purchase PDF Split-Merge on to remove this watermark. One of the first assumptions you will make in your testing is that .
