Random String Gerneration in Oracle 9i

Posted on 2006-03-23
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:

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
Question by:Slowang
    LVL 14

    Expert Comment

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

    Accepted Solution

    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.

    LVL 9

    Expert Comment

    have a look at :, u can use it to generate number, charatcters, ...
    LVL 9

    Expert Comment

    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;

    Author Comment

    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

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now