Oracle string serialization in XML

Question

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

The table may contain gobs and drops.

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

The main purpose of this is to have a table with all updates and deletes on some tables, to save the last X versions or X days of data (the table will include something like chg_date(default:sysdate) , chg_type(U or D) , chg_xml and, perhaps with some metadata about this user).

Possible applications: - It can also store all data and be used as a log - Ability to return a string to any previous value. - Ability to do EDI in a specific format.

I do not want to use Oracle backback queries to get there, since there is no guarantee of data availability.

Answer 1

Oracle has a function that returns a request in XML format.

In this example, replace & table with the name of your table and & rowid with rowid. I tested it and it seemed to work with Klobs and blobs. For blobs, it returns data in hexadecimal format.

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