A company is interested in buying E2E. But they have the requirement to support complex data types when calling Oracle stored procedures.
This is not yet supporteed by E2E. But I have found a way to do this.
The basic principle is:
This approach suggests a few different solutions:
The customer seems happy with solution 3. This is how it works:
This procedure must be created in the clients database, a drawback. The procedure:
PROCEDURE validate_order_sv (pir_createorder_sv IN VARCHAR2, por_result_sv OUT VARCHAR2) IS
inputxmltype := XMLTYPE.createxml(pir_createorder_sv);
ohb_odr.validate_order (pir_createorder => lr_ohb_order, por_result => lr_ohb_val_result);
SELECT sys_XMLGEN(lr_ohb_val_result, XMLFormat(‘ohb_result_obj’)).getStringVal() INTO por_result_sv FROM DUAL;
This procedure can be declared in the e2e. Action script before calling:
set inputString = ohb_createorder_obj.classToXML().transcodeToString(“UTF8”);
Action script after calling:
set ohb_result_obj = outputString.xmlToClass();
While testing, some problems were encountered:
ORA-01861: literal does not match format string
Problem 1 and 2 can be solved in action script. But the DATE field is more complicated:
Obviously, I consider this a bug in the Oracle XML implementation 🙂 XML is well specified, XMLTYPE.createxml should understand well-formed XML.
Please let me know your views on this
I think it’s a creative solution to a the problem! So far, when I came across this requirement, I also solved it with xml as input to the SQL adapter, but didn’t have to deal with the XML to Oracle Objects transformation within Oracle.
If I understand you correctly, an alternative approach to solving your problem 3 would be to use xmlOptions in the classToXml() operation, and defining a dateFormatString like %F:
For the NLS_DATE_FORMAT option, we do something similar with NLS_LANG, see CON-726.