I am very sorry to waste your time with something that is probably VERY SIMPLE, but IT IS something I cannot understand ... I must be obfuscated
select level, lpad(to_char(level), 1000)
from dual
connect by level<=20
order by 2;
--wrong sort
With ...lpad(to_char(level), 999)... or any value less than 1000 the sort is correct...
Why? (In the Oracle version of Live SQL does not happen)
Thanks a lot for being there
Wrong sort... how? I've not got any 10g R2 instances handy.
And can't reproduce any problems on the databases I have.
If this is a major issue for you, really the solution here is:
UPGRADE!
10.2 is well over a decade old now. You really should move to a more recent version anyway.
In the meantime, does sorting on consecutive substrings of the value solve the issue?
order by substr ( str, 1, 500 ), substr ( str, 501 )