Oracle doesn't specify an order when it stores rows. If you want the data =
returned in a specific order, user the ORDER clause on the cursor select =
that you will use to access the temp table.
>>> james ellis <jellis24_gso_at_yahoo.com> 08/17/00 10:04AM >>>
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.=20
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=20
> "insert into select ..from" statement
> although you can do it using an ordered cursor and
> single row inserts.
>=20
> But having done all that the table would not be
> ordered as there is no=20
> guarantee of data retrieval without an order by or a
> group by.
>=20
> 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=20
> is still the case in 8.1.6 but haven't checked]
>=20
> I am more interested in why the table needs to be
> ordered in the first place.
>=20
> Surely if the indexation is fit-for-purpose then the
> notional ordering=20
> of the rows is still impacted by the physical
> distribution of the data.
>=20
> Like Stephen, I may be missing something.
>=20
> cheers
>=20
> Rod
>=20
> --=20
> Rod Corderey
>=20
> Lane Associates
> RodCorderey_at_Lane-Associates.com=20
> http://www.Lane-Associates.com=20
> =20
> "Manning, Stephen (calanais)" wrote:
> >=20
> >=20
> > Wouldn't he just need to ORDER BY the source data?
> Or am I missing
> > something.
> >=20
> > Stephen
> >=20
> > -----Original Message-----
> > Sent: 16 August 2000 19:21
> > To: Multiple recipients of list ORACLE-L
> >=20
> > 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?
> >=20
> > Apparently, SQL-Server has this ability and
> they're
> > in the process of converting from SQL-Server to
> Oracle.
> >=20
> > TIA
> > -----
> > Stewart McGlaughlin
> > Oracle DBA
> > On-site at AT&T Wireless Services
> > Oracle Links @ http://www.stewartmc.com/oracle=20
> > stewartm_at_iname.com=20
> >=20
> > --
> > Author: McGlaughlin,Stewart
> > INET: mcglaughlin_at_attws.com=20
> >=20
> > Fat City Network Services -- (858) 538-5051=20
> 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=20
> >=20
> > Fat City Network Services -- (858) 538-5051=20
> 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).
> --=20
> Author: Rod Corderey
> INET: RodCorderey_at_Lane-Associates.com=20
>=20
> 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/=20
--=20
Author: james ellis
INET: jellis24_gso_at_yahoo.com=20
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 - 10:33:04 CDT
