In Oracle SQL 11g I am trying to fill a table with procedure. For some columns I need to take data randomly from predefined set of strings. How do I define such set and take data from it by random order?
2
-
"Predefined set of strings" sounds like "a table" to me. DBMS_RANDOM is a standard package in Oracle which contains functions that return random numbers. Best of luck.Bob Jarvis - Слава Україні– Bob Jarvis - Слава Україні2015-05-19 00:52:19 +00:00Commented May 19, 2015 at 0:52
-
Isn't there a way to create the set as a variable like in Java?Justin– Justin2015-05-19 00:55:11 +00:00Commented May 19, 2015 at 0:55
Add a comment
|
2 Answers
You could use a cte and dbms_random.value. Something like:
with strings as (
select 'string1' as s from dual union all
select 'string2' as s from dual union all
select 'string3' as s from dual union all
select 'string4' as s from dual
)
select <col1>,
(select s
from (select s from strings order by dbms_random.value) s
where rownum = 1
) as RandomString
from dual;
Comments
Can you give this a try,it is working.
1.Insert the list of strings in a table (strings).
2.Create a function(RANDOM) to generate random number.
3.Create a procedure(PROC_STRING) to pick a string name from (STRINGS) table using the random number generated from function(RANDOM) and then insert into (NEW_TABLE)
PROGRAM:
--Table with list of string names
Create table strings (string_id number,string_name varchar2(2000) );
--Table to store new string names in random order
Create table new_table (string_id number,string_name varchar2(2000) );
--Function to generate random numbers
create or replace function random(p_number in number)
return number
is
a number;
begin
select dbms_random.value(1,10) into a
from dual;
a := floor(a);
return a;
end;
/
delete from strings;
delete from new_table;
insert into strings values(1,'abc');
insert into strings values(2,'def');
insert into strings values(3,'ghi');
insert into strings values(4,'abc 1');
insert into strings values(5,'def 1');
insert into strings values(6,'ghi 1');
insert into strings values(7,'abc 2');
insert into strings values(8,'def 2');
insert into strings values(9,'ghi 2');
insert into strings values(10,'xyz 3');
--Procedure to pick string names randomly from strings table and insert into new_table
create or replace procedure proc_string(p_no in number)
as
s_id number;
s_name varchar2(2000);
begin
select random(1) into s_id from dual;
select string_name into s_name from strings where string_id = s_id;
insert into New_table values(s_id,s_name);
dbms_output.put_line('insert successfully completed');
commit;
Exception when others
then dbms_output.put_line('ERROR:' || SQLCODE || ' ' || SQLERRM);
end;
/
commit;
EXECUTION:
--After executing the procedure for 3 times
SQL> exec proc_string(1);
insert successfully completed
PL/SQL procedure successfully completed.
-- Random string names got inserted into newtable
SQL> select * from new_table;
STRING_ID STRING_NAME
5 def 1
3 ghi
1 abc
Let me know if you questions.
2 Comments
Justin
Thank you for your answer, but I am using the Gordon Linoff's solution. I have posted another sql problem here: stackoverflow.com/questions/30418926/oracle-sql-illegal-year I'd appreciate if you helped me with that.
mahi_0707
Sure, Will try to help there.