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

Question

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?

Answer 1

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.

Answer 2

Or query ALL_TAB_COLS to get the column datatype.

Answer 3

I'm having a similar problem. Using DBMSSQL.DESCRIBECOLUMNS I get column types but now I need to have variables declared before as I need them in DBMSSQL.DEFINECOLUMN to get columns' values. Is it possible to declare variable after I execute DESCRIBECOLUMNS?

Answer 4

try this link it helps me lot .. http://pravinmagdum.wordpress.com/2009/06/15/query-for-select-field-namesdata-types-etc/