I have a multi-part design question regarding description generation functions in the PIM Data Hub.
1. We have approximately 200 Item Catalog Categories, each with a unique description concatenation rule. Although different attributes are passed in for each rule, they generally follow the same pattern of comma delimiting aside from a "constant" value that prefixes each description based on category (ie. For a Motherboard item, the description rule would be "MOTHERBOARD,<Attribute1>,<Attribute2>,<Attribute3>")
If would be nice if a single rule could be re-used where the "constant" value would just be determined based on the category of the item being fed into it.
With that being said, is there anyway to capture the context of the item for the description being generated?
2. In terms of the question above, are there any advantages to using Java functions vs. PL/SQL functions? Both appear to act as "stand-alone" functions that cannot capture the context of the item being generated.
3. Finally, if each category requires that its own function be created for description generation... it would be nice to be able to leverage off of these to perform certain attribute validations. Both PL/SQL and Java functions provide a means of passing back an Error Array from the function, but both appear to have issues:
PL/SQL: You can successfully pass back errors using the USER_FUNC_ERROR_ARRAY datatype. However, there doesn't appear to be a way to "halt" the DML operation taking place - so these "errors" returned to the UI are really more like "notifications" rather than errors (ie. if you update an item that's description rule returns an error, the update transaction will complete and be committed and the errors will display on the screen after-the-fact).
Java: It appears that the ArrayList parameter that Oracle passes to your function is a null value, so you cannot populate the list (NullPointerException). And if you initialize the ArrayList in your function, you lose reference to the object and the values cannot be passed back. I have logged a TAR for this issue. Example for an Item Number generation function:
public static String validateCADNumber( StringBuffer itemNumStr, ArrayList errors) throws
String itemNumNotInRangeMsg="CAD Item Number "+itemNum+" is not in range.";
Since "errors" is passed in as null, the "errors.add()" line will cause a null pointer
exception. And if you add in a line for errors = new ArrayList();, then the
reference for the object is no good as the output parameter.