Script to generate data, SQL oracle 10g

Question

I'm trying to build a script that insert random datas into my table.

My actual script looks like that :

INSERT INTO Utilisateurs (id_utilisateur, Uti_nom, Uti_prenom, Uti_role, Uti_mdp, Uti_Statut)
SELECT
  -- here to input the id (number that increment each time)
  dbms_random.string('A', trunc(dbms_random.value(5, 50))), -- data for uti_nom
  dbms_random.string('A', trunc(dbms_random.value(5, 100))), -- data for uti_prenom
  -- randomly get 'Administrateur' or 'Utilisateur'
  dbms_random.string('X', 10), -- data for uti_mdp
  trunc(dbms_random.value(0, 1)) -- data for uti_status
FROM dual
CONNECT BY LEVEL < 100;

So if someone can help me to get the both comment line...

There's a sample, but what i really need it's the ID that increments and Uti_role (Administrateur/Utilisateur) the others fields can be generated and looks like "dsjhadakj"

id_utilisateur   Uti_nom   Uti_prenom    Uti_role        Uti_mdp      Uti_Statut
d--------+---------+---------+---------+---------+---------+---------+---------+
   1             Elche        Marco    Administrateur  Haj432Hgn         1
   2             Babo         Jules    Utilisateur     Haj432Hgn         0          
   3             Ghale        Alex     Administrateur  Haj432Hgn         1

Can you please post your table definition and some sample results of your inner query? — PM 77-1
– PM 77-1, Commented May 16, 2013 at 21:41
Please explain -- here a random to get 'Administrateur' or 'Utilisateur'. Do you mean that you want to randomly get one of these two strings? — PM 77-1
– PM 77-1, Commented May 16, 2013 at 21:48
@PM77-1 Updated, and yes that what i'm trying to explain here. — e1che
– e1che, Commented May 16, 2013 at 21:50

PM 77-1 · Accepted Answer · 2013-05-16 21:58:41Z

1

For self-incremental ID you can use LEVEL
For uti_role something like this:

CASE WHEN dbms_random.value(0, 1) > 0.5 THEN 'Administrateur' ELSE 'Utilisateur' END

Here's SQL Fiddle for just the SELECT part.

answered May 16, 2013 at 21:58

PM 77-1

13.4k21 gold badges73 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

e1che Over a year ago

Thank you ! I'll try it tomorrow and probably validate your answer ! Just another ask, if i've more than only 2 options like : administrateur, utilisateur and invité how can i do it ?

PM 77-1 Over a year ago

Have you looked at the demo (SQL Fiddle)? It runs on 11g but should be fine for 10g as well.

e1che Over a year ago

Yes i've looked and wow that's awesome , but i don't have oracle now.

e1che Over a year ago

Ok i've found something here for multiple condition

Collectives™ on Stack Overflow

Script to generate data, SQL oracle 10g

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related