SQLcl Aliases & The Invisible Column Trick

SQLcl is the bee's knees.

Problem

Today I had a common problem. I wanted to reposition a column within a table.

"Oh you should use views!" 

"Never reference a table directly!" 

"Column position should be meaningless!"

I hear you shout. Yes but, look at this table definition.

PERSON table

FIRSTNAME,ADDRESS,PHONE,LASTNAME

Doesn't that drive you mad. 

I want the order FIRSTNAME, LASTNAME, ADDRESS, PHONE

Or worse. You decide to add a new column  (middlename) to your table definition script.

create table person (firstname varchar2(100), 

                           middlename varchar2(100),

                                 lastname varchar2(100), 

                                   address varchar2(100), 

                                     phone varchar2(100));

But you need to update the table right now

alter table person add (middlename varchar2(100));

Your table definition script will generated a different table from your live table as the live tables "middlename" will be the last column... 

Solutions

You could perform CTAS, drop the old table and rename the new table. But what about its triggers, indexes, ... 

Luckily in Oracle 12c  INVISIBLE columns  have a neat side effect.

Ask Tom goes into the details, but the gist of is 

• If you make a column invisible it is logically ordered as the last column. 
• By making some of the columns invisible  it allows you to position a column where you want.
• Afterwards make the columns visible again. The new ordering is retained.

Example

ALTER TABLE person MODIFY(lastname INVISIBLE,address INVISIBLE,phone INVISIBLE);

Shifts those columns to the end, making the middlename column the second column. Make them visible again and you have logically reordered the table columns.

SQLcl Alias & The Invisible Column Trick

How about we write a command in SQLcl to do this for us?
That's where ALIAS comes in. You can name a query,PL/SQL or script and provide it arguments to match bind variables.

Done! Save the pos.xml to your local drive.

https://gist.github.com/dermotoneill/b654404f112846212d4d

Open SQLcl  and load the alias

alias load c:\pos.xml

Now you can change column positions (At your own risk! This is just an example code. What happens if you already have INVISIBLE columns? What happens if your column names are case sensitive ???  I have more work to do before production)

Syntax
pos tablename columname position