Oracle SQL:从 all_tab_columns 中选择未找到现有列

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/17364870/
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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-19 01:46:01  来源:igfitidea点击:

Oracle SQL: selecting from all_tab_columns does not find existing column

sqloracleoracle-sqldeveloper

提问by lostinthebits

If I run the following query:

如果我运行以下查询:

select count(*) from all_tab_columns
        where column_name = 'foo'
        and table_name = 'VIEW0';

I get 0 for a result. I expect 1.

我得到 0 的结果。我期待 1。

But if I run the following query I get many (expected) rows returned:

但是,如果我运行以下查询,则会返回许多(预期的)行:

select foo from VIEW0;

Why? I'm assuming I'm making some dumb syntax mistake or my understanding is way off.

为什么?我假设我犯了一些愚蠢的语法错误,或者我的理解有偏差。

回答by Robert

Probably the reason is that you have case sensitive setting.

可能的原因是您有区分大小写的设置。

Try to add UPPERfunction as below.

尝试添加UPPER如下功能。

select count(*) from all_tab_columns
        where column_name = upper('foo')
        and table_name = 'VIEW0';

回答by Mikhail

ALL_TAB_COLUMNS describes the columns of the tables, views, and clusters accessibleto the current user.Check, if user under whom you running this query have access to the desired table.

ALL_TAB_COLUMNS 描述了当前用户可以访问的表、视图和集群的列。检查您运行此查询的用户是否有权访问所需的表。

回答by Pete Nelson

It appears, at least in 11g, that you cannot access the data dictionary tables from PL/SQL. Running any select on all_tab_columns inside PL/SQL always returns no results. Attempting to access dba_tab_columns will not compile, because the compiler believes the table (or view) does not exist.

至少在 11g 中,您似乎无法从 PL/SQL 访问数据字典表。在 PL/SQL 中的 all_tab_columns 上运行任何选择总是不返回任何结果。尝试访问 dba_tab_columns 将不会编译,因为编译器认为该表(或视图)不存在。

I'd love to see how to access the data dictionary from PL/SQL.

我很想知道如何从 PL/SQL 访问数据字典。