CODEWITHSUNDEEP
oracle-databaseplsql
ttarchala
ttarchala

Reputation: 4567

PL-SQL: getting column data types out of query results

Trying to make a generic PL/SQL procedure to export data in specific XML format, e.g. Excel XML. Let's say the procedure accepts a string with the SELECT query to EXECUTE IMMEDIATE.

This requires access to data types of each column of the resulting rowset, which -- seeing as the procedure is to be generic -- is only known after the query is run.

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

How can I next process the rows and columns of an EXECUTE IMMEDIATE result in a double loop that analyzes the type of each value and emits an appropriate piece of XML?

Upvotes: 5

Views: 10183

Answers (2)

cagcowboy
cagcowboy

Reputation: 30848

Or query ALL_TAB_COLS to get the column datatype.

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132580

You can't do that with EXECUTE IMMEDIATE. You'll have to use the more powerful (and more complex) DBMS_SQL package - I've linked you to the DESCRIBE_COLUMNS procedure, which is especially relevant.

Upvotes: 8

Related Questions