Generating Random String in PL/SQL(oracle 9i) with restricted Letters

Question

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.

Answer 1

Try using DBMS_RANDOM package to generate random strings.

Ex : dbms_random.string('x',10) gives uppercase alphanumeric character string of length 10.

But you cant exclude specific characters. You will have to use TRANSLATE or REPLACE functions to remove the unwanted characters.