oracle PL-SQL：从查询结果中获取列数据类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/375458/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PL-SQL: getting column data types out of query results
提问by ttarchala
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.
试图制作一个通用的 PL/SQL 程序来以特定的 XML 格式导出数据，例如 Excel XML。假设该过程接受一个带有 SELECT 查询到 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?
我接下来如何处理 EXECUTE IMMEDIATE 结果的行和列，以分析每个值的类型并发出适当的 XML 片段？
回答by Tony Andrews
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.
你不能用 EXECUTE IMMEDIATE 做到这一点。您将不得不使用更强大（也更复杂）的DBMS_SQL 包- 我已将您链接到 DESCRIBE_COLUMNS 过程，该过程尤其相关。
回答by cagcowboy
Or query ALL_TAB_COLS to get the column datatype.
或查询 ALL_TAB_COLS 以获取列数据类型。
