Home » SQL & PL/SQL » SQL & PL/SQL » Row Order in External Table
Row Order in External Table [message #296285] Fri, 25 January 2008 09:49 Go to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Hello all,

I'm using external tables to access flat files at some places, and now I have to do a few additions to the program logic that would only work if the select from the external table returns the rows in the same order as they are in the file.

I have tested it a quite often and it was always the case, but I haven't found any definite answer in the documentation that that will ALWAYS be the case, and also will stay that way in future version.

Neither in the Administrator Guide nor in the SQL Reference

A Google for "external table" "row order" oracle also wasn't very helpful.

Has anyone seen a definite answer somewhere, or encountered a situation where an "select *" from an external table hasn't returned the rows in the order of the lines in the flat file?
Re: Row Order in External Table [message #296289 is a reply to message #296285] Fri, 25 January 2008 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't find anything because it is not the case.
As always if you want a specific order then use an ORDER BY clause.
And this is a definitive answer.

Regards
Michel

[Updated on: Fri, 25 January 2008 10:33]

Report message to a moderator

Re: Row Order in External Table [message #296298 is a reply to message #296289] Fri, 25 January 2008 11:13 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
But is there an "order by" clause for external tables that would return the rows in the order of the external file, or a way to add a "line number" as a column for the external tables??

A lot of examples I have seen seem to depend on the rows being returned in the order of the external file, like the " External Table For Viewing Alert Logs" here, which uses an "order by rownum" to get the last 200 lines of the file.



Re: Row Order in External Table [message #296300 is a reply to message #296298] Fri, 25 January 2008 11:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But is there an "order by" clause for external tables that would return the rows in the order of the external file, or a way to add a "line number" as a column for the external tables??

Yes, as for SQL*Loader, you have the RECNUM parameter.
See Database Utilities
Chapter 9 Field List Reference
Section Using SQL*Loader to Generate Data for Input
Subsection Setting a Column to the Datafile Record Number

Regards
Michel
Re: Row Order in External Table [message #296302 is a reply to message #296300] Fri, 25 January 2008 11:29 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Thanks, I just tried it and it works perfectly. Very Happy

Have a nice weekend, Michel.
Previous Topic: Handling different datatypes
Next Topic: comments on approach
Goto Forum:
  


Current Time: Sun Dec 04 10:40:27 CST 2016

Total time taken to generate the page: 0.11998 seconds