Tuesday, March 22, 2011

Expedient XU2/XU5 Script Development

Well, you have taken the Noetix Certification Course. You want to develop xu2/xu5 scripts, but the method to uncover syntactical/table constraint generated errors is very inefficient.

Specifically, if one runs stage 2 through 4 of the Noetix View Administrator, it will take greater than 20 minutes to run a re-generation against an Oracle Applications ERP environment.

Like all programming, syntactical errors are the lowest level of errors. One can have no syntactical errors, but still create some really bad design or logical errors that are not caught.

How can I speed-up checking for syntactical/table constraint generated errors (not logical errors)? Here are three methods I have used to help me uncover errors quickly.

1. Run the script (excluding the commit) against the noetix_sys account using SQL Plus (or your favorite querying tool). After the script has completed, look for any errors being thrown. In any event, perform a “rollback” when done. This works well for both xu2 and xu5 scripts.

2. Run stages 2 to 4 in the NVA (specifically stop at the point where there is a prompt for the APPS login information during stage 4). If an error has been thrown with the xu2 script, opt to not enter the APPS password and select "Cancel". This method does not work very well for xu5 scripts (I would recommend method 1 above for xu5).

The key here is to check the corresponding generated spool file(s) for any Oracle errors being thrown. I watch the SQL Loader statistics and I know the xu2 scripts are ran immediately afterwards. I then look for the spool file(s). Within about 5 minutes, I know if my script has syntactical/table constraint errors.

Again, to do this properly, I will not enter the stage 4 prompt for the APPS password until I have performed a check of the spool file(s).

3. Identical to approach 2 (which is usually best), but use Windows command mode to search for errors instead of manually opening spool files. Typically, one is only working on one script at a time. Suppose you are working on multiple scripts at once (probably not advisable). Use a search string command like this within the base directory:

findstr /s “ORA-“ *.lst>error_check.txt

/* I am using Microsoft’s Windows Server 2003 R2 */

Translation of this search command: find a matching string of the form “ORA-“ in any spool file in this directory and redirect the output to the file, “error_check.txt”

I usually use method two to uncover syntactical and constraint related errors.

No comments:

Post a Comment