I'm trying to generate a Random String using PL/SQL with some restricted letters, because i want to avoid letters like O, o, 0, etc. I heard that this task can be achievable using the listagg function available in oracle 11g. But i want to achieve this using Oracle 9i as we are using this in our environment. Any suggestions will be appreciated.
Riffing off A.B.Cade's answer (it's getting a bit too far away from the original to keep in comments):
select xmlagg(xmlelement("r", ch)).extract('//text()').getstringval()
from
(
select distinct first_value(ch) over (partition by lower(ch)) as ch
from (
select substr('abcdefghijklmnpqrstuvwxyzABCDEFGHIJKLMNPQRSTUVWXYZ123456789',
level, 1) as ch
from dual
connect by level <= 59
order by dbms_random.value
)
where rownum <= dbms_random.value(10,13)
);
The inner select is putting each character in the string of values into a random order; the next level uses first_value() and distinct to pick whichever it sees first of an uppercase and lowercase pair (a/A) so you don't get repetition even ignoring case; that's then restricted to the first 10, 11 or 12 (distinct) rows; and finally it uses the same xmlagg call to turn those rows into a single string.