Re: PL/SQL Ordered Insert

It seems that maybe a PL/SQL table makes more sense than a temp table. You have your rows available via a nifty indexed access, and they are extremely easy to manipulate as well.

Good Luck
Steve McClure
----- Original Message -----
From: james ellis <jellis24_gso_at_yahoo.com> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Thursday, August 17, 2000 7:04 AM
Subject: 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).
>
>
> __________________________________________________
> Do You Yahoo!?
> Send instant messages & get email alerts with Yahoo! Messenger.
> http://im.yahoo.com/
> --
> Author: james ellis
> INET: jellis24_gso_at_yahoo.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
Received on Thu Aug 17 2000 - 13:10:40 CDT