Serialize Oracle row to XML

Question

I want to create a stored procedure that takes the name of a table and a row_id and can serialize it to an xml string.

The table may contain clobs and blobs.

Can I do this in PL/SQL, or do I have to use Java?

The main objective for this is to have a table with all updates and deletes on some tables, keeping the X latest versions, or X days of Data (the table would include something like chg_date(default:sysdate), chg_type(U or D), chg_xml, and probably with some metadata about the user).

Possible uses: - It could also keep all the data and be used as a log - The ability to return the row to any previous value. - The ability to do EDI in a specific format.

I don't want to use Oracle's flashback queries to get there since there's no guarantee of the data availability.

Answer 1

Oracle has a function to return a query in xml format.

In this example, replace &table with your table name, and &rowid with the rowid. I tested it and it seems to work with clobs and blobs. For blobs it returns the data in hex.

SELECT DBMS_XMLGEN.getxmltype ('select * from &table_name where rowid = ''&rowid''' )
  FROM DUAL