Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Amrinder.

Asked: September 02, 2015 - 5:12 am UTC

Last updated: October 26, 2021 - 2:32 am UTC

Version: 9i

Viewed 1000+ times

You Asked

Can we add a new column between two columns.If yes,please tell me command

and Connor said...

The column order is really of little or no consequence, so you should feel comfortable with adding it to the "end" of the table. Any code written against a table should be either

a) explicitly referencing columns by name (so the column order makes no difference)
b) using "*" into a %rowtype variable (the column order makes no difference)

So ... the column order ... makes no difference :-)

However, if you really are concerned about it (you shouldn't be), you could put a view over the top of it to sequence the columns however you like, eg

create table T ( c1 date, c2 date);

alter table T add c3 date;

create or replace view V as
select c3,c2,c1 from T;


Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

change column order

Michael, September 02, 2015 - 9:17 am UTC

you could play with invisible columns. making columns invisible + visible again makes it the last column.


SQL> create table test (col1 number, col2 number, col3 number);

Table created.

SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NUMBER
COL2 NUMBER
COL3 NUMBER

SQL> alter table test modify col2 invisible;

Table altered.

SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NUMBER
COL3 NUMBER

SQL> alter table test modify col2 visible;

Table altered.

SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NUMBER
COL3 NUMBER
COL2 NUMBER


Connor McDonald
September 03, 2015 - 5:34 am UTC

True, but the version specified was 9i. Invisible columns come along a lot later than that :-)

I would vote for such a feature

nomadus, February 06, 2018 - 5:47 pm UTC

I have seen such a request many times, not sure why Oracle still not implemented it.
Let me explain, we have conventions, e.g. name of methods in Java agreed to start with a lower case letter. If we break it nothing breaks, but we break the convention and thus it affects readability of the code. The same way is column ordering, I personally would like to have order_number and order_id be close to each other and who columns be at the end of table. When I see a table, where columns are not grouped/ordered by their purpose this tells me that the developer did not do his homework did not think about the model beforehand.
Technically, I agree it does not affect anything, but if the feature was there it would be great.
Connor McDonald
February 07, 2018 - 12:35 am UTC

If you are on 12c, then the routine I wrote here

https://connor-mcdonald.com/2013/07/22/12c-invisible-columns/

lets you re-sequence columns to whatever order you like.

Reg. use of Invisible columns to change column order

achal, August 06, 2021 - 10:15 am UTC

Hi Connor,

Regarding use of Invisible/Visible to change column order, there are any gotchas that one should be aware of ? Any particular thing that needs to be tested before doing this in production?

Regards,
AB
Connor McDonald
August 09, 2021 - 5:44 am UTC

1) question why you really need to do it....I mean, what real benefit do you get?

2) there have been some reports of triggers/packages not being marked for compilation when they should have been, and thus you get inconsistent results. (I havent seen that myself - just going on feedback from the community)

Regarding triggers/packages not being marked for compilation

A reader, August 17, 2021 - 9:00 am UTC

Thanks for the reply Connor.

Regarding reports of triggers/packages not being marked for compilation, So we should be fine if we re-compile all dependent triggers/packages after changing column order? Also, any idea where we can read more about this issue ?

Why we want to do it: We have to transform some columns of tables having lots of data (~100 Million to Billion rows). These tables are used extensively by third party developers. The transformation logic is time consuming and we will have a short maintenance window to do the change.

Therefore, we will add a new invisible column to the table. Data will be transformed and populated in to this new column out side the maintenance window. During maintenance window, we will swap names of the two columns and will then drop the original column. However, the new column will be created at end of the table.

This change can impact Insert statements that don't specify column names. We understand that such Inserts violate good coding practices. However, there is third party/legacy code that is outside of our control.

Thanks
AB
Connor McDonald
August 18, 2021 - 5:56 am UTC

So we should be fine if we re-compile all dependent triggers/packages after changing column order?



Yes.

Also, any idea where we can read more about this issue ?

Unknown. I just recall people making some comments on forums and twitter in the past. I can't find any explicit bug references.

Regarding triggers/packages not being marked for compilation

Rajeshwaran Jeyabal, August 18, 2021 - 1:15 pm UTC

Here is one such inconsistent behaviour from 18c XE version.
both procedure (P1 and P2) were identical, except P1 uses ANSI join, while P2 use Oracle syntax.

Instead of "select *..." in the cursor, if you got expicit reference to columns, then we dont end up with ORA-00932

demo@XEPDB1> create table t1( x1 int, y1 varchar2(20) );

Table created.

demo@XEPDB1> create table t2( x2 int, y2 varchar2(20) );

Table created.

demo@XEPDB1>
demo@XEPDB1> insert into t1(x1,y1) values(1,'A');

1 row created.

demo@XEPDB1> insert into t2(x2,y2) values(2,'B');

1 row created.

demo@XEPDB1> commit;

Commit complete.

demo@XEPDB1> create or replace procedure p1
  2  as
  3     cursor c1 is select *
  4             from t1 join t2
  5             on t1.x1 = t2.x2 ;
  6     r c1%rowtype;
  7  begin
  8     open c1;
  9     fetch c1 into r;
 10     close c1;
 11
 12  exception
 13     when others then
 14             if c1%isopen then
 15             close c1;
 16             end if;
 17             raise ;
 18  end;
 19  /

Procedure created.

demo@XEPDB1> create or replace procedure p2
  2  as
  3     cursor c1 is select *
  4             from t1,t2
  5             where t1.x1 = t2.x2 ;
  6     r c1%rowtype;
  7  begin
  8     open c1;
  9     fetch c1 into r;
 10     close c1;
 11
 12  exception
 13     when others then
 14             if c1%isopen then
 15             close c1;
 16             end if;
 17             raise ;
 18  end;
 19  /

