Oracle 识别数据类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1087161/
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
Oracle Identify Data Type
提问by Steven
Is there an Oracle function to return the data type of the parameter?
是否有一个 Oracle 函数可以返回参数的数据类型？
Alternatively, what is the easiest way to determine the data type of all columns in a query that I've written?
或者，确定我编写的查询中所有列的数据类型的最简单方法是什么？
回答by borjab
The DumpFunction:
该转储功能：
returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of expr. The returned result is always in the database character set. For the datatype corresponding to each code, see Table 2-1.
返回一个 VARCHAR2 值，其中包含数据类型 code、长度（以字节为单位）和 expr 的内部表示。返回的结果总是在数据库字符集中。各编码对应的数据类型见表2-1。
回答by Jeffrey Kemp
If you've written a query, you could create a view based on it and then query the data dictionary to see what the columns' data types are:
如果您编写了查询，则可以基于它创建一个视图，然后查询数据字典以查看列的数据类型是什么：
create view vw_test as
select 1 an_integer,
'abc' a_string,
sysdate a_date
from dual;
desc vw_test;
Name Null Type
----------- -------- ------------------
AN_INTEGER NUMBER
A_STRING CHAR(3)
A_DATE DATE
I'm guessing, however, you want some kind of generic way of determining this at runtime. This method would not be very useful since it involves DDL. In which case DBMS_SQL may help you:
但是，我猜想您需要某种通用方法来在运行时确定这一点。这种方法不是很有用，因为它涉及 DDL。在这种情况下，DBMS_SQL 可以帮助您：
DECLARE
c NUMBER;
d NUMBER;
col_cnt INTEGER;
f BOOLEAN;
rec_tab DBMS_SQL.DESC_TAB;
col_num NUMBER;
PROCEDURE print_rec(rec in DBMS_SQL.DESC_REC) IS
BEGIN
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE('col_type = '
|| rec.col_type);
DBMS_OUTPUT.PUT_LINE('col_maxlen = '
|| rec.col_max_len);
DBMS_OUTPUT.PUT_LINE('col_name = '
|| rec.col_name);
DBMS_OUTPUT.PUT_LINE('col_name_len = '
|| rec.col_name_len);
DBMS_OUTPUT.PUT_LINE('col_schema_name = '
|| rec.col_schema_name);
DBMS_OUTPUT.PUT_LINE('col_schema_name_len = '
|| rec.col_schema_name_len);
DBMS_OUTPUT.PUT_LINE('col_precision = '
|| rec.col_precision);
DBMS_OUTPUT.PUT_LINE('col_scale = '
|| rec.col_scale);
DBMS_OUTPUT.PUT('col_null_ok = ');
IF (rec.col_null_ok) THEN
DBMS_OUTPUT.PUT_LINE('true');
ELSE
DBMS_OUTPUT.PUT_LINE('false');
END IF;
END;
BEGIN
c := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(c, 'SELECT * FROM scott.bonus', DBMS_SQL.NATIVE);
d := DBMS_SQL.EXECUTE(c);
DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
/*
* Following loop could simply be for j in 1..col_cnt loop.
* Here we are simply illustrating some of the PL/SQL table
* features.
*/
col_num := rec_tab.first;
IF (col_num IS NOT NULL) THEN
LOOP
print_rec(rec_tab(col_num));
col_num := rec_tab.next(col_num);
EXIT WHEN (col_num IS NULL);
END LOOP;
END IF;
DBMS_SQL.CLOSE_CURSOR(c);
END;
/
