Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: PL/SQL Ordered Insert
I might be wrong, but you can't have an order by in an
"insert into select ..from" statement
although you can do it using an ordered cursor and single row inserts.
But having done all that the table would not be ordered as there is no guarantee of data retrieval without an order by or a group by.
The exception to this is an index_organised table but be aware of limitations in using this table, in that additional indexes may not be created. [I think that is still the case in 8.1.6 but haven't checked]
I am more interested in why the table needs to be ordered in the first place.
Surely if the indexation is fit-for-purpose then the notional ordering of the rows is still impacted by the physical distribution of the data.
Like Stephen, I may be missing something.
cheers
Rod
-- Rod Corderey Lane Associates RodCorderey_at_Lane-Associates.com http://www.Lane-Associates.com "Manning, Stephen (calanais)" wrote:Received on Wed Aug 16 2000 - 16:04:43 CDT
>
>
> Wouldn't he just need to ORDER BY the source data? Or am I missing
> something.
>
> Stephen
>
> -----Original Message-----
> Sent: 16 August 2000 19:21
> To: Multiple recipients of list ORACLE-L
>
> A fellow DBA has a need to insert records into
> a temp table (in order) via a stored proc. Does
> anyone know a way to accomplish this?
>
> Apparently, SQL-Server has this ability and they're
> in the process of converting from SQL-Server to Oracle.
>
> TIA
> -----
> Stewart McGlaughlin
> Oracle DBA
> On-site at AT&T Wireless Services
> Oracle Links @ http://www.stewartmc.com/oracle
> stewartm_at_iname.com
>
> --
> Author: McGlaughlin,Stewart
> INET: mcglaughlin_at_attws.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Author: Manning, Stephen (calanais)
> INET: Stephen.Manning_at_calanais.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may