SQL 在ORACLE中的select语句中获取字段的数据类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22962114/
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
Get data type of field in select statement in ORACLE
提问by Bushwacka
Can I get data types of each column I selected instead of the values, using a select statement?
我可以使用 select 语句获取我选择的每一列的数据类型而不是值吗？
FOR EXAMPLE:
例如：
SELECT a.name, a.surname, b.ordernum
FROM customer a
JOIN orders b
ON a.id = b.id
and result should be like this
结果应该是这样的
name | NVARCHAR(100)
surname | NVARCHAR(100)
ordernum| INTEGER
or it can be in row like this, it isn't important:
或者它可以像这样排成一行，这并不重要：
name | surname | ordernum
NVARCHAR(100) | NVARCHAR(100) | INTEGER
Thanks
谢谢
采纳答案by abhi
You can query the all_tab_columnsview in the database.
您可以查询all_tab_columns数据库中的视图。
SELECT table_name, column_name, data_type, data_length FROM all_tab_columns where table_name = 'CUSTOMER'
回答by Maxwell Cheng
I found a not-very-intuitive way to do this by using DUMP()
我通过使用找到了一种不太直观的方法来做到这一点 DUMP()
SELECT DUMP(A.NAME),
DUMP(A.surname),
DUMP(B.ordernum)
FROM customer A
JOIN orders B
ON A.id = B.id
It will return something like:
它会返回类似的东西：
'Typ=1 Len=2: 0,48'for each column.
'Typ=1 Len=2: 0,48'对于每一列。
Type=1means VARCHAR2/NVARCHAR2Type=2means NUMBER/FLOATType=12means DATE, etc.
Type=1手段VARCHAR2/NVARCHAR2Type=2手段NUMBER/FLOATType=12手段DATE等。
You can refer to this oracle doc for information Datatype Code
or this for a simple mapping Oracle Type Code Mappings
您可以参考此 oracle 文档以获取信息数据类型代码
或此以获取简单映射Oracle 类型代码映射
回答by Fernando M
I usually create a view and use the DESCcommand:
我通常创建一个视图并使用DESC命令：
CREATE VIEW tmp_view AS
SELECT
a.name
, a.surname
, b.ordernum
FROM customer a
JOIN orders b
ON a.id = b.id
Then, the DESCcommand will show the type of each field.
然后，该DESC命令将显示每个字段的类型。
DESC tmp_view
DESC tmp_view
回答by Praveen
I came into the same situation. As a workaround, I just created a view(If you have privileges) and described it and dropped it later. :)
我遇到了同样的情况。作为一种解决方法，我刚刚创建了一个view（如果您有权限）并对其进行了描述并稍后将其删除。:)
回答by Swamp
If you don't have privileges to create a view in Oracle, a "hack" around it to use MS Access :-(
如果您没有在 Oracle 中创建视图的权限，请绕开它以使用 MS Access :-(
In MS Access, create a pass through query with your sql (but add where clause to just select 1 record), create a select query from the view (very important), selecting all *, then create a make table from the select query. When this runs it will create a table with one record, all the data types should "match" oracle. i.e. Passthrough --> Select --> MakeTable --> Table
在 MS Access 中，使用您的 sql 创建一个传递查询（但添加 where 子句以仅选择 1 条记录），从视图中创建一个选择查询（非常重要），选择所有 *，然后从选择查询中创建一个生成表。当它运行时，它将创建一个包含一条记录的表，所有数据类型都应该“匹配”oracle。即Passthrough --> Select --> MakeTable --> Table
I am sure there are other better ways, but if you have limited tools and privileges this will work.
我相信还有其他更好的方法，但如果您的工具和权限有限，这将起作用。
回答by Florin Ghita
Also, if you have Toad for Oracle, you can highlight the statement and press CTRL+ F9and you'll get a nice view of column and their datatypes.
此外，如果您有 Toad for Oracle，您可以突出显示该语句并按CTRL+ F9，您将获得列及其数据类型的良好视图。
回答by issam
you can use the DBMS_SQL.DESCRIBE_COLUMNS2
SET SERVEROUTPUT ON;
DECLARE
STMT CLOB;
CUR NUMBER;
COLCNT NUMBER;
IDX NUMBER;
COLDESC DBMS_SQL.DESC_TAB2;
BEGIN
CUR := DBMS_SQL.OPEN_CURSOR;
STMT := 'SELECT object_name , to_char(object_id), created FROM DBA_OBJECTS where rownum<10';
SYS.DBMS_SQL.PARSE(CUR, STMT, DBMS_SQL.NATIVE);
DBMS_SQL.DESCRIBE_COLUMNS2(CUR, COLCNT, COLDESC);
DBMS_OUTPUT.PUT_LINE('Statement: ' || STMT);
FOR IDX IN 1 .. COLCNT
LOOP
CASE COLDESC(IDX).col_type
WHEN 2 THEN
DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': NUMBER');
WHEN 12 THEN
DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': DATE');
WHEN 180 THEN
DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': TIMESTAMP');
WHEN 1 THEN
DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': VARCHAR'||':'|| COLDESC(IDX).col_max_len);
WHEN 9 THEN
DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': VARCHAR2');
-- Insert more cases if you need them
ELSE
DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': OTHERS (' || TO_CHAR(COLDESC(IDX).col_type) || ')');
END CASE;
END LOOP;
SYS.DBMS_SQL.CLOSE_CURSOR(CUR);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE()) || ': ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
SYS.DBMS_SQL.CLOSE_CURSOR(CUR);
END;
/
full example in the below url
https://www.ibm.com/support/knowledgecenter/sk/SSEPGG_9.7.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0055146.html
