Re: PL/SQL Ordered Insert

I am the original DBA that this post was for. You are correct you can not have an order by clause in an insert/select statement. The reason the data needs to be ordered is to ensure that it is retrieved in the same order everytime this procedure is executed. The data in the table is temporary. It will be deleted at the end of the execution so everytime there will be new data inserted by sessionid to ensure when it is selected out the user who inserted it gets only his/her rows back. The reason for the ordering is because a position is passed into the procedure which tells the procedure to retrieve back only row number 2 or 3 and this will stay constant if the data is inserted in the same order every time. I hope this sheds some light on the situation. I think I am going to use the cursor concept to accomplish this goal. Which if I do it this way there is no need for a temp table. I can just pick the record out of the loop when the counter reaches the position that was passed in. Thanks for the ideas.

• Rod Corderey <RodCorderey_at_Lane-Associates.com> wrote:
  > 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:
  > >
  > >
  > > 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
  > > also send the HELP command for other information
  > (like subscribing).
  > --
  > Author: Rod Corderey
  > INET: RodCorderey_at_Lane-Associates.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).