Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Creating data with dbms_random

BeefStuJun 22 2022

I'm putting together sample test data using SQL trying to populate the table below but I appear to be running into some syntax errors that I can't figure out.

I was hoping someone can help me out. Thanks in advance to all who respond and for your expertise.

CREATE TABLE students (
   student_id  number(*,0),
  first_name VARCHAR(25) NOT NULL,
  last_name VARCHAR(25) NOT NULL,
  active VARCHAR2(1) DEFAULT 'Y',
constraint student_pk primary key (student_id));
   insert into students (student_id, first_name, last_name, active)
WITH cte AS ( SELECT  level lvl FROM dual CONNECT BY level <= 5 )
     SELECT rownum, case t1.lvl
            WHEN 1 THEN 'Faith' 
            WHEN 2 THEN 'Tom' 
            WHEN 3 THEN 'Anna'
            WHEN 4 THEN 'Lisa' 
            WHEN 5 THEN 'Andy' 
            end first_name,
         
        SELECT rownum, case t1.lvl
            WHEN 1 THEN 'Andrews' 
            WHEN 2 THEN 'Thorton' 
            WHEN 3 THEN 'Smith'
            WHEN 4 THEN 'Jones' 
            WHEN 5 THEN 'Beirs' 
            end END last_name,
      
       CASE MOD(LEVEL, 5)
           WHEN 0
          THEN 'N'
          ELSE 'Y'
       end
FROM 
(SELECT lvl FROM cte 
ORDER BY dbms_random.value() ) t1;
      
This post has been answered by BluShadow on Jun 22 2022
Jump to Answer

Comments

Post Details

Added on Jun 22 2022
13 comments
164 views