Universal XML/JSON conversion for Oracle Objects

This will be a short post about making it easier to work with object-oriented code every day. Often, we want to check what's inside an object at a specific point in a function or procedure. Tools like Logger help with debugging, and if Logger isn't available, even using DBMS_OUTPUT.PUT_LINE can be very helpful. It lets us see the exact state of our object and fix the code issue

I thought it would be useful to create a universal method to display object content. No matter how complex it is, I want two extra methods that will return the object as XML or JSON. Even if the object has nested types, like collections. Also, I don't want to manually define the XML or JSON structure - I want Oracle to handle all the conversion and mapping for me.

💡

The final result I want to achieve is to have a method similar to toString() in Java.

For the example, I created a sample object that includes simple types and a collection of items. In the constructor, I assign example values to each attribute and add two methods: to_xml() and to_json().

💡

If you are looking for the sandbox to test my code, check out my link to FreeSQL.

💡

As you can see, both methods are very simple and don't require me to explicitly map the object to the final document format.

Finally, we can easily test my solution.

And the result is:

<EMPLOYEE_OT>
    <EMPLOYEE_ID>101</EMPLOYEE_ID>
    <FIRST_NAME>Steven</FIRST_NAME>
    <LAST_NAME>King</LAST_NAME>
    <EMAIL>steven.king@randomdomain.com</EMAIL>
    <JOB_ID>IT</JOB_ID>
    <SALARY_HISTORY>
        <SALARY_HISTORY_OT>
            <SALARY>10000</SALARY>
            <DATE_FROM>01-JAN-25</DATE_FROM>
            <DATE_TO>31-DEC-25</DATE_TO>
        </SALARY_HISTORY_OT>
        <SALARY_HISTORY_OT>
            <SALARY>20000</SALARY>
            <DATE_FROM>01-JAN-26</DATE_FROM>
            <DATE_TO>31-DEC-26</DATE_TO>
        </SALARY_HISTORY_OT>
    </SALARY_HISTORY>
</EMPLOYEE_OT>

{
  "EMPLOYEE_ID": 101,
  "FIRST_NAME": "Steven",
  "LAST_NAME": "King",
  "EMAIL": "steven.king@randomdomain.com",
  "JOB_ID": "IT",
  "SALARY_HISTORY": {
    "1": {
      "SALARY": 10000,
      "DATE_FROM": "2025-01-01T00:00:00",
      "DATE_TO": "2025-12-31T00:00:00"
    },
    "2": {
      "SALARY": 20000,
      "DATE_FROM": "2026-01-01T00:00:00",
      "DATE_TO": "2026-12-31T00:00:00"
    }
  }
}