ORACLE INVISIBLE Column can be used for column ordering

September 22, 2021Posted inAPEX, PLSQL, SQL

How to reorder ORACLE table columns?

Sample table

Copy Code
CREATE TABLE test_columns_table
(
   id           NUMBER,
   name         VARCHAR2 (200),
   created_by   VARCHAR2 (50)
);

Add a column

Copy Code
ALTER TABLE TEST_COLUMNS_TABLE ADD EMAIL VARCHAR2(50);

Column Order will be

  • ID
  • NAME
  • CREATED_BY
  • EMAIL

Reorder Email next to NAME.

Copy Code
ALTER TABLE TEST_COLUMNS_TABLE MODIFY CREATED_BY INVISIBLE; 
ALTER TABLE TEST_COLUMNS_TABLE MODIFY CREATED_BY VISIBLE;

After doing so we get the table columns reordered

Column Order will be

  • ID
  • NAME
  • EMAIL
  • CREATED_BY

For all the tables for the user

Copy Code
DECLARE
CURSOR c_get_tables
is 
select table_name from all_tables where owner= USER;
l_sql_stmt VARCHAR2(4000);
BEGIN
FOR l_cnt in c_get_tables
LOOP
l_sql_stmt := 'ALTER TABLE '|| l_cnt.table_name||' MODIFY CREATED_BY INVISIBLE';
execute IMMEDIATE l_sql_stmt;

l_sql_stmt := 'ALTER TABLE '|| l_cnt.table_name||' MODIFY CREATED_BY VISIBLE';
execute IMMEDIATE l_sql_stmt;

l_sql_stmt := 'ALTER TABLE '|| l_cnt.table_name||' MODIFY CREATED_ON INVISIBLE';
execute IMMEDIATE l_sql_stmt;

l_sql_stmt := 'ALTER TABLE '|| l_cnt.table_name||' MODIFY CREATED_ON VISIBLE';
execute IMMEDIATE l_sql_stmt;

l_sql_stmt := 'ALTER TABLE '|| l_cnt.table_name||' MODIFY UPDATED_BY INVISIBLE';
execute IMMEDIATE l_sql_stmt;

l_sql_stmt := 'ALTER TABLE '|| l_cnt.table_name||' MODIFY UPDATED_BY VISIBLE';
execute IMMEDIATE l_sql_stmt;

l_sql_stmt := 'ALTER TABLE '|| l_cnt.table_name||' MODIFY UPDATED_ON INVISIBLE';
execute IMMEDIATE l_sql_stmt;

l_sql_stmt := 'ALTER TABLE '|| l_cnt.table_name||' MODIFY UPDATED_ON VISIBLE';
execute IMMEDIATE l_sql_stmt;

END loop;
END;
/
Tags: