How to Fetch Specific Column Names from an Oracle Table, Excluding Certain Columns?

Oracle query to extract column names

Question:

Modify a MySQL query to extract column names from a table in an Oracle 11g database, specifically excluding certain columns and specifying a schema. It's not clear whether it is possible to use the tablespace name instead of the schema name.

Solution:

1. Oracle equivalent of MySQL query:

The Oracle equivalent of MySQL's information_schema.COLUMNS is USER_TAB_COLS (for tables owned by the current user), or ALL_TAB_COLS or DBA_TAB_COLS (for tables owned by all users).

2. Schema and table space:

Tablespace is not equivalent to schema. Tablespace refers to the physical storage of database objects, while schema refers to the logical grouping of database objects.

3. Oracle query example:

This is a modified Oracle query to extract column names from the users table, excluding specified columns and specifying the schema:

<code class="language-sql">SELECT column_name
FROM all_tab_cols
WHERE table_name = 'USERS'
AND owner = '[_db]'
AND column_name NOT IN ('PASSWORD', 'VERSION', 'ID')</code>

Note:

• As shown in the query, Oracle table and column names are usually stored in uppercase.
• Be sure to prevent SQL injection when providing user input in queries.

The above is the detailed content of How to Fetch Specific Column Names from an Oracle Table, Excluding Certain Columns?. For more information, please follow other related articles on the PHP Chinese website!