PL-SQL: Retrieving Column Data Types from Query Results

Question

An attempt to make a general PL / SQL procedure for exporting data in a specific XML format, for example. Excel XML Let them say that the procedure accepts a string with the SELECT query for EXECUTE IMMEDIATE.

This requires access to the data types of each column of the resulting rowset, which - seeing that the procedure is universal - is known only after the query is completed.

I tried the approach with a temporary table, but for the compilation procedure, the table must exist and have its own structure at compile time.

How can I process the rows and columns of an EXECUTE IMMEDIATE result in a double loop that parses the type of each value and emits the corresponding XML fragment?

Answer 1

You cannot do this with EXECUTE IMMEDIATE. You will need to use the more powerful (and more complex) package DBMS_SQL - I connected you with the DESCRIBE_COLUMNS procedure, which is especially important.

Answer 2

Or query ALL_TAB_COLS to get the column data type.