如何通过其代码确定Oracle数据类型的字符串值? [英] How can I determine string value of Oracle's datatype by its code?

问题描述

我使用DBMS_SQL.DESCRIBE_COLUMNS过程来确定SELECT查询结果集中的列使用哪种数据类型.但是不幸的是，这样我只能得到Oracle数据类型(record.col_type)的代码-1，8，12等.

I use DBMS_SQL.DESCRIBE_COLUMNS procedure to identify what datatypes are used for columns in result set for my SELECT query. But unfortunately in this way I can get only a code of Oracle's datatype (record.col_type) - 1, 8, 12 etc.

所以我想知道，如何轻松地获得与返回的数据类型的代码(即VARCHAR2，LONG，DATE等)等效的字符串，而不是其数字代码?

So I wonder, how can I easely get a string equivalent of returned datatype's code (i.e. VARCHAR2, LONG, DATE etc.) instead of its numeric code?

推荐答案

这是一个笨拙的解决方案，但是您可以使用CASE语句来查找"数据类型描述.

This is a slightly clunky solution, but you can use a CASE statement to "lookup" the datatype descriptions.

case record.col_type
  when dbms_types.TYPECODE_DATE then 'DATE'             
  when dbms_types.TYPECODE_NUMBER then 'NUMBER'           
  when dbms_types.TYPECODE_RAW then 'RAW'              
  when dbms_types.TYPECODE_CHAR then 'CHAR'             
  when dbms_types.TYPECODE_VARCHAR2 then 'VARCHAR2'         
  when dbms_types.TYPECODE_VARCHAR then 'VARCHAR'          
  when dbms_types.TYPECODE_MLSLABEL then 'MLSLABEL'         
  when dbms_types.TYPECODE_BLOB then 'BLOB'             
  when dbms_types.TYPECODE_BFILE then 'BFILE'            
  when dbms_types.TYPECODE_CLOB then 'CLOB'              
  when dbms_types.TYPECODE_CFILE then 'CFILE'            
  when dbms_types.TYPECODE_TIMESTAMP then 'TIMESTAMP'        
  when dbms_types.TYPECODE_TIMESTAMP_TZ then 'TIMESTAMP_TZ'     
  when dbms_types.TYPECODE_TIMESTAMP_LTZ then 'TIMESTAMP_LTZ'    
  when dbms_types.TYPECODE_INTERVAL_YM then 'INTERVAL_YM'      
  when dbms_types.TYPECODE_INTERVAL_DS then 'INTERVAL_DS'      
  when dbms_types.TYPECODE_REF then 'REF'              
  when dbms_types.TYPECODE_OBJECT then 'OBJECT'           
  when dbms_types.TYPECODE_VARRAY then 'VARRAY'                       
  when dbms_types.TYPECODE_TABLE then 'TABLE'                        
  when dbms_types.TYPECODE_NAMEDCOLLECTION then 'NAMEDCOLLECTION'  
  when dbms_types.TYPECODE_OPAQUE then 'OPAQUE'                            
  when dbms_types.TYPECODE_NCHAR then 'NCHAR'            
  when dbms_types.TYPECODE_NVARCHAR2 then 'NVARCHAR2'       
  when dbms_types.TYPECODE_NCLOB then 'NCLOB'                  
  when dbms_types.TYPECODE_BFLOAT then 'BFLOAT'           
  when dbms_types.TYPECODE_BDOUBLE then 'BDOUBLE'          
  when dbms_types.TYPECODE_UROWID then 'UROWID'              
end case

要获取此值，您应该按照tbone的建议查询数据字典.

To get the values for this you should query the data dictionary as tbone suggests.

这篇关于如何通过其代码确定Oracle数据类型的字符串值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！