I have a mySQL query to get columns from a table like this:
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')"
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?)
The Oracle equivalent for information_schema.COLUMNS
is USER_TAB_COLS
for tables owned by the current user, ALL_TAB_COLS
or DBA_TAB_COLS
for tables owned by all users.
Tablespace is not equivalent to a schema, neither do you have to provide the tablespace name.
Providing the schema/username would be of use if you want to query ALL_TAB_COLS
or DBA_TAB_COLS
for columns OF tables owned by a specific user. in your case, I'd imagine the query would look something like:
String sqlStr= "
SELECT column_name
FROM all_tab_cols
WHERE table_name = 'USERS'
AND owner = '" +_db+ "'
AND column_name NOT IN ( 'PASSWORD', 'VERSION', 'ID' )"
Note that with this approach, you risk SQL injection.
EDIT: Uppercased the table- and column names as these are typically uppercase in Oracle; they are only lower- or mixed case if created with double quotes around them.
The below query worked for me in Oracle database.
select COLUMN_NAME from ALL_TAB_COLUMNS where TABLE_NAME='MyTableName';
...WHERE LOWER(Table_Name) = 'mytablename';
.
where lower(TABLE_NAME) = lower('WHATEVER')
, else when the table name has some uppercase character it won't find the table either
SELECT column_name FROM all_tab_cols WHERE UPPER(Table_Name) = UPPER('tablename');
works just as well.
lower(TABLE_NAME)
or upper(TABLE_NAME)
requires oracle to do a table scan of the ALL_TAB_COLUMNS
table to get all values of TABLE_NAME
before it can compare it to the supplied UPPER('MyTableName')
. In quick testing this made the performance unusable for my purpose so I will stick with case sensitive comparisons.
WHERE table_name = UPPER('my_table_name')
. If this is part of a larger piece of code, you should uppercase the user input before passing it to the WHERE condition.
in oracle you can use
desc users
to display all columns containing in users table
desc users
a bad answer to some questions, but it is not a good answer to this one.
You may try this : ( It works on 11g and it returns all column name from a table , here test_tbl is the table name and user_tab_columns are user permitted table's columns )
select COLUMN_NAME from user_tab_columns where table_name='test_tbl';
USER_TAB_COLUMNS
is actually the columns of the tables owned by the user, not those of tables permitted to the user.
the point is that in toad u have to write table name capital, like this:
select *
FROM all_tab_columns
where table_name like 'IDECLARATION';
The query to use with Oracle is:
String sqlStr="select COLUMN_NAME from ALL_TAB_COLUMNS where TABLE_NAME='"+_db+".users' and COLUMN_NAME not in ('password','version','id')"
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 DatabaseMetaData.
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 Oracle doc.
TABLE_NAME='"+_db+".users'
will fail; you need to separate owner/schema and table name in ALL_TAB_COLUMNS
The only way that I was able to get the column names was using the following query:
select COLUMN_NAME
FROM all_tab_columns atc
WHERE table_name like 'USERS'
On Several occasions, we would need comma separated list of all the columns from a table in a schema. In such cases we can use this generic function which fetches the comma separated list as a string.
CREATE OR REPLACE FUNCTION cols(
p_schema_name IN VARCHAR2,
p_table_name IN VARCHAR2)
RETURN VARCHAR2
IS
v_string VARCHAR2(4000);
BEGIN
SELECT LISTAGG(COLUMN_NAME , ',' ) WITHIN GROUP (
ORDER BY ROWNUM )
INTO v_string
FROM ALL_TAB_COLUMNS
WHERE OWNER = p_schema_name
AND table_name = p_table_name;
RETURN v_string;
END;
/
So, simply calling the function from the query yields a row with all the columns.
select cols('HR','EMPLOYEES') FROM DUAL;
EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID
Note: LISTAGG
will fail if the combined length of all columns exceed 4000
characters which is rare. For most cases , this will work.
I find this one useful in Oracle:
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;
SELECT * FROM
You can use the below query to get a list of table names which uses the specific column in DB2:
SELECT TBNAME
FROM SYSIBM.SYSCOLUMNS
WHERE NAME LIKE '%COLUMN_NAME';
Note : Here replace the COLUMN_NAME
with the column name that you are searching for.
You can try this:
describe 'Table Name'
It will return all column names and data types
Success story sharing
and virtual_column = 'NO'
to my query.