asked on

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:
'cDGADBeGAF'

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

dfu23

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 ...

ASKER CERTIFIED SOLUTION

RCorfman

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

konektor

have a look at : http://orsweb.com/downloads/source/372.html, u can use it to generate number, charatcters, ...

konektor

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;

Slowang

ASKER

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