set verify off

accept system_password char prompt "Enter the password for SYSTEM: " hide

accept db_conn char prompt "Enter DB SID: "

accept user_2_create char prompt "Enter new username: "

accept new_user_pw char prompt "Enter new user's password: " hide

connect system/&&system_password@&&db_conn

create user &&user_2_create identified by &&new_user_pw;

grant create session,  create table,

      create sequence, create procedure,

      create view, create trigger to &&user_2_create;

alter user &&user_2_create default tablespace users quota 500M on users;

connect &&user_2_create/&&new_user_pw@&&db_conn;

-- code begin

create or replace package session_vars

is

session_start_time     timestamp;

session_part1_end_time timestamp;

session_part2_end_time timestamp;

session_end_time       timestamp;

end;

/

begin session_vars.session_start_time := sysdate; end;

/

 

create or replace procedure disable_foreign_keys (p_table_name varchar2) is

   CURSOR my_c

   IS

      SELECT 'alter table ' || table_name || ' disable constraint '

             || constraint_name

        FROM user_constraints

       WHERE constraint_type='R' AND status = 'ENABLED'

         AND table_name = upper(p_table_name);

   my_var   varchar2(1000);

BEGIN

   OPEN my_c;

   LOOP

      FETCH my_c INTO my_var;

      EXIT WHEN my_c%NOTFOUND;

      EXECUTE IMMEDIATE my_var;

   END LOOP;

   CLOSE my_c;

END;

/

create or replace procedure enable_foreign_keys is

   CURSOR my_c

   IS

      SELECT 'alter table ' || table_name || ' enable constraint '

             || constraint_name

        FROM user_constraints

       WHERE constraint_type='R' AND status = 'DISABLED';

   my_var   varchar2(1000);

BEGIN

   OPEN my_c;

   LOOP

      FETCH my_c INTO my_var;

      EXIT WHEN my_c%NOTFOUND;

      EXECUTE IMMEDIATE my_var;

   END LOOP;

   CLOSE my_c;

END;

/

create table fname (fname_id number,

                    fname_name varchar2(40),

                    fname_gender varchar2(1)) tablespace users;

insert into fname values (1,'Fred','M');

insert into fname values (2,'Mary','F');

insert into fname values (3,'Pat','U');

insert into fname values (4,'Hillary','F');

insert into fname values (5,'Beatriz','F');

insert into fname values (6,'Georgette','F');

insert into fname values (7,'Frank','M');

insert into fname values (8,'Geoffry','M');

insert into fname values (9,'Steven','M');

insert into fname values (10,'Beth','F');

insert into fname values (11,'Charley','M');

insert into fname values (12,'Yaqoush','M');

insert into fname values (13,'Brian','M');

insert into fname values (14,'Bethany','F');

insert into fname values (15,'Sybil','F');

insert into fname values (16,'Scarlett','F');

insert into fname values (17,'Veronica','F');

insert into fname values (18,'Michael','M');

insert into fname values (19,'Brittney','F');

insert into fname values (20,'Cher','F');

insert into fname values (21,'Angela','F');

insert into fname values (22,'Harold','M');

insert into fname values (23,'Brad','M');

insert into fname values (24,'Stuart','M');

insert into fname values (25,'Kathy','F');

insert into fname values (26,'Bill','M');

insert into fname values (27,'Mildred','F');

insert into fname values (28,'Erin','U');

insert into fname values (29,'Colly','F');

insert into fname values (30,'Lolly','F');

insert into fname values (31,'Mary-Kate','F');

insert into fname values (32,'Filmore','M');

insert into fname values (33,'Jeff','M');

insert into fname values (34,'Strove','M');

insert into fname values (35,'Bethanny','F');

insert into fname values (36,'Paula','F');

insert into fname values (37,'Binky','M');

insert into fname values (38,'Nestor','M');

insert into fname values (39,'Pauly','M');

insert into fname values (40,'Becky','F');

insert into fname values (41,'Thomas','M');

insert into fname values (42,'Xavier','M');

insert into fname values (43,'Billy','M');

insert into fname values (44,'Amanda','F');

insert into fname values (45,'Roberta','F');

insert into fname values (46,'Ona','F');

insert into fname values (47,'Gerry','M');

insert into fname values (48,'Beto','M');

insert into fname values (49,'Victor','M');

insert into fname values (50,'Valery','F');

insert into fname values (51,'Timmy','M');

insert into fname values (52,'Bob','M');

insert into fname values (53,'Jack','M');

insert into fname values (54,'Jill','F');

insert into fname values (55,'Stacy','F');

insert into fname values (56,'Norma','F');

insert into fname values (57,'William','M');

insert into fname values (58,'Thelma','F');

insert into fname values (59,'Emma','F');

insert into fname values (60,'Silvia','F');

insert into fname values (61,'Stan','M');

insert into fname values (62,'Lautauro','M');

insert into fname values (63,'Benny','M');

insert into fname values (64,'Kerry','F');

insert into fname values (65,'Jilian','F');

insert into fname values (66,'Umma','F');

insert into fname values (67,'Ken','M');

