bildwelt
E2E FORUM
E2E Bridge E2E Commerce

Complex data types in Oracle stored procedures

E2E Forum General Discussion Complex data types in Oracle stored procedures

This topic contains 1 reply, has 2 voices, and was last updated by  Christof 4 years, 2 months ago.

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #656

    Andi
    Moderator

    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:

    • Create or import classes for all complex data types. Reed supplied these classes in an XSD.
    • When interfacing to a stored procedure with complex data types:
      • Using action script to convert input classes to a string.
      • Use Oracle functionality to convert the strings to a complex type (object).
      • Call the stored procedure.
      • Use Oracle functionality to convert output complex types to a string.
      • Use action script to convert output strings to classes.

    This approach suggests a few different solutions:

    1. Use the SQL adapter and SQL editor.
    2. Use the SQL adapter with dynamic SQL.
    3. Use a Oracle stored procedure for conversion.

    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
    lr_ohb_order    ohb_createorder_obj;
    lr_ohb_val_result    ohb_result_obj;
    inputxmltype    xmltype;
    BEGIN
    inputxmltype := XMLTYPE.createxml(pir_createorder_sv);
    inputxmltype.toobject(lr_ohb_order);
    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;
    END validate_order_sv;

    This procedure can be declared in the e2e. Action script before calling:
    set inputString = ohb_createorder_obj.classToXML().transcodeToString(“UTF8”);
    Action script after calling:
    create ohb_result_obj;
    set ohb_result_obj = outputString.xmlToClass();

    While testing, some problems were encountered:

    1. The Reed xsd provided namespaces. The Oracle functions did not accept namespaces.
    2. The Reed xsd was incorrect when mapping “TABLE OF”.
    3. Oracle rejected all DATE fields:
    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:

    1. The class contains an attribute, invoiceDate. The XSD specifies this as xs:date. But classToXML() fills the XML with 2013-06-01T00:00:00.0Z. Why add the time?
    2. The customer remarked that the Oracle variable NLS_DATE_FORMAT might cause this problem. True, when I set NLS_DATE_FORMAT to YYYY-MM-DD”T”HH24:MI:SS”.00Z” it works.
    3. This raises the question, what is the best place to set NLS_DATE_FORMAT:
    • Computer environment (E2E server). Rather not, might break queries and results from other tables and procedures.
    • Inside the stored procedure: EXECUTE IMMEDIATE ‘ALTER SESSION SET nls_date_format=”YYYY-MM-DD”T”HH24:MI:SS”.00Z””’;
      • This depends on how the SQL adapter handles sessions. 1 session for each SQL adapter action, this will work. 1 session per persistent state object might break things.
    • As a parameter to the SQL adapter action. I do not know how to do this.

    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

    #657

    Christof
    Blocked

    Andi,

    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:

    http://docu.e2ebridge.com/classToXML#

    For the NLS_DATE_FORMAT option, we do something similar  with NLS_LANG, see CON-726.

    Regards,

    Christof

     

     

     

     

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.