Random String Gerneration in Oracle 9i

Dear All,

I need to generate random strings from a given charset, and length of string in PL/SQL Oracle 9i.

for example,
charset = 'ABcDeFG'
length = 10
A possible random string output would be:

I had a look at the dbms_random.string function.
Unfortunately, this function only returns a random string but it doesn't gerenate the string from a given charset.

Can experts please give me a hand?

Thanks in advance
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

can you easily generate a random integer with a range?

if you had an array of characters that held your charset you could loop the number of times that your string length needs to be.

array of chars [A, B, c, D, e, F, G]
string length = 10
new string = ''

while new string length < 10
  generate random number between 0 and upper bound of array of chars
  new string = new string + chararray[random number]

Unfortunately, my knowledge of PL-SQL isn't that extensive so I'm not really sure how easy/hard this would be ...
I believe this is a completely bullet proof function....

create or replace function random_string(charset in varchar2,string_length in number)
return varchar2 is
random_string varchar2(2000);
charset_length number;
 if string_length not between 1 and 2000 then
   raise_application_error(-20001,'Length must be between 1 and 2000');
 end if;
 charset_length := length(charset);
 if charset is null then raise_application_error(-20002,'Charset must have data');
 end if;
 for n in 1 .. string_length loop
  random_string := random_string ||
 end loop;
 return random_string;

And an execution script to prove it out....

Function created.

SQL> select random_string(null,10) from dual;
select random_string(null,10) from dual
ERROR at line 1:
ORA-20002: Charset must have data
ORA-06512: at "EVTREAD.RANDOM_STRING", line 10
ORA-06512: at line 1

SQL> select random_string('ABC',null) from dual;
select random_string('ABC',null) from dual
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "EVTREAD.RANDOM_STRING", line 12

SQL> select random_string('ABC',-5) from dual;
select random_string('ABC',-5) from dual
ERROR at line 1:
ORA-20001: Length must be between 1 and 2000
ORA-06512: at "EVTREAD.RANDOM_STRING", line 7

SQL> select random_string('ABC',10) from dual;


SQL> /


SQL> /


SQL> /


SQL> /


SQL> /


SQL> /



Note that there is no attempt to assure that the same character isn't in the string more than one time... so, if you have 'AAAB' as the passed in string, you are going to likely get 3 times as many A's as B's in the return string.... I suspect this is ok though.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
have a look at : http://orsweb.com/downloads/source/372.html, u can use it to generate number, charatcters, ...
if u want to use special characterset, define it eg as:
"my characterset" VARCHAR(100):='AaBbCcDdEeFf0123456789'
than use
for i in 1..<wanted length of string> loop
  "random num" := generate random number from 1 till lendth of "my characterset";
  "random string" := "random string" || substr("my characterset","random number",1);
end loop;
SlowangAuthor Commented:
Thanks dudes,

Instead of writing my own functions, I decided to use the dbms_random.string function with the translate function.

After the dbms_random.string function returns a random string, the translate functionto replace the chars that i dont want.

Well, the drawback is that the replacement chars are hardcode, but anyway, it solve my problem~

Thanks again
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.