Generate random string in PL / SQL (oracle 9i) with limited letters

Question

I am trying to create a random string using PL / SQL with some limited letters, because I want to avoid letters like O, o, 0, etc. I heard that this task can be achieved with the listagg function available in oracle 11g. But I want to achieve this using Oracle 9i, as we use it in our environment. Any suggestions would be appreciated.

Answer 1

Refusing ABCade's answer (it goes a little too far from the original to store comments):

select xmlagg(xmlelement("r", ch)).extract('//text()').getstringval() from ( select distinct first_value(ch) over (partition by lower(ch)) as ch from ( select substr('abcdefghijklmnpqrstuvwxyzABCDEFGHIJKLMNPQRSTUVWXYZ123456789', level, 1) as ch from dual connect by level <= 59 order by dbms_random.value ) where rownum <= dbms_random.value(10,13) ); 

SQL Fiddle

An internal selection puts each character in a string of values ​​in random order; on the next level, first_value() and distinct are used to select what the uppercase and lowercase pairs ( a / a ) see first, so you won’t get a repetition, even ignoring the case; which are then limited to the first 10, 11 or 12 (separate) lines; and finally, it uses the same xmlagg call to turn these lines into one line.

Answer 2

One approach would be to create a custom aggregate function that mimics the behavior of the listagg function. Here is an example:

• Our custom aggregate function:

    SQL> create or replace type stragg as object ( 2 3 str varchar2(4000), 4 5 static function ODCIAggregateInitialize(sctx in out stragg) 6 return number, 7 8 member function ODCIAggregateIterate (self in out stragg, 9 value in varchar2 ) 10 return number, 11 12 member function ODCIAggregateTerminate (self in stragg , 13 return_value out varchar2, 14 flags in number ) 15 return number, 16 17 member function ODCIAggregateMerge(self in out stragg, 18 ctx2 in stragg ) 19 return number 20 ) 21 / Type created SQL> create or replace type body stragg is 2 3 static function ODCIAggregateInitialize(sctx in out stragg) 4 return number is 5 begin 6 sctx := stragg(null); 7 return ODCIConst.Success; 8 end; 9 10 member function ODCIAggregateIterate( 11 self in out stragg, value in varchar2) 12 return number is 13 begin 14 str := str || value; 15 return ODCIConst.Success; 16 end; 17 18 member function ODCIAggregateTerminate(self in stragg, 19 return_value out varchar2, flags in number) return number is 20 begin 21 return_value := str; 22 return ODCIConst.Success; 23 end; 24 25 member function ODCIAggregateMerge(self in out stragg, 26 ctx2 in stragg) return number is 27 begin 28 str := str || ctx2.str; 29 return ODCIConst.Success; 30 end; 31 end; 32 / Type body created SQL> create or replace function str_agg (input varchar2) return varchar2 2 parallel_enable aggregate using stragg; 3 / Function created 

If you plan to work with strings longer than 4000, you can use the CLOB type datatype instead of varchar2 . Now you can create an additional function that will generate a random string for you using this str_agg aggregate function. Since you want to exclude some characters from the resulting string, I think it would be better to provide a set of characters from which the desired string will be generated.

 SQL> create or replace function Str_gen(p_CharSet varchar2, p_length number) 2 return varchar2 3 is 4 l_res_str varchar2(4000); 5 begin 6 select str_agg(symbol) 7 into l_res_str 8 from ( select substr(p_CharSet, 9 dbms_random.value(1, length(p_charset)), 1) as symbol 10 from dual 11 connect by level <= p_length 12 order by dbms_random.value 13 ); 14 15 return l_res_str; 16 end; 17 / Function created 

And here is how it works:

 SQL> select str_gen('abcdefghijklmnpqrstuvwxyzABCDEFGHIJKLMNPQRSTUVWXYZ123456789',11) as res 2 from dual 3 connect by level <= 11 4 / RES -------------------------------------------------------------------------------- NBG5jK6G46G fSrzmyq7ZLE vdGE1dRXlah 1D2IsI54qzD PhktBAh5rXu JtRsarxFNiV 1sUGFpwmypQ 7giwfdV4I7s I2WMhKzxvc2 NZpngmrq1gM rFuZ8gSUDgL 11 rows selected 

Answer 3

Try using DBMS_RANDOM to generate random rows.

Example: dbms_random.string('x',10) contains a lowercase alphanumeric string of length 10.

But you cannot exclude certain characters. You will need to use the TRANSLATE or REPLACE function to remove unwanted characters.

Answer 4

 create or replace function str_gen ( len in number) return varchar2 as str varchar2(4000); valid_chars constant varchar2(50) := 'abcdefghijklmnpqrstuvwxyz'; begin for i in 1..len loop str := str || substr( valid_chars, dbms_random.value(1, length(valid_chars)), 1); end loop; return str; end; / 

---

Without directly repeating the same letters

 create or replace function str_gen ( len in number) return varchar2 as str varchar2(4000); valid_chars constant varchar2(50) := 'abcdefghijklmnpqrstuvwxyz'; last_char varchar2(1); new_char varchar2(1); chars_len NUMBER; num NUMBER; begin chars_len := length(valid_chars); num := 0; if len > 0 then loop new_char := substr( valid_chars, dbms_random.value(1, chars_len ), 1); if num = 0 then str := new_char; num := num +1; elsif new_char != last_char then str := str || new_char; num := num +1; end if; last_char := new_char; exit when num = len; end loop; end if; return str; end; / select str_gen(11) from dual;