Re: HELP: Sorting in INSERT-statements

In article <4655fh$6ba_at_daneel.sds.no>, Håvard D. Jørgensen <havard.dingstad_jorgensen_at_sds.no> wrote:
>
>We are currently developing a client/server application that is to use
>different DBMSs (Oracle, Sybase, Watcom etc.) in various installations.
>For partitioning the printing of large reports, and to be able to
>resume printing from the middle of a report after failure, we want to
>store the keys of the rows for the report in an ordered table.
>The column "seq_no" in this table represents the position of the row in
>the report. This (or a similar) SQL works with other DBMSs:
>
> INSERT INTO report_table ( seq_no, a_column, another_column)
> SELECT sequence_a.NEXTVAL, a_column, another_column
> FROM table_A
> ORDER BY a_column ASC
>
>Oracle, however, does not allow an ORDER-clause in INSERT statements.
>We could solve the problem using Cursors, but we fear the performance
>of such a solution.
>
>Does anybody know a way around this problem ?
>Any help would be greatly appreciated.
>
Actually, there is NO way to guarantee that Oracle will store rows in any particular order. This question comes up a lot in this newsgroup. Ordering must be done in the SELECT statement when you extract the data. Don't let anyone tell you differently. No matter what scheme you think you've come up with to order data on inserts, there is no guarantee that the data was stored in that order.

Vince Cross

-- 
* disclaimer - My views respresent NT/BNR in every way, NOT!
NOTE - my real email address is bartok_at_bnr.ca, NOT vcross_at_bnr.ca
* non-work related email to vlcross_at_aol.com (Having an AOL account doesn't
  make me a complete idiot. Excessive drinking makes me one.)