Home » SQL & PL/SQL » SQL & PL/SQL » Can I replace the column position?
Can I replace the column position? [message #301325] Wed, 20 February 2008 01:12 Go to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Hi all!

I have wondered about the column position
SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------

 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL>


And, can I replace the column JOB into COMM's position?

Thank you for your reply!
Re: Can I replace the column position? [message #301333 is a reply to message #301325] Wed, 20 February 2008 01:34 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Not without re-creating the table. As a general rule, the order of columns in a table is irrelevant
Re: Can I replace the column position? [message #301334 is a reply to message #301325] Wed, 20 February 2008 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And why do you want to do this?

Regards
Michel
Re: Can I replace the column position? [message #301416 is a reply to message #301325] Wed, 20 February 2008 09:30 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I too am curious. I am always amused when this question is asked.

The other thing people do when they want to re-order columns, is use a view.

create or replace view vw_diff_col_order
as
, DEPTNO
, SAL
, COMM
, HIREDATE
, MGR
, JOB
, ENAME
, EMPNO
from test
/


Good luck, Kevin
Re: Can I replace the column position? [message #301424 is a reply to message #301416] Wed, 20 February 2008 09:59 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, the one place where a "sane" column order would make sense is in development tools (even when it's just DESC in SQL*Plus), where work is a lot of easier when logically related columns are grouped together, not scattered all over the place. Wink

For that I miss an option in the development tools I have used so far to sort columns alphabetically, or even sort them by the column comment.
Re: Can I replace the column position? [message #301426 is a reply to message #301325] Wed, 20 February 2008 10:06 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I gave up long ago worrying about "most efficient column orders" and now make all my tables and views have columns in alphabetical order.

Some people hate it (the primary key is supposed to be at the beginning, followed by foreign keys, followed by all other not null columns, followed by everything else).

Some people love it (wow, sure is easy to find what I need when looking at your stuff Kev, wish those other people would do this).

Good luck, Kevin
Re: Can I replace the column position? [message #301436 is a reply to message #301426] Wed, 20 February 2008 11:03 Go to previous messageGo to next message
amcghie
Messages: 35
Registered: March 2005
Location: Sunny Dubai
Member
I'd have to agree as to why would you want to do this? But if OCD got the better of you, something like this would work:

ALTER TABLE emp ADD
  ( comm_tmp     NUMBER(7,2)
  , mgr_tmp      NUMBER(4)
  , hiredate_tmp DATE
  , sal_tmp      NUMBER(7,2)
  , job_tmp      VARCHAR2(9)
  , deptno_tmp   NUMBER(2) );
  
UPDATE emp
SET    comm_tmp      = comm
     , mgr_tmp       = mgr
     , hiredate_tmp  = hiredate
     , sal_tmp       = sal
     , job_tmp       = job
     , deptno_tmp    = deptno;
     
ALTER TABLE emp DROP
  ( comm, mgr, hiredate, sal, job, deptno );
  
ALTER TABLE emp RENAME COLUMN comm_tmp TO comm;
ALTER TABLE emp RENAME COLUMN mgr_tmp TO mgr;
ALTER TABLE emp RENAME COLUMN hiredate_tmp TO hiredate;
ALTER TABLE emp RENAME COLUMN sal_tmp TO sal;
ALTER TABLE emp RENAME COLUMN job_tmp TO job;
ALTER TABLE emp RENAME COLUMN deptno_tmp TO deptno;

Or
CREATE TABLE emp_new AS
  SELECT empno
       , ename
       , comm
       , mgr
       , hiredate
       , sal
       , job
       , deptno
   FROM  emp;
DROP TABLE emp;
RENAME TABLE emp_new TO emp;

Either way you'd have to also recreate any foreign keys / indexes on the columns you have dropped which could get VERY messy!

As other ppl have suggested create a view OR create a custom describe script that is saved in the SQLPATH directory.

Andy
Re: Can I replace the column position? [message #301438 is a reply to message #301436] Wed, 20 February 2008 11:17 Go to previous messageGo to next message
amcghie
Messages: 35
Registered: March 2005
Location: Sunny Dubai
Member
I've actually thought of one reason (and I am clutching at straws) why you may want to do this. Consider the scenario...

...Some idiot accidently drops a column from one of your tables and some other idiot (it was probably the same guy) had written an INSERT statement where the columns are not specified. Unfortunately you no longer have the source code for the compiled binaries where the INSERT statement resides.

Even then I think I'd probably rename the table and create a view with the original table name.
Re: Can I replace the column position? [message #301446 is a reply to message #301438] Wed, 20 February 2008 12:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Unfortunately you no longer have the source code for the compiled binaries where the INSERT statement resides.

You mean some idiot delete all sources and their backups?

Regards
Michel
Re: Can I replace the column position? [message #301586 is a reply to message #301446] Thu, 21 February 2008 01:36 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank for all!

Exactly, I've not cared about this till now. One person who work with me in the office ask for changing the column position. And I ask why, she said "There are many tables associated with inserting statements into them, some statement I can re-write, other else I can not, the statement I can re-write are in local machine or in the package which I can see, but the other else are in the wrapped package, then I can not do anything."

An example:
I have one table named BILLING with
CUSTOMER_ID,
CUSTOMER_NAME,
TELEPHONE,
PERCENTAGE_COMMISSION
columns. And one statement was called in the wrapped package that update every day to the table like

Update BILLING 
set CUSTOMER_ID = 100, 
CUSTOMER_NAME='CUSTOMER',
PERCENTAGE_COMMISSION=500
TELEPHONE='123456';  


Of cource, the values are called by the other module, there is example values above.

Code of statement is not re-write, due to the position, you know that the values will be update wrong, and it's the reason I wondered about the column position.

Thank you very much!



[Updated on: Thu, 21 February 2008 01:42]

Report message to a moderator

Re: Can I replace the column position? [message #301602 is a reply to message #301586] Thu, 21 February 2008 02:32 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
The code that you supply would not care what position columns are in. Are you saying that you have wrapped packages without source code? i.e. can you/your 'colleague' not go back to the original code and fix it, test it, wrap and recompile it? Do you have inserts that do not include column lists? (If you do, then you should be fixing this anyway)
Anyway, use a view to do your bidding. Rename your table, then create a view on top with the old table name and the columns in the appropriate order.
Re: Can I replace the column position? [message #301607 is a reply to message #301602] Thu, 21 February 2008 02:41 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you, pablolee!

I meant that both of me and my colleague did not recompile/re-write/change/etc the source code which was in some wrapped package before.

Hmm, so I searched something related in Google plus your answere here, I understood that, I should re-create table. Really, I do not like to do it, because of many triggers, constraints will be re-created associately.

Thank you for your reply!
Re: Can I replace the column position? [message #301610 is a reply to message #301607] Thu, 21 February 2008 02:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Exactly what happens now, without the recreation of the table? Do you get errors? If so, which?
If not, why bother?
Re: Can I replace the column position? [message #301613 is a reply to message #301610] Thu, 21 February 2008 03:03 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
I am getting an error, no, for logically, it's not an error, but for the apllication returned, it is error when I got
Quote:

CUSTOMER_ID = 100
CUSTOMNER_NAME='Alan'
TELEPHONE=10
PERCENTAGE_COMMISSION=84904352121



That, the row in TELEPHONE column was updated must be 84904352121 and the PERCENTAGE_COMMISSION must be 10.

Thank you!
Re: Can I replace the column position? [message #301615 is a reply to message #301613] Thu, 21 February 2008 03:06 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
And this is why you should include a column list when writing INSERTs
(I also notice that there is clearly no data validation on your columns)

[Updated on: Thu, 21 February 2008 03:09]

Report message to a moderator

Re: Can I replace the column position? [message #301616 is a reply to message #301615] Thu, 21 February 2008 03:10 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
pablolee wrote on Thu, 21 February 2008 16:06
And this is why you should include a column list when writing INSERTs
(I also notice that there is clearly no data validation on your columns)


Yeap, if I can re-write the code Razz
Re: Can I replace the column position? [message #301619 is a reply to message #301616] Thu, 21 February 2008 03:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So do what pablolee said: rename the table, create a view with the previous name on this table with the expected columns order.

Regards
Michel
Re: Can I replace the column position? [message #301621 is a reply to message #301619] Thu, 21 February 2008 03:19 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
And then find the original unwrapped code and fix it, wrap it, compile it, then make various threats of violence against anyone who writes that kind of code again Smile
Re: Can I replace the column position? [message #301622 is a reply to message #301621] Thu, 21 February 2008 03:20 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you Michel and Pablolee!

Re: Can I replace the column position? [message #301636 is a reply to message #301613] Thu, 21 February 2008 03:46 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
trantuananh24hg wrote on Thu, 21 February 2008 10:03

That, the row in TELEPHONE column was updated must be 84904352121 and the PERCENTAGE_COMMISSION must be 10.

In addition to pablolees and Michels advises:
The above is caused by an INSERT, not by an UPDATE. Make sure you distinguish between INSERT and UPDATE, or it will be very hard for people to help you.
Previous Topic: What is Global Temporary Table?
Next Topic: log miner is not displaying the result in text format
Goto Forum:
  


Current Time: Wed Dec 07 14:38:59 CST 2016

Total time taken to generate the page: 0.12018 seconds