Oracle query to fetch column names

by

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.

More Related Contents:

  1. How to create id with AUTO_INCREMENT on Oracle?
  2. LISTAGG in Oracle to return distinct values
  3. Fetch the row which has the Max value for a column
  4. Oracle SQL: Update a table with data from another table
  5. Is there an Oracle SQL query that aggregates multiple rows into one row? [duplicate]
  6. How to handle a single quote in Oracle SQL
  7. Select count(*) from multiple tables
  8. Oracle SQL – How to Retrieve highest 5 values of a column [duplicate]
  9. Fetch the rows which have the Max value for a column for each distinct value of another column
  10. REGEX to select nth value from a list, allowing for nulls
  11. SQL Error: ORA-00933: SQL command not properly ended
  12. CASE vs. DECODE
  13. DATEDIFF function in Oracle [duplicate]
  14. WHERE IN condition not accepting String value
  15. What is the difference between Views and Materialized Views in Oracle?
  16. Pivoting of data using two columns
  17. Check if current date is between two dates Oracle SQL
  18. ORA-00907: missing right parenthesis
  19. How do I specify date literal when writing SQL query from SQL Server that is linked to Oracle?
  20. Unordered results in SQL
  21. Oracle Dynamic Pivoting
  22. Foreign Key Creation issue in Oracle
  23. create custom function for date difference excluding weekends and holidays in oracle sql
  24. What is it exactly a BLOB in a DBMS context
  25. Is there a version control system for database structure changes?
  26. Oracle: Updating a table column using ROWNUM in conjunction with ORDER BY clause
  27. How to check if a column exists before adding it to an existing table in PL/SQL?
  28. How can I solve ORA-00911: invalid character error?
  29. Why doesn’t Oracle SQL allow us to use column aliases in GROUP BY clauses?
  30. Oracle ‘Partition By’ and ‘Row_Number’ keyword

Leave a Comment