?
Solved

Random String Gerneration in Oracle 9i

Posted on 2006-03-23
5
Medium Priority
?
1,054 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:Slowang
5 Comments
 
LVL 14

Expert Comment

by:dfu23
ID: 16277733
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 ...
0
 
LVL 16

Accepted Solution

by:
RCorfman earned 400 total points
ID: 16278156
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;
begin
 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 ||
    substr(charset,round(dbms_random.value(1,length(charset))),1);
 end loop;
 return random_string;
end;
/

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;

RANDOM_STRING('ABC',10)
----------------------------------------------------------------------------
ABBCBBCABC

SQL> /

RANDOM_STRING('ABC',10)
----------------------------------------------------------------------------
BBCBBABAAC

SQL> /

RANDOM_STRING('ABC',10)
----------------------------------------------------------------------------
BABCABCBBB

SQL> /

RANDOM_STRING('ABC',10)
----------------------------------------------------------------------------
CBBCCCCCCA

SQL> /

RANDOM_STRING('ABC',10)
----------------------------------------------------------------------------
CBCBBCBCCB

SQL> /

RANDOM_STRING('ABC',10)
----------------------------------------------------------------------------
ACACCBABBB

SQL> /

RANDOM_STRING('ABC',10)
----------------------------------------------------------------------------
BCBBCBBBBA

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.

Enjoy.
0
 
LVL 9

Expert Comment

by:konektor
ID: 16278716
have a look at : http://orsweb.com/downloads/source/372.html, u can use it to generate number, charatcters, ...
0
 
LVL 9

Expert Comment

by:konektor
ID: 16278745
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;
0
 

Author Comment

by:Slowang
ID: 16296259
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
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question