如何使用低权限的 PL-SQL 在 Oracle 中获取列数据类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2339053/
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
How do I get column datatype in Oracle with PL-SQL with low privileges?
提问by James
I have "read only" access to a few tables in an Oracle database. I need to get schema information on some of the columns. I'd like to use something analogous to MS SQL's sp_help.
我对 Oracle 数据库中的几个表具有“只读”访问权限。我需要获取某些列的架构信息。我想使用类似于 MS SQL 的sp_help.
I see the table I'm interested in listed in this query:
我看到这个查询中列出了我感兴趣的表：
SELECT * FROM ALL_TABLES
When I run this query, Oracle tells me "table not found in schema", and yes the parameters are correct.
当我运行这个查询时，Oracle 告诉我“在架构中找不到表”，是的，参数是正确的。
SELECT
DBMS_METADATA.GET_DDL('TABLE', 'ITEM_COMMIT_AGG', 'INTAMPS') AS DDL
FROM DUAL;
After using my Oracle universal translator 9000 I've surmised this doesn't work because I don't have sufficient privileges. Given my constraints how can I get the datatype and data length of a column on a table I have read access to with a PL-SQL statement?
使用我的 Oracle 通用转换器 9000 后，我推测这不起作用，因为我没有足够的权限。鉴于我的限制，我怎样才能通过 PL-SQL 语句获得表中列的数据类型和数据长度？
采纳答案by Adam Musch
ALL_TAB_COLUMNSshould be queryable from PL/SQL. DESCis a SQL*Plus command.
ALL_TAB_COLUMNS应该可以从 PL/SQL 查询。 DESC是一个 SQL*Plus 命令。
SQL> desc all_tab_columns;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
COLUMN_NAME NOT NULL VARCHAR2(30)
DATA_TYPE VARCHAR2(106)
DATA_TYPE_MOD VARCHAR2(3)
DATA_TYPE_OWNER VARCHAR2(30)
DATA_LENGTH NOT NULL NUMBER
DATA_PRECISION NUMBER
DATA_SCALE NUMBER
NULLABLE VARCHAR2(1)
COLUMN_ID NUMBER
DEFAULT_LENGTH NUMBER
DATA_DEFAULT LONG
NUM_DISTINCT NUMBER
LOW_VALUE RAW(32)
HIGH_VALUE RAW(32)
DENSITY NUMBER
NUM_NULLS NUMBER
NUM_BUCKETS NUMBER
LAST_ANALYZED DATE
SAMPLE_SIZE NUMBER
CHARACTER_SET_NAME VARCHAR2(44)
CHAR_COL_DECL_LENGTH NUMBER
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
AVG_COL_LEN NUMBER
CHAR_LENGTH NUMBER
CHAR_USED VARCHAR2(1)
V80_FMT_IMAGE VARCHAR2(3)
DATA_UPGRADED VARCHAR2(3)
HISTOGRAM VARCHAR2(15)
回答by akf
You can use the desccommand.
您可以使用该desc命令。
desc MY_TABLE
This will give you the column names, whether null is valid, and the datatype (and length if applicable)
这将为您提供列名、null 是否有效以及数据类型（如果适用，还有长度）
回答by sev3ryn
The best solution that I've found for such case is
我为这种情况找到的最佳解决方案是
select column_name, data_type||
case
when data_precision is not null and nvl(data_scale,0)>0 then '('||data_precision||','||data_scale||')'
when data_precision is not null and nvl(data_scale,0)=0 then '('||data_precision||')'
when data_precision is null and data_scale is not null then '(*,'||data_scale||')'
when char_length>0 then '('||char_length|| case char_used
when 'B' then ' Byte'
when 'C' then ' Char'
else null
end||')'
end||decode(nullable, 'N', ' NOT NULL')
from user_tab_columns
where table_name = 'TABLE_NAME'
and column_name = 'COLUMN_NAME';
@Aaron Stainback, thank you for correction!
@Aaron Stainback，感谢您的指正！
回答by Lorin Davis
Note: if you are trying to get this information for tables that are in a different SCHEMA use the all_tab_columns view, we have this problem as our Applications use a different SCHEMA for security purposes.
注意：如果您尝试为不同 SCHEMA 中的表获取此信息，请使用 all_tab_columns 视图，我们会遇到此问题，因为我们的应用程序出于安全目的使用不同的 SCHEMA。
use the following:
使用以下内容：
EG:
例如：
SELECT
data_length
FROM
all_tab_columns
WHERE
upper(table_name) = 'MY_TABLE_NAME' AND upper(column_name) = 'MY_COL_NAME'
回答by Ibrahim Bayer
select t.data_type
from user_tab_columns t
where t.TABLE_NAME = 'xxx'
and t.COLUMN_NAME='aaa'
回答by Eric Leschinski
Oracle: Get a list of the full datatype in your table:
Oracle：获取表中完整数据类型的列表：
select data_type || '(' || data_length || ')'
from user_tab_columns where TABLE_NAME = 'YourTableName'
回答by mattpltt
select column_name, data_type || '(' || data_length || ')' as datatype
from all_tab_columns
where TABLE_NAME = upper('myTableName')
回答by Tagar
Quick and dirty way (e.g. to see how data is stored in oracle)
快速而肮脏的方式（例如查看数据如何存储在 oracle 中）
SQL> select dump(dummy) dump_dummy, dummy
, dump(10) dump_ten
from dual
DUMP_DUMMY DUMMY DUMP_TEN
---------------- ----- --------------------
Typ=1 Len=1: 88 X Typ=2 Len=2: 193,11
1 row selected.
will show that dummy column in table sys.dual has typ=1 (varchar2), while 10 is Typ=2 (number).
将显示表 sys.dual 中的虚拟列具有 typ=1（varchar2），而 10 是 Typ=2（数字）。
回答by R Muruganandhan
You can try this.
你可以试试这个。
SELECT *
FROM (SELECT column_name,
data_type,
data_type
|| CASE
WHEN data_precision IS NOT NULL
AND NVL (data_scale, 0) > 0
THEN
'(' || data_precision || ',' || data_scale || ')'
WHEN data_precision IS NOT NULL
AND NVL (data_scale, 0) = 0
THEN
'(' || data_precision || ')'
WHEN data_precision IS NULL AND data_scale IS NOT NULL
THEN
'(*,' || data_scale || ')'
WHEN char_length > 0
THEN
'(' || char_length
|| CASE char_used
WHEN 'B' THEN ' Byte'
WHEN 'C' THEN ' Char'
ELSE NULL
END
|| ')'
END
|| DECODE (nullable, 'N', ' NOT NULL')
DataTypeWithLength
FROM user_tab_columns
WHERE table_name = 'CONTRACT')
WHERE DataTypeWithLength = 'CHAR(1 Byte)';
回答by PiC
To see the internal representation size in bytes you can use:
要查看以字节为单位的内部表示大小，您可以使用：
REGEXP_SUBSTR(DUMP(your_column_name), 'Len=(\d+)\:', 1, 1, 'c', 1 )
