blocks|key|689649|text|与Oracle一起使用的查询为：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|689650|String+sqlStr="select+COLUMN_NAME+from+ALL_TAB_COLUMNS+where+TABLE_NAME='"%2B_db%2B".users'+and+COLUMN_NAME+not+in+('password','version','id')"|offset|length|style|CODE|689651|从未听说过用于此类查询的HQL。我认为ORM实现来处理它是没有意义的。ORM是一种对象关系映射，而您正在寻找的是元数据映射...您不会使用HQL，而是使用API方法或直接SQL。例如，您可以使用JDBC+DatabaseMetaData。|689652|我认为表空间与模式无关。AFAIK表空间主要用于逻辑内部技术目的，这应该会困扰DBA。有关表空间的更多信息，请参阅Oracle+doc。|689653|entityMap|0|LINK|mutability|MUTABLE|url|http://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html|1|http://docs.oracle.com/cd/B19306_01/server.102/b14220/physical.htm^0|0|0|3V|0|2U|G|0|0|1L|A|1|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|W|8|@$D|X|E|Y|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|Z|8|@]|9|@$D|10|E|11|1|12]]|A|$]]|$1|J|3|K|5|6|7|13|8|@]|9|@$D|14|E|15|1|16]]|A|$]]|$1|L|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|M|$N|$5|O|P|Q|A|$R|S]]|T|$5|O|P|Q|A|$R|U]]]]

The query to use with Oracle is:

<code>String sqlStr="select COLUMN_NAME from ALL_TAB_COLUMNS where TABLE_NAME='"+_db+".users' and COLUMN_NAME not in ('password','version','id')"</code>

Never heard of HQL for such queries. I assume it doesn't make sense for ORM implementations to deal with it. ORM is an Object Relational Mapping, and what you're looking for is metadata mapping... You wouldn't use HQL, rather use API methods for this purpose, or direct SQL. For instance, you can use JDBC <a href="http://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html" rel="nofollow">DatabaseMetaData</a>.

I think tablespace has nothing to do with schema. AFAIK tablespaces are mainly used for logical internal technical purposes which should bother DBAs. For more information regarding tablespaces, see <a href="http://docs.oracle.com/cd/B19306_01/server.102/b14220/physical.htm" rel="nofollow">Oracle doc</a>.

blocks|key|689681|text|您可以使用以下查询来获取使用DB2中特定列的表名称列表：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|689682|SELECT+TBNAME++++++++++++++++
FROM+SYSIBM.SYSCOLUMNS+++++++
WHERE+NAME+LIKE+'%25COLUMN_NAME';+|code-block|syntax|javascript|689683|注意:在这里将COLUMN_NAME替换为您要搜索的列名。|offset|length|style|CODE|689684|entityMap^0|0|0|7|B|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

You can use the below query to get a list of table names which uses the specific column in DB2:

<pre><code>SELECT TBNAME 
FROM SYSIBM.SYSCOLUMNS 
WHERE NAME LIKE '%COLUMN_NAME'; 
</code></pre>

Note : Here replace the <code>COLUMN_NAME</code> with the column name that you are searching for.

blocks|key|473560|text|下面的查询在Oracle数据库中对我有效。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|473561|select+COLUMN_NAME+from+ALL_TAB_COLUMNS+where+TABLE_NAME='MyTableName';|code-block|syntax|javascript|473562|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The below query worked for me in Oracle database.

<pre><code>select COLUMN_NAME from ALL_TAB_COLUMNS where TABLE_NAME='MyTableName';
</code></pre>

blocks|key|689733|text|在oracle中，您可以使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|689734|desc+users|code-block|syntax|javascript|689735|显示users表中包含的所有列|689736|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

in oracle you can use 

<pre><code>desc users
</code></pre>

to display all columns containing in users table

blocks|key|473619|text|我发现这一点在Oracle中很有用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|473620|SELECT+
++++obj.object_name,+
++++atc.column_name,+
++++atc.data_type,+
++++atc.data_length+
FROM+
++++all_tab_columns+atc,
++++(SELECT+
++++++++*+
+++++FROM+
+++++++++all_objects
+++++WHERE+
++++++++object_name+like+'GL_JE%25'
++++++++AND+owner+=+'GL'
++++++++AND+object_type+in+('TABLE','VIEW')+++
++++)+obj
WHERE+
++++atc.table_name+=+obj.object_name
ORDER+BY+
++++obj.object_name,+
++++atc.column_name;|code-block|syntax|javascript|473621|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I find this one useful in Oracle:

<pre><code>SELECT 
 obj.object_name, 
 atc.column_name, 
 atc.data_type, 
 atc.data_length 
FROM 
 all_tab_columns atc,
 (SELECT 
 * 
 FROM 
 all_objects
 WHERE 
 object_name like 'GL_JE%'
 AND owner = 'GL'
 AND object_type in ('TABLE','VIEW') 
 ) obj
WHERE 
 atc.table_name = obj.object_name
ORDER BY 
 obj.object_name, 
 atc.column_name;
</code></pre>

blocks|key|473647|text|我能够获得列名的唯一方法是使用以下查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|473648|select+COLUMN_NAME
FROM+all_tab_columns+atc
WHERE+table_name+like+'USERS'|code-block|syntax|javascript|473649|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The only way that I was able to get the column names was using the following query:

<pre><code>select COLUMN_NAME
FROM all_tab_columns atc
WHERE table_name like 'USERS'
</code></pre>

blocks|key|473653|text|您可以尝试这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|473654|描述“表名”|473655|它将返回所有列名和数据类型|473656|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

You can try this:

describe 'Table Name'

It will return all column names and data types

blocks|key|689797|text|关键是，在toad中，您必须写出表名capital，如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|689798|select+*
FROM+all_tab_columns
where+table_name+like+'IDECLARATION';|code-block|syntax|javascript|689799|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

the point is that in toad u have to write table name capital, like this:

<pre><code>select *
FROM all_tab_columns
where table_name like 'IDECLARATION';
</code></pre>

I have a mySQL query to get columns from a table like this:

<pre><code>String sqlStr="select column_name 
from information_schema.COLUMNS 
where table_name='users' 
and table_schema='"+_db+"' 
and column_name not in ('password','version','id')"
</code></pre>

How do I change the above query in Oracle 11g database? I need to get columns names as a resultset for table 'users' excluding certain columns, specifying a schema. Right now I have all tables in my new tablespace, so do I specify tablespace name in place of schema name?

Also is there a generic HQL for this? In my new Oracle database (I am new to Oracle), I only have tablespace name, so is that equivalent to schema name (logically?)

Oracle query to fetch column names

Oracle数据库

数据库

我有一个从表中获取列的mySQL查询，如下所示：String sqlStr="select column_name from information_schema.COLUMNS where table_name='users' and table_schema='"+_db+"' and column_name no...

问用于获取列名的Oracle查询
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问用于获取列名的Oracle查询EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问用于获取列名的Oracle查询
EN