Oracle PL/SQL/System Tables Views/DBA TAB COLUMNS

Query column_name, hidden_column, data_type from DBA_TAB_COLS for a table name

   <source lang="sql">

SQL> create or replace type Job as object (

 2   firstname varchar2(30),
 3   surname varchar2(30),
 4   date_of_birth date,
 5   end_date date,
 6   item_count number(4),
 7   items_retrieved varchar2(1)
 8  )
 9  /

Type created. SQL> create table Task (

 2   person_id number(10),
 3   job_detail Job
 4  );

Table created. SQL> SQL> set long 50000 SQL> select text from dba_views where view_name = "DBA_TAB_COLUMNS"

 2  /

TEXT

---

select OWNER, TABLE_NAME,

      COLUMN_NAME, DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER,
      DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID,
      DEFAULT_LENGTH, DATA_DEFAULT, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE,
      DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE,
      CHARACTER_SET_NAME, CHAR_COL_DECL_LENGTH,
      GLOBAL_STATS, USER_STATS, AVG_COL_LEN, CHAR_LENGTH, CHAR_USED,
      V80_FMT_IMAGE, DATA_UPGRADED, HISTOGRAM
 from DBA_TAB_COLS
where HIDDEN_COLUMN = "NO"

1 row selected. SQL> select column_name, hidden_column, data_type from DBA_TAB_COLS where table_name = "TASK"

 2  /

SQL> drop table Task; Table dropped. SQL>

 </source>
   
  

Using the DBA_TAB_COLUMNS View

   <source lang="sql">
 

SQL> SQL> -- SQL> SQL> select column_name,data_type, nullable from dba_tab_columns

 2  where rownum < 50;

COLUMN_NAME

---

DATA_TYPE

---

N - SPARE6 DATE Y SPARE5 VARCHAR2 Y SPARE4 VARCHAR2 Y SPARE3 NUMBER Y SPARE2 NUMBER Y SPARE1 NUMBER Y INTCOL# NUMBER N OFFSET NUMBER N SEGCOLLENGTH NUMBER

COLUMN_NAME

---

DATA_TYPE

---

N - N SEGCOL# NUMBER N POS# NUMBER N COL# NUMBER N BO# NUMBER N OBJ# NUMBER N SPARE6 DATE Y SPARE5 VARCHAR2 Y SPARE4 VARCHAR2 Y SPARE3 NUMBER Y SPARE2 NUMBER Y SPARE1 NUMBER Y CON# NUMBER N NAME VARCHAR2 N OWNER# NUMBER N SPARE6 DATE Y SPARE5 VARCHAR2 Y SPARE4 VARCHAR2 Y SPARE3 NUMBER Y SPARE2 NUMBER Y SPARE1 NUMBER Y FLAGS NUMBER Y OPTIMAL NUMBER Y KEEP NUMBER Y UGRP# NUMBER Y TS# NUMBER Y STATUS$ NUMBER N INST# NUMBER Y UNDOSQN NUMBER Y XACTSQN NUMBER Y SCNWRP NUMBER Y SCNBAS NUMBER Y BLOCK# NUMBER N FILE# NUMBER N USER# NUMBER N NAME VARCHAR2 N US# NUMBER N OBJ# NUMBER Y ROLE# NUMBER N PROXY# NUMBER N CLIENT# NUMBER N

49 rows selected.

 </source>