如何在 Oracle 中获得具有低特权的 PL-SQL 列数据类型？

我可以“只读”访问 Oracle 数据库中的一些表。我需要获得一些列的模式信息。我想使用一些类似于 MS SQL 的 sp_help的东西。

我在这个查询中看到了我感兴趣的表:

SELECT * FROM ALL_TABLES

当我运行这个查询时，Oracle 告诉我“在模式中找不到表”，而且参数是正确的。

SELECT
DBMS_METADATA.GET_DDL('TABLE', 'ITEM_COMMIT_AGG', 'INTAMPS') AS DDL
FROM DUAL;

在使用了我的 Oracle 通用翻译器9000之后，我猜测这并不能工作，因为我没有足够的特权。考虑到我的约束，我怎样才能得到我用 PL-SQL 语句读访问的表上的列的数据类型和数据长度？

289210 次浏览
小开

您可以使用 desc命令。

desc MY_TABLE

这将为您提供列名(null 是否有效)和数据类型(以及适用的长度)

小开
最佳答案

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)
小开 
select t.data_type
from user_tab_columns t
where t.TABLE_NAME = 'xxx'
and t.COLUMN_NAME='aaa'
小开

Oracle 11.2: 在表中获取完整数据类型的列表:

create table SOMETABLE (foo integer, bar varchar(300));
select data_type || '(' || data_length || ')' thetype
from user_tab_columns where TABLE_NAME = 'SOMETABLE';

印刷品:

NUMBER(22)
VARCHAR(300)

截图: enter image description here

文件: Https://docs.oracle.com/cd/b19306_01/server.102/b14237/statviews_4462.htm#refrn26277

小开

注意: 如果您试图使用 all _ tab _ column 视图获取位于不同 SCHEMA 中的表的这些信息，那么出于安全目的，我们的应用程序使用不同的 SCHEMA 时就会遇到这个问题。

使用:

例如:

SELECT
data_length
FROM
all_tab_columns
WHERE
upper(table_name) = 'MY_TABLE_NAME' AND upper(column_name) = 'MY_COL_NAME'
小开

对于这种情况，我找到的最好的解决办法是

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 谢谢纠正！

小开 
select column_name, data_type || '(' || data_length || ')' as datatype
from all_tab_columns
where TABLE_NAME = upper('myTableName')
小开

快速和肮脏的方法(例如，查看数据是如何存储在 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.

将显示表 sys.double 中的虚拟列的 type = 1(varchar2) ，而10是 Typ = 2(number)。

小开

你可以试试这个。

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)';
小开

查看可以使用的内部表示大小(以字节为单位) :

REGEXP_SUBSTR(DUMP(your_column_name), 'Len=(\d+)\:', 1, 1, 'c', 1 )
小开 
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;


-- YOUR SELECT HERE
DBMS_SQL.PARSE(c, '


SELECT *
FROM table1 a
bable2 b
table3 c
where a.id = b.id
and b.id2 = c.id


', DBMS_SQL.NATIVE);


d := DBMS_SQL.EXECUTE(c);


DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);


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;
/

提交答案