Procedure created.

demo@XEPDB1> exec p1;

PL/SQL procedure successfully completed.

demo@XEPDB1> exec p2;

PL/SQL procedure successfully completed.

demo@XEPDB1> col object_name for a10
demo@XEPDB1> select object_name,object_type,status
  2  from user_objects
  3  where object_name in ('P1','P2');

OBJECT_NAM OBJECT_TYPE             STATUS
---------- ----------------------- -------
P1         PROCEDURE               VALID
P2         PROCEDURE               VALID

demo@XEPDB1> alter table t1 add Z varchar2(80);

Table altered.

demo@XEPDB1> select object_name,object_type,status
  2  from user_objects
  3  where object_name in ('P1','P2');

OBJECT_NAM OBJECT_TYPE             STATUS
---------- ----------------------- -------
P1         PROCEDURE               VALID
P2         PROCEDURE               INVALID

demo@XEPDB1> exec p1;
BEGIN p1; END;

*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at "DEMO.P1", line 17
ORA-06512: at "DEMO.P1", line 9
ORA-06512: at line 1


demo@XEPDB1> exec p2;

PL/SQL procedure successfully completed.

demo@XEPDB1> select object_name,object_type,status
  2  from user_objects
  3  where object_name in ('P1','P2');

OBJECT_NAM OBJECT_TYPE             STATUS
---------- ----------------------- -------
P1         PROCEDURE               VALID
P2         PROCEDURE               VALID

demo@XEPDB1> select banner_full from v$version;

BANNER_FULL
----------------------------------------------------------------------------
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0


demo@XEPDB1>

Regarding triggers/packages not being marked for compilation

Rajeshwaran Jeyabal, August 19, 2021 - 6:44 am UTC

Sorry- Here is the correct testcase with invisible columns in place.
drop table t1 purge;
drop table t2 purge;
create table t1( x1 int, y1 varchar2(20) );
create table t2( x2 int, y2 varchar2(20) );

insert into t1(x1,y1) values(1,'A');
insert into t2(x2,y2) values(2,'B');

create or replace procedure p1
as
  cursor c1 is select *
    from t1 join t2
    on t1.x1 = t2.x2 ;
  r c1%rowtype;
begin
  open c1;
  fetch c1 into r;
  close c1;
exception
  when others then
    if c1%isopen then
    close c1;
    end if;
    raise ;
end;
/
   
create or replace procedure p2
as
  cursor c1 is select *
    from t1,t2
    where t1.x1 = t2.x2 ;
  r c1%rowtype;
begin
  open c1;
  fetch c1 into r;
  close c1;
exception
  when others then
    if c1%isopen then
    close c1;
    end if;
    raise ;
end;
/   

demo@XEPDB1> exec p1;

PL/SQL procedure successfully completed.

demo@XEPDB1> exec p2;

PL/SQL procedure successfully completed.

demo@XEPDB1> col object_name for a10
demo@XEPDB1> select object_name,object_type,status
  2  from user_objects
  3  where object_name in ('P1','P2');

OBJECT_NAM OBJECT_TYPE             STATUS
---------- ----------------------- -------
P1         PROCEDURE               VALID
P2         PROCEDURE               VALID

demo@XEPDB1> alter table t1 modify y1 invisible;

Table altered.

demo@XEPDB1> select object_name,object_type,status
  2  from user_objects
  3  where object_name in ('P1','P2');

OBJECT_NAM OBJECT_TYPE             STATUS
---------- ----------------------- -------
P1         PROCEDURE               VALID
P2         PROCEDURE               INVALID

demo@XEPDB1> exec p1;
BEGIN p1; END;

*
ERROR at line 1:
ORA-01007: variable not in select list
ORA-06512: at "DEMO.P1", line 16
ORA-06512: at "DEMO.P1", line 9
ORA-06512: at line 1


demo@XEPDB1> exec p2;

PL/SQL procedure successfully completed.

demo@XEPDB1> exec p1;
BEGIN p1; END;

*
ERROR at line 1:
ORA-01007: variable not in select list
ORA-06512: at "DEMO.P1", line 16
ORA-06512: at "DEMO.P1", line 9
ORA-06512: at line 1


demo@XEPDB1> select object_name,object_type,status
  2  from user_objects
  3  where object_name in ('P1','P2');

OBJECT_NAM OBJECT_TYPE             STATUS
---------- ----------------------- -------
P1         PROCEDURE               VALID
P2         PROCEDURE               VALID

demo@XEPDB1> alter table t1 modify y1 visible;

Table altered.

demo@XEPDB1> select object_name,object_type,status
  2  from user_objects
  3  where object_name in ('P1','P2');

OBJECT_NAM OBJECT_TYPE             STATUS
---------- ----------------------- -------
P1         PROCEDURE               VALID
P2         PROCEDURE               INVALID

demo@XEPDB1> exec p1;

PL/SQL procedure successfully completed.

demo@XEPDB1> exec p2;

PL/SQL procedure successfully completed.

demo@XEPDB1>


Connor McDonald
August 23, 2021 - 2:11 am UTC

Thanks Rajesh ... no wonder I couldn't repo, I never use ANSI joins :-)

Bug 33262666 logged

Not able to find Bug 33262666 on support.oracle

A reader, October 25, 2021 - 2:17 pm UTC

Hi Team,

I am not able to find Bug 33262666 on support.oracle.com . Any idea if I am missing anything?
Connor McDonald
October 26, 2021 - 2:32 am UTC

Probably means not published to the general public.

This is (one reason) we always prefer customers to log SR's and get bugs associated with SRs - more visibility and generally the bugs become public as well.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library