OWB 11gR2 – Errors and Line Numbers
By David Allan-Oracle on Sep 23, 2010
How do you find errors in mapping designs when you can the PLS-0021 style errors deploying code? Here's some hints and tips that make life easier. One of the most common errors I've seen is finding the needle in the haystack when there is a typo in an expression that is entered and hasn't been validated/verified. This appends when for example you forget a quote, type a wrong name for a function, forget a brace .... all the usual stuff we forget to do when we design ... anything in fact.
The typical kind of error you will see is something like this when you use a function that doesn't exist.
This example uses a mapping input parameter accessor and the wrong name is used. Now there are a few clues here, one is the PACKAGE BODY, next the line number and finally the identifier GET_FILENAMES. Let's see what we can investigate...
Just above the errors are the generated scripts, if you view the generated package by double-clicking, you can find the line number in question.
The code opens in the new 11gR2 code viewer, by default it opens on Source, but note there is also a Spec and Body tab. Remember PACKAGE BODY was where the error was, so click on Body.
Then we can use the all to familiar control-G style operation to go-to the line number in question, line 548 in problem above, this takes us exactly to the line in error. This let's us see the expression in the context of the generated code.
I was sure the parameter was named FILENAMES, so let me search for at least get_filena.
I find that the accessor is named GET_FILENAME, I made a mistake and used GET_FILENAMES. Changing the expression in my map and redeploying resolves my issue.
The changes in 11gR2 make this kind of cycle much simpler, the fact that the Spec and Body are split and viewable with line numbers and have easy to use search capabilities, makes life simpler than using the data dictionary views for checking the source code which could be done like this;
select text from user_source where name='DYNAMIC_FILE_TGT' and type = 'PACKAGE BODY' and line > 545 and line < 552;
"FFFF_Fh" := UTL_FILE.FOPEN(
So accessing the body and searching is a nice capability that makes it easier for finding those little issues.