insert into fname values (68,'Wanda','F');

insert into fname values (69,'Lolly','F');

insert into fname values (70,'Pricilla','F');

insert into fname values (71,'Mark','M');

insert into fname values (72,'Roger','M');

insert into fname values (73,'Morry','M');

insert into fname values (74,'Sue','F');

insert into fname values (75,'Jackie','F');

insert into fname values (76,'Paulina','F');

insert into fname values (77,'Teodoro','M');

insert into fname values (78,'Yi','F');

insert into fname values (79,'Crissy','F');

insert into fname values (80,'Vanessa','F');

insert into fname values (81,'Allen','M');

insert into fname values (82,'Adam','M');

insert into fname values (83,'Greg','M');

insert into fname values (84,'Tippy','F');

insert into fname values (85,'Adrian','U');

insert into fname values (86,'Lucy','F');

insert into fname values (87,'Bert','M');

insert into fname values (88,'Grace','F');

insert into fname values (89,'Peggy','F');

insert into fname values (90,'Sissy','F');

commit;

create table lname (lname_id number, lname_name varchar2(40)) tablespace users;

 

insert into lname values (1,'Franko');

insert into lname values (2,'Welling');

insert into lname values (3,'Grabto');

insert into lname values (4,'Lawrence');

insert into lname values (5,'Plasto');

insert into lname values (6,'Chrand');

insert into lname values (7,'Tryo');

insert into lname values (8,'Black');

insert into lname values (9,'Matter');

insert into lname values (10,'Yahoo');

insert into lname values (11,'Liken');

insert into lname values (12,'Pott');

insert into lname values (13,'Starbuck');

insert into lname values (14,'Mather');

insert into lname values (15,'Ubb');

insert into lname values (16,'Tyron');

insert into lname values (17,'Into');

insert into lname values (18,'Strac');

insert into lname values (19,'Chilly');

insert into lname values (20,'Yado');

insert into lname values (21,'Lock');

insert into lname values (22,'Strasso');

insert into lname values (23,'Ink');

insert into lname values (24,'Tear');

insert into lname values (25,'Stew');

insert into lname values (26,'Till');

insert into lname values (27,'Opus');

insert into lname values (28,'Rondo');

insert into lname values (29,'Harp');

insert into lname values (30,'Picky');

insert into lname values (31,'Lulu');

insert into lname values (32,'Stamp');

insert into lname values (33,'Choolo');

insert into lname values (34,'Yarton');

insert into lname values (35,'Ulup');

insert into lname values (36,'Lack');

insert into lname values (37,'Placid');

insert into lname values (38,'Yoyo');

insert into lname values (39,'Orum');

insert into lname values (40,'Tyme');

insert into lname values (41,'York');

insert into lname values (42,'Tron');

insert into lname values (43,'Waz');

insert into lname values (44,'Plocko');

insert into lname values (45,'Yorn');

insert into lname values (46,'Pirend');

insert into lname values (47,'Yah');

insert into lname values (48,'Mired');

insert into lname values (49,'Stroble');

insert into lname values (50,'Mi');

insert into lname values (51,'Yeh');

insert into lname values (52,'Mikto');

insert into lname values (53,'Zibb');

insert into lname values (54,'Picker');

insert into lname values (55,'Radno');

insert into lname values (56,'Liu');

insert into lname values (57,'Zur');

insert into lname values (58,'Bock');

insert into lname values (59,'Brut');

insert into lname values (60,'Hrand');

insert into lname values (61,'Batt');

insert into lname values (62,'Loopo');

insert into lname values (63,'Zireo');

insert into lname values (64,'Piti');

insert into lname values (65,'Poin');

insert into lname values (66,'Midi');

insert into lname values (67,'Ole');

insert into lname values (68,'Joren');

insert into lname values (69,'Lire');

insert into lname values (70,'Star');

insert into lname values (71,'Lolo');

insert into lname values (72,'Blardy');

insert into lname values (73,'Trooper');

insert into lname values (74,'Zier');

insert into lname values (75,'Moodu');

insert into lname values (76,'Myno');

insert into lname values (77,'Torry');

insert into lname values (78,'Rondolo');

insert into lname values (79,'Plic');

insert into lname values (80,'Sro');

commit;

create table lname_suffix (lname_suffix_id number,

                           lname_suffix_suffix varchar2(10));

insert into lname_suffix values (1,'son');

insert into lname_suffix values (2,'ian');

insert into lname_suffix values (3,'io');

insert into lname_suffix values (4,'sto');

insert into lname_suffix values (5,'john');

insert into lname_suffix values (6,'smith');

insert into lname_suffix values (7,'tod');

insert into lname_suffix values (8,'cob');

insert into lname_suffix values (9,'blas');

insert into lname_suffix values (10,'ur');

insert into lname_suffix values (11,'igson');

insert into lname_suffix values (12,'ianmanson');

insert into lname_suffix values (13,'iotopolis');

insert into lname_suffix values (14,'stew');

insert into lname_suffix values (15,'johntrob');

insert into lname_suffix values (16,'smithicky');

insert into lname_suffix values (17,'topper');

