Column order in Select *

[prismtx]

I am generating an extract file using a "select * from table". I have been told that this will not always give me the columns in the same sequence and that I need to do a "select colname1, colname2, colname3, etc from table" in order to guarantee the results are the same each time.

Does anyone know if this is true and can you point me to a reference? I found a reference dating back to 2003, but was not sure if that was still true for Oracle 10g. I would like to avoid it because some of the tables have a couple of hundred columns in them and * is so much quicker!

 

[SantaMufasa]

Prismtx,

The only thing that could change the display order of columns in a "SELECT *..." command is if someone had done something to ALTER the structure of the table, such as "ALTER TABLE DROP COLUMN..." or "ALTER TABLE ADD..."

Whoever told you that you cannot rely upon the order of output may have been referring to rows, whose order you cannot rely upon (in the absence of an ORDER BY clause).

Does anyone know if this is true and can you point me to a reference?

It is false as far as I know (with the "ALTER TABLE..." exceptions I mentioned, above).

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[LKBrwnDBA]

On the other hand, it is GOOD PRACTICE to allways enumerate the columns in the SELECT statements.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

LK,

I agree...GOOD PRACTICE...but if I had all two hundred columns of a table to extract, I must say that I would opt for the * versus the enumeration. Saving that much time, when the alternative generates the same result, is also GOOD PRACTICE. <smile>

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[prismtx]

Thanks for the responses. I had written a shell script to do this extract on 14 files and the * made it very easy to repeat the code. I can do it with the enumeration, but it takes more time to code and means the script isn't quite as reusable, since they want to add more tables in the future.

 

[jaxtell]

would you really write that select by hand? I'd do a select from user_tab_columns to help make my select statement. It wouldn't be perfect, but its a couple minutes of extra work to get it the good way.

But I wrote an aggregate function to do fun things like that ;-)

 

[Dagon]

In case you need some help on Jaxtell's suggestion, this is what you do from sql*plus:

set pagesize 0
set feedback off
set heading off

select column_name||','
from user_tab_columns
where table_name = 'XXXX'
order by column_id

That gets you a basic column list which, apart from a trailing comma on the last line, can be pasted directly into a select statement.

 

[SantaMufasa]

Or, this slight modification of Dagon's excellent idea works without alteration. (You must run it as a script since there is an "ACCEPT...PROMPT" statement. I named my version of the script "TT_463.sql"):

set echo off
set feedback off
set pagesize 0
set feedback off
set heading off
accept tab_name prompt "Enter the table which content you wish to extract: "
spool temp.sql
prompt set heading on
prompt set pagesize 35
prompt set feedback on
prompt set linesize 200
select decode(rownum,1,'select ',',')||column_name
from user_tab_columns
where table_name = upper('&tab_name')
order by column_id
/
prompt from &tab_name
prompt /
spool off
@temp

Here is a sample execution of TT_463.sql:

@tt_463
Enter the table which content you wish to extract: s_dept
set heading on
set pagesize 35
set feedback on
set linesize 200
select ID
,NAME
,REGION_ID
from s_dept
/

        ID NAME                       REGION_ID
---------- ------------------------- ----------
        10 Finance                            1
        31 Sales                              1
        32 Sales                              2
        33 Sales                              3
        34 Sales                              4
        35 Sales                              5
        41 Operations                         1
        42 Operations                         2
        43 Operations                         3
        44 Operations                         4
        45 Operations                         5
        50 Administration                     1

12 rows selected.

Let us know if this takes care of business for you.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]