I'm a little embarrassed that this has taken me so long, but I'm a bit rusty on my pl/sql and am throwing in the towel
DB: 11.1.0.7.0
Scenario: We receive a file of records from an external system, and they must be validated in the Oracle database. A single record has to go through about 20 validations for length, content...etc. They want to perform every validation on a single record and gather up a list of every validation that failed and pass it back to the client. I'm creating a packaged stored procedure and need to pass out the set of errors to our middleware. It does not understand collection types, so I was thinking of passing back XML as a CLOB which it can easily parse instead of the array.
As I encounter an error, I'm throwing it in a simple array of varchar2(4000). Where I'm stuck is that at the end, I want to convert the errors into an XML string (CLOB) with a simple format like this:
<errors>
<error>ERROR GOES HERE</error>
<error>ERROR 2 GOES HERE</error>
...
</errors>
I've looked at the XML functions in pl/sql and admittedly I'm overwhelmed at what to use where. I do have it in a collection that can use table(cast()) to get it in a resultset, but I'm stuck on which of the 4000 XML functions I should use. I've searched extensively, but it seems like a lot of the hits I get are on how to convert XML to something else, and few on how to create XML from Oracle.
Can someone nudge me in the right direction?