insert into lname_suffix values (18,'col');

insert into lname_suffix values (19,'blastin');

insert into lname_suffix values (20,'iron');

insert into lname_suffix values (21,'la');

insert into lname_suffix values (22,'ron');

insert into lname_suffix values (23,'tra');

insert into lname_suffix values (24,'smyth');

insert into lname_suffix values (25,'lo');

insert into lname_suffix values (26,'pol');

insert into lname_suffix values (27,'er');

insert into lname_suffix values (28,'wa');

insert into lname_suffix values (29,'mir');

insert into lname_suffix values (30,'pire');

insert into lname_suffix values (31,'do');

insert into lname_suffix values (32,'gin');

insert into lname_suffix values (33,'ost');

insert into lname_suffix values (34,'ig');

insert into lname_suffix values (35,'lil');

insert into lname_suffix values (36,'uyh');

insert into lname_suffix values (37,'krop');

insert into lname_suffix values (38,'pip');

insert into lname_suffix values (39,'ind');

insert into lname_suffix values (40,'yasz');

insert into lname_suffix values (41,'op');

insert into lname_suffix values (42,'tor');

insert into lname_suffix values (43,'acing');

insert into lname_suffix values (44,'plo');

insert into lname_suffix values (45,'rond');

insert into lname_suffix values (46,'bing');

insert into lname_suffix values (47,'nono');

insert into lname_suffix values (48,'pore');

insert into lname_suffix values (49,'sire');

insert into lname_suffix values (50,'zindy');

commit;

create table street (street_id number, street_name varchar2(100));

insert into street values (1,'Pasadena Way');

insert into street values (2,'Orange Grove Ave.');

insert into street values (3,'Colorado Blvd.');

insert into street values (4,'Windward Way');

insert into street values (5,'La Presta Ct.');

insert into street values (6,'Strasson Lane');

insert into street values (7,'Chandler Blvd.');

insert into street values (8,'Mesa Court Drive');

insert into street values (9,'Standard Way');

insert into street values (10,'First Street');

insert into street values (11,'Noodle Drive');

insert into street values (12,'Stackton Blvd.');

insert into street values (13,'Martin Luther King Drive');

insert into street values (14,'Washington Ave.');

insert into street values (15,'Strasson Parkway');

insert into street values (16,'Highway 57');

insert into street values (17,'Longpark Drive');

insert into street values (18,'Bethington Lane');

insert into street values (19,'Charles McStreson Drive');

insert into street values (20,'Blackmore Lane');

insert into street values (21,'Chunko Way');

insert into street values (22,'Flappington Stadium Drive');

insert into street values (23,'Steriods Blvd.');

insert into street values (24,'Plathmore Lane');

insert into street values (25,'Sitting Bull Way');

insert into street values (26,'Miller Lane');

insert into street values (27,'Santa Clara Blvd.');

insert into street values (28,'Verdugo Drive');

insert into street values (29,'Pilliam Court Drive');

insert into street values (30,'Pracklone Street');

insert into street values (31,'Childer Drive');

insert into street values (32,'Mason Blvd.');

insert into street values (33,'Little Nook Drive');

insert into street values (34,'Stroop Ave.');

insert into street values (35,'Miller Highway');

insert into street values (36,'Yimook Drive');

insert into street values (37,'Ullman Way');

insert into street values (38,'Plackoop Street');

insert into street values (39,'Flapper Drive');

insert into street values (40,'Chilcot Lane');

insert into street values (41,'Santo Ricardo Drive');

insert into street values (42,'O''Rourk Lane');

insert into street values (43,'Jorge Mire Drive');

insert into street values (44,'Zander Way');

insert into street values (45,'Meander Drive');

insert into street values (46,'Urundo Place');

insert into street values (47,'Warzoond Lane');

insert into street values (48,'Doodlemeister Blvd.');

insert into street values (49,'Borondo Place');

insert into street values (50,'Crabtree Way');

commit;

create table zip (zip_id number, zip_code varchar2(9));

insert into zip values  (1,'91001');

insert into zip values  (2,'91002');

insert into zip values  (3,'91003');

insert into zip values  (4,'91008');

insert into zip values  (5,'91100');

insert into zip values  (6,'91101');

insert into zip values  (7,'91105');

insert into zip values  (8,'91201');

insert into zip values  (9,'91206');

insert into zip values (10,'91208');

insert into zip values (11,'91209');

insert into zip values (12,'91211');

insert into zip values (13,'91215');

insert into zip values (14,'91301');

insert into zip values (15,'91311');

insert into zip values (16,'91313');

insert into zip values (17,'91318');

insert into zip values (18,'91400');

insert into zip values (19,'91401');

insert into zip values (20,'92000');

insert into zip values (21,'92001');

insert into zip values (22,'92101');

insert into zip values (23,'92104');

insert into zip values (24,'92111');

insert into zip values (25,'92119');

insert into zip values (26,'93001');

insert into zip values (27,'93002');

commit;

create table cxclie (cxclie_id number,

                     cxclie_first_name varchar2(40),

                     cxclie_last_name