Hello,
when I try the Edit command with a positional “order by” (e.g: "edit my_table ORDER BY 1 ") , it is not ordering by the FIRST column!! because for sure in the Edit command they add a hidden column in the first position ( pseudo column rowid maybe) internaly --> then “first position” of the order by is NOT my table’s first column anymore!! but it is now the SECOND one!
so:
select * from my_table ORDER BY 1 = edit my_table ORDER BY 2
“order by 1”(select clause ) is equal to order by 2 (edit command).
this can easily cause huge damages editing tables in production environment!
is this a known issue?
regards,
Aravinda
version: Toad for Oracle 12.6.0.53
It’s more of a caveat in my opinion. Automatically adjusting your order by would work for some, but long time users may already know to update their column offsets. You could write the “select rowid, *” yourself to be 100% safe.
The safest thing is to specify column name with order by, rather than position.
1 Like
There you go, I like John’s suggestion better.
I agree that you can use column name but…
if I write “order by 1” and Toad is not ordering by first column of the table… it seems a bug to me 
Well, if you do “order by 1” you are not telling Oracle “order by the first column of the table”. You are telling Oracle “order by the first column in the result set”, which, since you aren’t writing the query, you don’t have 100% control over. In this case, Toad turns your “edit tablename order by 1” into “select t.rowid, t.* from tablename t order by 1”, and now you see how that changes the meaning of your “order by 1”.
Toad needs to select the rowid to make the dataset editable. (because when you edit in the grid, Toad does “update tablename … set … where rowid = …”).
There is a technical reason why we don’t put the rowid as the last column selected, but I don’t recall what it is off the top of my head.
And I hesitate to behind the scenes change your “order by 1” to “order by 2” because that might also be construed as a bug.
There is an option to show the “rowid” column in the editable data grids. Maybe enabling that makes it easier to see, why the order is different and to avoid the errors in production environments OP mentioned.