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
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.
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
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
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>
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?
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.