mckenzie Posted May 25, 2006 Share Posted May 25, 2006 (edited) hi there i've been given a task to change the following script to create a new user, dev1 with password dev1 who has unlimited access. I have to create another table called university that has ID, name, address, number of students, date of frst contact! i have been trying but not getting anywhere. it seems fairly simple to do by copy and apstgin the original script and changing the names.the underlined code is what i attempted. i try the user thing but fail. spool dbcre8.logconnect system/managerdrop user ora1 cascade;drop user ora2 cascade;create user ora1 identified by ora1;grant create session to ora1;grant create table to ora1;grant create view to ora1;grant create sequence to ora1;grant create synonym to ora1;grant create procedure to ora1;grant create trigger to ora1;grant create cluster to ora1;grant create type to ora1;alter user ora1 quota unlimited on system;drop user ora2 cascade;create user ora2 identified by ora2;grant create session to ora2;grant create table to ora2;grant create view to ora2;grant create sequence to ora2;grant create synonym to ora2;grant create procedure to ora2;grant create trigger to ora2;grant create type to ora2;grant create cluster to ora2;alter user ora2 quota unlimited on system;connect ora1/ora1CREATE OR REPLACE TYPE address_type AS OBJECT (street VARCHAR2(25),city VARCHAR2(25),country VARCHAR2(20) )/CREATE TABLE person_address OF address_type;CREATE TABLE student ( student_id number(4) not null, student_fname varchar2(10) not null, student_lname varchar2(12) not null, address REF address_type SCOPE IS person_address, tel_no varchar2(15), fax_no number(15), gender varchar2(1), date_of_birth date, student_desc varchar2(250), preferred_language varchar2(2), passport_program varchar2(1), company_id number(2) not null);CREATE TABLE company ( company_id number(2) not null, company_name varchar2(15), city varchar2(12), industry varchar2(15), no_employees number(5), revenue number(10), training_budget number(6));CREATE TABLE instructor ( instructor_id number(3) not null, instructor_name varchar2(15) not null, address REF address_type SCOPE IS person_address, salary number(6,2), commission number(6,2), mentor_id number(3), date_hired date, profile CLOB);CREATE TABLE course ( course_id number(3) not null, title varchar2(30), duration number(2), cost number(5), subject_id number(2), brochure bfile);CREATE TABLE authorization ( instructor_id number(3) not null, course_id number(3) not null, auth_date date);CREATE TABLE subject_area ( subject_id number(2) not null, subject_name varchar2(30));CREATE TABLE offering ( offering_id number(5) not null, course_id number(3) not null, site_id number(2) not null, instructor_id number(4), start_date date, max_no_students number(2));[u]CREATE TABLE university ( university_id number(5) not null, name varchar(20), number_of_students number(6), date_of_first_contact date,);[/u]CREATE TYPE classroom_type AS OBJECT(room_number NUMBER(2) ,capacity NUMBER(2) ,description VARCHAR2(200));/CREATE TYPE classroom_varray_type AS VARRAY(50) OF classroom_type;/CREATE TABLE site ( site_id number(2) not null, address address_type, classroom classroom_varray_type, location varchar2(12));CREATE TABLE attendance ( offering_id number(5) not null, student_id number(4) not null, evaluation varchar2(1), amount_paid number(6,2));CREATE OR REPLACE TYPE invoice_item_type AS OBJECT(student_lname VARCHAR2(12) ,course_title VARCHAR2(30) ,start_date DATE ,end_date DATE ,amount NUMBER(7,2) );/CREATE TYPE invoice_item_table_type AS TABLE OF invoice_item_type;/CREATE TABLE invoice(invoice_number NUMBER(6) NOT NULL,company_id NUMBER(2) NOT NULL,billing_date DATE NOT NULL,due_date DATE,invoice_item invoice_item_table_type)NESTED TABLE invoice_item STORE AS invoice_item_table;insert into person_address (street, city, country)values ('111 BAY STATE ROAD', 'BOSTON', 'USA');insert into person_address (street, city, country)values ('111 VALLEY WAY', 'SAN FERNANDO', 'USA');insert into person_address (street, city, country)values ('4545 ORACLE DRIVE', 'SAN FRANCISCO', 'USA');insert into person_address (street, city, country)values ('22 GOLDEN GATE DRIVE', 'SAN FRANCISCO', 'USA');insert into person_address (street, city, country)values ('77 ELM STREET', 'NEWARK', 'USA');insert into person_address (street, city, country)values ('544 42ND STREET', 'NEW YORK', 'USA');insert into person_address (street, city, country)values ('111 BEACHCOMBER PLACE', 'OCEANSIDE', 'USA');insert into person_address (street, city, country)values ('25 MISSION WAY', 'SAN DIEGO', 'USA');insert into person_address (street, city, country)values ('177 AIRPORT ROAD', 'NEWARK', 'USA');insert into person_address (street, city, country)values ('5567 KNIGHTSBRIDGE COURT', 'LONDON', 'UK');insert into person_address (street, city, country)values ('111 CRUISING WAY', 'TORTOLA', 'BVI');insert into person_address (street, city, country)values ('45 KING STREET', 'OTTAWA', 'CANADA');insert into person_address (street, city, country)values ('45555 BIG STREET', 'DALLAS', 'USA');insert into person_address (street, city, country)values ('AVENUE CHAMPS-ELYSEES 456', 'PARIS', 'FRANCE');insert into person_address (street, city, country)values ('562 HOCKEY STREET', 'TORONTO', 'CANADA');insert into person_address (street, city, country)values ('4242 MISTY LANE', 'SEATTLE', 'USA');insert into person_address (street, city, country)values ('KUNGSGATAN 56', 'STOCKHOLM', 'SWEDEN');insert into person_address (street, city, country)values ('11-882 KAMEHAMEHA HIGHWAY', 'HAWAII', 'USA');insert into person_address (street, city, country)values ('333 SAN JUAN BLVD', 'SAN JUAN', 'PUERTO RICO');insert into person_address (street, city, country)values ('7500 IMPERIAL BLVD', 'LOS ANGELES', 'USA');insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (1111, 'DIANE', 'BROWN', '(617)342-2345', 6173422345, 'F', '01-FEB-1964', 'EN', 20);update student set address = (select ref(x) from person_address xwhere x.street='111 BAY STATE ROAD')where student_id = 1111;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (2299, 'CHRIS', 'ADAMS', '(213)334-2789', 2133342789, 'M', '22-MAR-1965', 'EN', 30);update student set address = (select ref(x) from person_address xwhere x.street='7500 IMPERIAL BLVD')where student_id = 2299;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (4568, 'KEVIN', 'COX', '(619)433-6845', 6194336845, 'M', '04-DEC-1963', 'EN', 10);update student set address = (select ref(x) from person_address xwhere x.street='25 MISSION WAY')where student_id = 4568;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (5556, 'JOHN', 'TYLER', '(212)444-9769', 2124449769, 'M', '13-JUN-1966', 'EN', 20);update student set address = (select ref(x) from person_address xwhere x.street='544 42ND STREET')where student_id = 5556;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (6874, 'NANCY', 'GIBBS', '(714)346-2896', 7143462896, 'F', '15-JUL-1965', 'EN', 10);update student set address = (select ref(x) from person_address xwhere x.street='111 VALLEY WAY')where student_id = 6874;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (6789, 'JENNY', 'ROSE', '(415)334-2345', 4153342345, 'F', '16-SEP-1963', 'SW', 10);update student set address = (select ref(x) from person_address xwhere x.street='4545 ORACLE DRIVE')where student_id = 6789;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (1199, 'FRANK', 'NELSON', '(609)345-2346', 6093452346, 'M', '22-APR-1964', 'EN', 20);update student set address = (select ref(x) from person_address xwhere x.street='77 ELM STREET')where student_id = 1199;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (6263, 'KEN', 'CRICK', '(415)345-2313', 4153452313, 'M', '14-MAR-1965', 'EN', 50);update student set address = (select ref(x) from person_address xwhere x.street='22 GOLDEN GATE DRIVE')where student_id = 6263;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (3452, 'NIGEL', 'TURNER', '(818)352-2511', 8183522511, 'M', '16-FEB-1962', 'SW', 10);update student set address = (select ref(x) from person_address xwhere x.street='111 BEACHCOMBER PLACE')where student_id = 3452;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (2346, 'CARL', 'DUDLEY', '(609)223-7890', 6092237890, 'M', '28-NOV-1967', 'EN', 20);update student set address = (select ref(x) from person_address xwhere x.street='5567 KNIGHTSBRIDGE COURT')where student_id = 2346;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (9842, 'MIKE', 'COOPER', '(713)726-9842', 7137269842, 'M', '18-OCT-1962', 'EN', 50);update student set address = (select ref(x) from person_address xwhere x.street='562 HOCKEY STREET')where student_id = 9842;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (6901, 'LILY', 'GREEN', '(809)342-9621', 8093429621, 'F', '14-MAY-1964', 'EN', 30);update student set address = (select ref(x) from person_address xwhere x.street='111 CRUISING WAY')where student_id = 6901;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (7652, 'MARY', 'FRANKS', '(713)234-8521', 7132348521, 'F', '23-DEC-1965', 'EN', 60);update student set address = (select ref(x) from person_address xwhere x.street='45 KING STREET')where student_id = 7652;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (4631, 'JOE', 'FISHER', '(709)452-7392', 7094527392, 'M', '22-NOV-1962', 'EN', 50);update student set address = (select ref(x) from person_address xwhere x.street='AVENUE CHAMPS-ELYSEES 456')where student_id = 4631;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (3459, 'LARRY', 'MYERS', '(909)352-6216', 9093526216, 'M', '15-AUG-1967', 'EN', 10);update student set address = (select ref(x) from person_address xwhere x.street='4242 MISTY LANE')where student_id = 3459;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (6738, 'NICK', 'CARTER', '(808)238-2358', 8082382358, 'M', '17-FEB-1964', 'EN', 20);update student set address = (select ref(x) from person_address xwhere x.street='11-882 KAMEHAMEHA HIGHWAY')where student_id = 6738;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (8390, 'CAROL', 'WOOD', '(809)342-2345', 8093422345, 'F', '01-MAR-1964', 'EN', 60); update student set address = (select ref(x) from person_address xwhere x.street='KUNGSGATAN 56')where student_id = 8390;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (8872, 'DEBBY', 'ARCHER', '(980)247-2349', 9802472349, 'F', '01-SEP-1964', 'EN', 20);update student set address = (select ref(x) from person_address xwhere x.street='333 SAN JUAN BLVD')where student_id = 8872;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (6474, 'PETER', 'HALL', '(707)843-2346', 7078432346, 'M', '22-FEB-1965', 'EN', 10);update student set address = (select ref(x) from person_address xwhere x.street='45555 BIG STREET')where student_id = 6474;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (9835, 'PAUL', 'MARKS', '(706)343-2345', 7063432345, 'M', '16-DEC-1964', 'EN', 20);update student set address = (select ref(x) from person_address xwhere x.street='177 AIRPORT ROAD')where student_id = 9835;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (7777, 'FRED', 'FLINTSTONE', '(747)987-6543', 7478882929, 'M', '04-JUL-1904', 'FR', 70);update student set address = (select ref(x) from person_address xwhere x.street='177 AIRPORT ROAD')where student_id = 7777insert into company (company_id, company_name, city, industry, no_employees, revenue, training_budget)values (10, 'USCABLES', 'WASHINGTON', 'MANUFACTURING', 250, 4000000, 50000);insert into company (company_id, company_name, city, industry, no_employees, revenue, training_budget)values (20, 'KODAK', 'HOUSTON', 'MANUFACTURING', 2500, 10000000, 100000);insert into company (company_id, company_name, city, industry, no_employees, revenue, training_budget)values (30, 'KLM', 'AMSTERDAM', 'SERVICE', 5000, 11000000, 990000);insert into company (company_id, company_name, city, industry, no_employees, revenue, training_budget)values (40, 'HAMBRO', 'LONDON', 'SERVICE', 2000, 6000000, 500000);insert into company (company_id, company_name, city, industry, no_employees, revenue, training_budget)values (50, 'FORD', 'DETROIT', 'MANUFACTURING', 8000, 20000000, 100000);insert into company (company_id, company_name, city, industry, no_employees, revenue, training_budget)values (60, 'CITYBANK', 'BOSTON', 'FINANCE', 3000, 5000000, 200000);insert into company (company_id, company_name, city, industry, no_employees, revenue, training_budget)values (70, 'DISNEY', 'ORLANDO', 'ARTS', 2500, 10000000, 300000);insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (835, 'SPARKS', 4000 , 200, 978, '16-DEC-1984');update instructor set address = (select ref(x) from person_address xwhere x.street='177 AIRPORT ROAD')where instructor_id = 835;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (978, 'STEEL', 5000 , 250, 222, '16-JAN-1980');update instructor set address = (select ref(x) from person_address xwhere x.street='177 AIRPORT ROAD')where instructor_id = 978;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (222, 'CAINE', 5500 , 350, NULL, '02-NOV-1976');update instructor set address = (select ref(x) from person_address xwhere x.street='333 SAN JUAN BLVD')where instructor_id = 222;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (243, 'TUCKER', 2000 , NULL, 835, '18-DEC-1990');update instructor set address = (select ref(x) from person_address xwhere x.street='11-882 KAMEHAMEHA HIGHWAY')where instructor_id = 243;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (263, 'JOHNSON', 4000 , NULL, 835, '18-JUL-1992');update instructor set address = (select ref(x) from person_address xwhere x.street='562 HOCKEY STREET')where instructor_id = 263;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (515, 'SHELLEY', 3500 , 200, 700, '20-JAN-1991');update instructor set address = (select ref(x) from person_address xwhere x.street='4242 MISTY LANE')where instructor_id = 515;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (453, 'LODGE', 2500 , 100, 835, '14-SEP-1988');update instructor set address = (select ref(x) from person_address xwhere x.street='4242 MISTY LANE')where instructor_id = 453;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (700, 'WAYNE', 4500 , 300, NULL, '16-MAY-1981');update instructor set address = (select ref(x) from person_address xwhere x.street='AVENUE CHAMPS-ELYSEES 456')where instructor_id = 700;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (628, 'MONROE', 3000 , NULL, 700, '16-JUN-1984');update instructor set address = (select ref(x) from person_address xwhere x.street='5567 KNIGHTSBRIDGE COURT')where instructor_id = 628;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (790, 'NEWMAN', 3100 , 300, 700, '16-DEC-1982');update instructor set address = (select ref(x) from person_address xwhere x.street='544 42ND STREET')where instructor_id = 790;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (795, 'BOGART', 3200 , NULL, 700, '16-DEC-1983');update instructor set address = (select ref(x) from person_address xwhere x.street='111 CRUISING WAY')where instructor_id = 795;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (560, 'LAUREL', 5200 , 200, 978, '16-DEC-1983');update instructor set address = (select ref(x) from person_address xwhere x.street='333 SAN JUAN BLVD')where instructor_id = 560;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (123, 'FLINTSTONE,FRED', 1000 , NULL, 978, '16-JUN-1989');update instructor set address = (select ref(x) from person_address xwhere x.street='177 AIRPORT ROAD')where instructor_id = 123insert into course values (215, 'UNIX', 5, 1995, 10, null);insert into course values (315, 'C++', 5, 1995, 20, null);insert into course values (415, 'ADA', 4, 1500, 20, null);insert into course values (515, 'ORACLE', 5, 1995, 30, null);insert into course values (615, 'SYBASE', 3, 1200, 30, null);insert into course values (115, 'DOS', 1, 500, 10, null);insert into course values (715, 'VMS', 2, 995, 10, null);insert into course values (815, 'NETWARE', 5, 1995, 40, null);insert into course values (915, 'INFORMIX', 3, 1200, 30, null);insert into subject_area values (10, 'SYSTEMS');insert into subject_area values (20, 'LANGUAGES');insert into subject_area values (30, 'DBMS');insert into subject_area values (40, 'NETWORKS');insert into authorization values (222, 315, '14-MAR-1990');insert into authorization values (222, 415, '13-JAN-1992');insert into authorization values (978, 815, '14-MAR-1989');insert into authorization values (700, 315, '14-FEB-1991');insert into authorization values (835, 515, '12-MAR-1992');insert into authorization values (515, 915, '22-APR-1991');insert into authorization values (263, 215, '14-JUN-1992');insert into authorization values (263, 315, '14-FEB-1992');insert into authorization values (453, 615, '14-JUL-1993');insert into authorization values (453, 715, '14-AUG-1993');insert into authorization values (628, 315, '13-JUL-1991');insert into authorization values (790, 315, '14-AUG-1991');insert into site values (1, address_type('3 Place de Concorde','PARIS','FRANCE'),classroom_varray_type (classroom_type (1, 20, null), classroom_type (2, 24, null), classroom_type (3, 30, null)),'Europe Ctr');insert into site values (2, address_type('1600 Pennsylvania','WASHINGTON','USA'),classroom_varray_type (classroom_type (1, 20, null), classroom_type (2, 24, null)), 'US East');insert into site values (3, address_type('22 Trafalgar Square','LONDON','UK'), classroom_varray_type (classroom_type (1, 20, null), classroom_type (2, 24, null), classroom_type (3, 24, null)),'Europe West');insert into site values (4, address_type('Kungsgatan 26','STOCKHOLM','SWEDEN'), classroom_varray_type (classroom_type (1, 20, null)), 'Europe North');insert into site values (5, address_type('343 Elm Street','OTTAWA','CANADA'),classroom_varray_type (classroom_type (1, 20, null), classroom_type (2, 20, null), classroom_type (3, 20, null)), 'Canada Ctr');insert into site values (6, address_type('101 Boylston Street','BOSTON','USA'),classroom_varray_type (classroom_type (1, 20, null), classroom_type (2, 24, null)), 'US East');insert into site values (7, address_type('1010 Sepulveda','LOS ANGELES','USA'),classroom_varray_type (classroom_type (1, 20, null), classroom_type (2, 20, null), classroom_type (3, 20, null)) , 'US West');insert into site values (8, address_type('101 Younge Street','TORONTO','CANADA'), classroom_varray_type (classroom_type (1, 20, null), classroom_type (2, 24, null)), 'Canada South'); insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1001, 315, 700, 1, '16-DEC-1994', 24);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1002, 315, 263, 3, '16-JUL-1995', 20);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1003, 915, 560, 7, '23-NOV-1996', 30);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1004, 215, 263, 2, '26-SEP-1995', 24);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1005, 815, 978, 3, '15-OCT-1995', 20);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1006, 915, 560, 4, '04-JAN-1996', 22);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1007, 615, 453, 4, '30-SEP-1995', 30);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1008, 515, 835, 5, '20-JUN-1995', 24);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1009, 315, 790, 4, '21-JUN-1995', 24);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1010, 315, NULL, 4, '30-AUG-1997', 24);insert into attendance values (1001,1111, 4, 1000);insert into attendance values (1002,2299, NULL, 1000);insert into attendance values (1003,4568, 3, 1200);insert into attendance values (1002,6789, 4, 1150);insert into attendance values (1005,9835, 3, 1995);insert into attendance values (1006,9842, 4, 1000);insert into attendance values (1006,1199, 4, 1200);insert into attendance values (1006,3452, 2, 200);insert into attendance values (1007,8390, 4, 1200);insert into attendance values (1007,6474, 4, 900);insert into attendance values (1008,8390, 4, 1000);insert into attendance values (1003,1199, 4, 1000);insert into attendance values (1001,6738, 4, 850);insert into attendance values (1001,5556, 4, 1995);insert into attendance values (1001,6263, 1, 1995);insert into attendance values (1002,2346, 4, 1995);insert into attendance values (1003,6901, 4, 1200);insert into attendance values (1003,7652, 4, 1200);insert into attendance values (1003,4631, 3, 1200);insert into attendance values (1008,3459, 4, 1995);insert into attendance values (1004,2299, 3, 1995);insert into attendance values (1004,6789, 4, 1995);insert into attendance values (1004,6263, 4, 1995);insert into attendance values (1007,9842, 3, 1200);insert into attendance values (1007,4631, 3, 1200);insert into attendance values (1005,6738, 4, 1000);insert into attendance values (1005,3459, 3, 1995);insert into attendance values (1006,6474, 2, 1200);insert into attendance values (1006,9835, 4, 1000);insert into attendance values (1007,9835, 4, 1200);insert into attendance values (1009,3459, 3, 4000);commit;create sequence inv_no_seq start with 10000 ;insert into invoiceselect inv_no_seq.nextval, co.company_id, trunc(sysdate), trunc(sysdate) + 30,CAST (MULTISET(select s.student_lname, c.title, o.start_date , o.start_date + c.duration, a.amount_paidfrom offering o , attendance a, course c, student swhere o.offering_id=a.offering_idand o.course_id = c.course_idand s.student_id = a.student_idand co.company_id = s.company_id) AS INVOICE_ITEM_TABLE_TYPE)FROM COMPANY CO;commit;spool off Edited May 25, 2006 by aspnetguy Link to comment Share on other sites More sharing options...
Lights Out Posted May 25, 2006 Share Posted May 25, 2006 (edited) hi there i've been given a task to change the following script to create a new user, dev1 with password dev1 who has unlimited access. I have to create another table called university that has ID, name, address, number of students, date of frst contact! i have been trying but not getting anywhere. it seems fairly simple to do by copy and apstgin the original script and changing the names.the underlined code is what i attempted. i try the user thing but fail. spool dbcre8.logconnect system/managerdrop user ora1 cascade;drop user ora2 cascade;create user ora1 identified by ora1;grant create session to ora1;grant create table to ora1;grant create view to ora1;grant create sequence to ora1;grant create synonym to ora1;grant create procedure to ora1;grant create trigger to ora1;grant create cluster to ora1;grant create type to ora1;alter user ora1 quota unlimited on system;drop user ora2 cascade;create user ora2 identified by ora2;grant create session to ora2;grant create table to ora2;grant create view to ora2;grant create sequence to ora2;grant create synonym to ora2;grant create procedure to ora2;grant create trigger to ora2;grant create type to ora2;grant create cluster to ora2;alter user ora2 quota unlimited on system;connect ora1/ora1CREATE OR REPLACE TYPE address_type AS OBJECT (street VARCHAR2(25),city VARCHAR2(25),country VARCHAR2(20) )/CREATE TABLE person_address OF address_type;CREATE TABLE student ( student_id   number(4)   not null, student_fname  varchar2(10) not null, student_lname  varchar2(12) not null, address REF address_type SCOPE IS person_address, tel_no     varchar2(15), fax_no     number(15), gender    varchar2(1), date_of_birth  date, student_desc      varchar2(250), preferred_language   varchar2(2), passport_program    varchar2(1), company_id number(2)   not null);CREATE TABLE company ( company_id   number(2)   not null, company_name  varchar2(15), city      varchar2(12), industry    varchar2(15), no_employees  number(5), revenue    number(10), training_budget number(6));CREATE TABLE instructor ( instructor_id number(3)   not null, instructor_name varchar2(15) not null, address REF address_type SCOPE IS person_address, salary     number(6,2), commission   number(6,2), mentor_id   number(3), date_hired   date, profile CLOB);CREATE TABLE course ( course_id   number(3)   not null, title     varchar2(30), duration    number(2), cost      number(5), subject_id   number(2), brochure bfile);CREATE TABLE authorization ( instructor_id number(3)   not null, course_id   number(3)   not null, auth_date   date);CREATE TABLE subject_area ( subject_id   number(2)   not null, subject_name  varchar2(30));CREATE TABLE offering ( offering_id  number(5)   not null, course_id   number(3)   not null, site_id    number(2)   not null, instructor_id number(4), start_date   date, max_no_students number(2));[u]CREATE TABLE university (        university_id number(5)    not null,        name      varchar(20),            number_of_students   number(6),           date_of_first_contact   date,);[/u]CREATE TYPE classroom_type AS OBJECT(room_number NUMBER(2) ,capacity NUMBER(2) ,description VARCHAR2(200));/CREATE TYPE classroom_varray_type AS VARRAY(50) OF classroom_type;/CREATE TABLE site ( site_id    number(2)   not null, address address_type, classroom classroom_varray_type, location    varchar2(12));CREATE TABLE attendance ( offering_id  number(5)   not null, student_id   number(4)   not null, evaluation   varchar2(1), amount_paid  number(6,2));CREATE OR REPLACE TYPE invoice_item_type AS OBJECT(student_lname VARCHAR2(12) ,course_title VARCHAR2(30) ,start_date DATE ,end_date DATE ,amount NUMBER(7,2) );/CREATE TYPE invoice_item_table_type AS TABLE OF invoice_item_type;/CREATE TABLE invoice(invoice_number NUMBER(6) NOT NULL,company_id NUMBER(2) NOT NULL,billing_date DATE NOT NULL,due_date DATE,invoice_item invoice_item_table_type)NESTED TABLE invoice_item STORE AS invoice_item_table;insert into person_address (street, city, country)values ('111 BAY STATE ROAD', 'BOSTON', 'USA');insert into person_address (street, city, country)values ('111 VALLEY WAY', 'SAN FERNANDO', 'USA');insert into person_address (street, city, country)values ('4545 ORACLE DRIVE', 'SAN FRANCISCO', 'USA');insert into person_address (street, city, country)values ('22 GOLDEN GATE DRIVE', 'SAN FRANCISCO', 'USA');insert into person_address (street, city, country)values ('77 ELM STREET', 'NEWARK', 'USA');insert into person_address (street, city, country)values ('544 42ND STREET', 'NEW YORK', 'USA');insert into person_address (street, city, country)values ('111 BEACHCOMBER PLACE', 'OCEANSIDE', 'USA');insert into person_address (street, city, country)values ('25 MISSION WAY', 'SAN DIEGO', 'USA');insert into person_address (street, city, country)values ('177 AIRPORT ROAD', 'NEWARK', 'USA');insert into person_address (street, city, country)values ('5567 KNIGHTSBRIDGE COURT', 'LONDON', 'UK');insert into person_address (street, city, country)values ('111 CRUISING WAY', 'TORTOLA', 'BVI');insert into person_address (street, city, country)values ('45 KING STREET', 'OTTAWA', 'CANADA');insert into person_address (street, city, country)values ('45555 BIG STREET', 'DALLAS', 'USA');insert into person_address (street, city, country)values ('AVENUE CHAMPS-ELYSEES 456', 'PARIS', 'FRANCE');insert into person_address (street, city, country)values ('562 HOCKEY STREET', 'TORONTO', 'CANADA');insert into person_address (street, city, country)values ('4242 MISTY LANE', 'SEATTLE', 'USA');insert into person_address (street, city, country)values ('KUNGSGATAN 56', 'STOCKHOLM', 'SWEDEN');insert into person_address (street, city, country)values ('11-882 KAMEHAMEHA HIGHWAY', 'HAWAII', 'USA');insert into person_address (street, city, country)values ('333 SAN JUAN BLVD', 'SAN JUAN', 'PUERTO RICO');insert into person_address (street, city, country)values ('7500 IMPERIAL BLVD', 'LOS ANGELES', 'USA');insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (1111, 'DIANE', 'BROWN', '(617)342-2345', 6173422345, 'F', '01-FEB-1964', 'EN', 20);update student set address = (select ref(x) from person_address xwhere x.street='111 BAY STATE ROAD')where student_id = 1111;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (2299, 'CHRIS', 'ADAMS', '(213)334-2789', 2133342789, 'M', '22-MAR-1965', 'EN', 30);update student set address = (select ref(x) from person_address xwhere x.street='7500 IMPERIAL BLVD')where student_id = 2299;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (4568, 'KEVIN', 'COX', '(619)433-6845', 6194336845, 'M', '04-DEC-1963', 'EN', 10);update student set address = (select ref(x) from person_address xwhere x.street='25 MISSION WAY')where student_id = 4568;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (5556, 'JOHN', 'TYLER', '(212)444-9769', 2124449769, 'M', '13-JUN-1966', 'EN', 20);update student set address = (select ref(x) from person_address xwhere x.street='544 42ND STREET')where student_id = 5556;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (6874, 'NANCY', 'GIBBS', '(714)346-2896', 7143462896, 'F', '15-JUL-1965', 'EN', 10);update student set address = (select ref(x) from person_address xwhere x.street='111 VALLEY WAY')where student_id = 6874;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (6789, 'JENNY', 'ROSE', '(415)334-2345', 4153342345, 'F', '16-SEP-1963', 'SW', 10);update student set address = (select ref(x) from person_address xwhere x.street='4545 ORACLE DRIVE')where student_id = 6789;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (1199, 'FRANK', 'NELSON', '(609)345-2346', 6093452346, 'M', '22-APR-1964', 'EN', 20);update student set address = (select ref(x) from person_address xwhere x.street='77 ELM STREET')where student_id = 1199;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (6263, 'KEN', 'CRICK', '(415)345-2313', 4153452313, 'M', '14-MAR-1965', 'EN', 50);update student set address = (select ref(x) from person_address xwhere x.street='22 GOLDEN GATE DRIVE')where student_id = 6263;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (3452, 'NIGEL', 'TURNER', '(818)352-2511', 8183522511, 'M', '16-FEB-1962', 'SW', 10);update student set address = (select ref(x) from person_address xwhere x.street='111 BEACHCOMBER PLACE')where student_id = 3452;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (2346, 'CARL', 'DUDLEY', '(609)223-7890', 6092237890, 'M', '28-NOV-1967', 'EN', 20);update student set address = (select ref(x) from person_address xwhere x.street='5567 KNIGHTSBRIDGE COURT')where student_id = 2346;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (9842, 'MIKE', 'COOPER', '(713)726-9842', 7137269842, 'M', '18-OCT-1962', 'EN', 50);update student set address = (select ref(x) from person_address xwhere x.street='562 HOCKEY STREET')where student_id = 9842;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (6901, 'LILY', 'GREEN', '(809)342-9621', 8093429621, 'F', '14-MAY-1964', 'EN', 30);update student set address = (select ref(x) from person_address xwhere x.street='111 CRUISING WAY')where student_id = 6901;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (7652, 'MARY', 'FRANKS', '(713)234-8521', 7132348521, 'F', '23-DEC-1965', 'EN', 60);update student set address = (select ref(x) from person_address xwhere x.street='45 KING STREET')where student_id = 7652;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (4631, 'JOE', 'FISHER', '(709)452-7392', 7094527392, 'M', '22-NOV-1962', 'EN', 50);update student set address = (select ref(x) from person_address xwhere x.street='AVENUE CHAMPS-ELYSEES 456')where student_id = 4631;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (3459, 'LARRY', 'MYERS', '(909)352-6216', 9093526216, 'M', '15-AUG-1967', 'EN', 10);update student set address = (select ref(x) from person_address xwhere x.street='4242 MISTY LANE')where student_id = 3459;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (6738, 'NICK', 'CARTER', '(808)238-2358', 8082382358, 'M', '17-FEB-1964', 'EN', 20);update student set address = (select ref(x) from person_address xwhere x.street='11-882 KAMEHAMEHA HIGHWAY')where student_id = 6738;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (8390, 'CAROL', 'WOOD', '(809)342-2345', 8093422345, 'F', '01-MAR-1964', 'EN', 60);   update student set address = (select ref(x) from person_address xwhere x.street='KUNGSGATAN 56')where student_id = 8390;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (8872, 'DEBBY', 'ARCHER', '(980)247-2349', 9802472349, 'F', '01-SEP-1964', 'EN', 20);update student set address = (select ref(x) from person_address xwhere x.street='333 SAN JUAN BLVD')where student_id = 8872;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (6474, 'PETER', 'HALL', '(707)843-2346', 7078432346, 'M', '22-FEB-1965', 'EN', 10);update student set address = (select ref(x) from person_address xwhere x.street='45555 BIG STREET')where student_id = 6474;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (9835, 'PAUL', 'MARKS', '(706)343-2345', 7063432345, 'M', '16-DEC-1964', 'EN', 20);update student set address = (select ref(x) from person_address xwhere x.street='177 AIRPORT ROAD')where student_id = 9835;insert into student (student_id, student_fname, student_lname, tel_no, fax_no, gender,date_of_birth, preferred_language, company_id)values (7777, 'FRED', 'FLINTSTONE', '(747)987-6543', 7478882929, 'M', '04-JUL-1904', 'FR', 70);update student set address = (select ref(x) from person_address xwhere x.street='177 AIRPORT ROAD')where student_id = 7777insert into company (company_id, company_name, city, industry, no_employees, revenue, training_budget)values (10, 'USCABLES', 'WASHINGTON', 'MANUFACTURING', 250, 4000000, 50000);insert into company (company_id, company_name, city, industry, no_employees, revenue, training_budget)values (20, 'KODAK', 'HOUSTON', 'MANUFACTURING', 2500, 10000000, 100000);insert into company (company_id, company_name, city, industry, no_employees, revenue, training_budget)values (30, 'KLM', 'AMSTERDAM', 'SERVICE', 5000, 11000000, 990000);insert into company (company_id, company_name, city, industry, no_employees, revenue, training_budget)values (40, 'HAMBRO', 'LONDON', 'SERVICE', 2000, 6000000, 500000);insert into company (company_id, company_name, city, industry, no_employees, revenue, training_budget)values (50, 'FORD', 'DETROIT', 'MANUFACTURING', 8000, 20000000, 100000);insert into company (company_id, company_name, city, industry, no_employees, revenue, training_budget)values (60, 'CITYBANK', 'BOSTON', 'FINANCE', 3000, 5000000, 200000);insert into company (company_id, company_name, city, industry, no_employees, revenue, training_budget)values (70, 'DISNEY', 'ORLANDO', 'ARTS', 2500, 10000000, 300000);insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (835, 'SPARKS', 4000 , 200, 978, '16-DEC-1984');update instructor set address = (select ref(x) from person_address xwhere x.street='177 AIRPORT ROAD')where instructor_id = 835;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (978, 'STEEL', 5000 , 250, 222, '16-JAN-1980');update instructor set address = (select ref(x) from person_address xwhere x.street='177 AIRPORT ROAD')where instructor_id = 978;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (222, 'CAINE', 5500 , 350, NULL, '02-NOV-1976');update instructor set address = (select ref(x) from person_address xwhere x.street='333 SAN JUAN BLVD')where instructor_id = 222;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (243, 'TUCKER', 2000 , NULL, 835, '18-DEC-1990');update instructor set address = (select ref(x) from person_address xwhere x.street='11-882 KAMEHAMEHA HIGHWAY')where instructor_id = 243;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (263, 'JOHNSON', 4000 , NULL, 835, '18-JUL-1992');update instructor set address = (select ref(x) from person_address xwhere x.street='562 HOCKEY STREET')where instructor_id = 263;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (515, 'SHELLEY', 3500 , 200, 700, '20-JAN-1991');update instructor set address = (select ref(x) from person_address xwhere x.street='4242 MISTY LANE')where instructor_id = 515;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (453, 'LODGE', 2500 , 100, 835, '14-SEP-1988');update instructor set address = (select ref(x) from person_address xwhere x.street='4242 MISTY LANE')where instructor_id = 453;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (700, 'WAYNE', 4500 , 300, NULL, '16-MAY-1981');update instructor set address = (select ref(x) from person_address xwhere x.street='AVENUE CHAMPS-ELYSEES 456')where instructor_id = 700;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (628, 'MONROE', 3000 , NULL, 700, '16-JUN-1984');update instructor set address = (select ref(x) from person_address xwhere x.street='5567 KNIGHTSBRIDGE COURT')where instructor_id = 628;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (790, 'NEWMAN', 3100 , 300, 700, '16-DEC-1982');update instructor set address = (select ref(x) from person_address xwhere x.street='544 42ND STREET')where instructor_id = 790;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (795, 'BOGART', 3200 , NULL, 700, '16-DEC-1983');update instructor set address = (select ref(x) from person_address xwhere x.street='111 CRUISING WAY')where instructor_id = 795;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (560, 'LAUREL', 5200 , 200, 978, '16-DEC-1983');update instructor set address = (select ref(x) from person_address xwhere x.street='333 SAN JUAN BLVD')where instructor_id = 560;insert into instructor (instructor_id, instructor_name, salary, commission, mentor_id, date_hired)values (123, 'FLINTSTONE,FRED', 1000 , NULL, 978, '16-JUN-1989');update instructor set address = (select ref(x) from person_address xwhere x.street='177 AIRPORT ROAD')where instructor_id = 123insert into course values (215, 'UNIX', 5, 1995, 10, null);insert into course values (315, 'C++', 5, 1995, 20, null);insert into course values (415, 'ADA', 4, 1500, 20, null);insert into course values (515, 'ORACLE', 5, 1995, 30, null);insert into course values (615, 'SYBASE', 3, 1200, 30, null);insert into course values (115, 'DOS', 1, 500, 10, null);insert into course values (715, 'VMS', 2, 995, 10, null);insert into course values (815, 'NETWARE', 5, 1995, 40, null);insert into course values (915, 'INFORMIX', 3, 1200, 30, null);insert into subject_area values (10, 'SYSTEMS');insert into subject_area values (20, 'LANGUAGES');insert into subject_area values (30, 'DBMS');insert into subject_area values (40, 'NETWORKS');insert into authorization values (222, 315, '14-MAR-1990');insert into authorization values (222, 415, '13-JAN-1992');insert into authorization values (978, 815, '14-MAR-1989');insert into authorization values (700, 315, '14-FEB-1991');insert into authorization values (835, 515, '12-MAR-1992');insert into authorization values (515, 915, '22-APR-1991');insert into authorization values (263, 215, '14-JUN-1992');insert into authorization values (263, 315, '14-FEB-1992');insert into authorization values (453, 615, '14-JUL-1993');insert into authorization values (453, 715, '14-AUG-1993');insert into authorization values (628, 315, '13-JUL-1991');insert into authorization values (790, 315, '14-AUG-1991');insert into site values (1, address_type('3 Place de Concorde','PARIS','FRANCE'),classroom_varray_type (classroom_type (1, 20, null),  classroom_type (2, 24, null),  classroom_type (3, 30, null)),'Europe Ctr');insert into site values (2, address_type('1600 Pennsylvania','WASHINGTON','USA'),classroom_varray_type (classroom_type (1, 20, null),  classroom_type (2, 24, null)), 'US East');insert into site values (3, address_type('22 Trafalgar Square','LONDON','UK'), classroom_varray_type (classroom_type (1, 20, null),  classroom_type (2, 24, null),  classroom_type (3, 24, null)),'Europe West');insert into site values (4, address_type('Kungsgatan 26','STOCKHOLM','SWEDEN'), classroom_varray_type (classroom_type (1, 20, null)), 'Europe North');insert into site values (5, address_type('343 Elm Street','OTTAWA','CANADA'),classroom_varray_type (classroom_type (1, 20, null),  classroom_type (2, 20, null),  classroom_type (3, 20, null)), 'Canada Ctr');insert into site values (6, address_type('101 Boylston Street','BOSTON','USA'),classroom_varray_type (classroom_type (1, 20, null),  classroom_type (2, 24, null)), 'US East');insert into site values (7, address_type('1010 Sepulveda','LOS ANGELES','USA'),classroom_varray_type (classroom_type (1, 20, null),  classroom_type (2, 20, null),  classroom_type (3, 20, null)) , 'US West');insert into site values (8, address_type('101 Younge Street','TORONTO','CANADA'), classroom_varray_type (classroom_type (1, 20, null),  classroom_type (2, 24, null)), 'Canada South'); insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1001, 315, 700, 1, '16-DEC-1994', 24);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1002, 315, 263, 3, '16-JUL-1995', 20);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1003, 915, 560, 7, '23-NOV-1996', 30);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1004, 215, 263, 2, '26-SEP-1995', 24);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1005, 815, 978, 3, '15-OCT-1995', 20);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1006, 915, 560, 4, '04-JAN-1996', 22);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1007, 615, 453, 4, '30-SEP-1995', 30);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1008, 515, 835, 5, '20-JUN-1995', 24);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1009, 315, 790, 4, '21-JUN-1995', 24);insert into offering (offering_id, course_id, instructor_id, site_id, start_date, max_no_students)values (1010, 315, NULL, 4, '30-AUG-1997', 24);insert into attendance values (1001,1111, 4, 1000);insert into attendance values (1002,2299, NULL, 1000);insert into attendance values (1003,4568, 3, 1200);insert into attendance values (1002,6789, 4, 1150);insert into attendance values (1005,9835, 3, 1995);insert into attendance values (1006,9842, 4, 1000);insert into attendance values (1006,1199, 4, 1200);insert into attendance values (1006,3452, 2, 200);insert into attendance values (1007,8390, 4, 1200);insert into attendance values (1007,6474, 4, 900);insert into attendance values (1008,8390, 4, 1000);insert into attendance values (1003,1199, 4, 1000);insert into attendance values (1001,6738, 4, 850);insert into attendance values (1001,5556, 4, 1995);insert into attendance values (1001,6263, 1, 1995);insert into attendance values (1002,2346, 4, 1995);insert into attendance values (1003,6901, 4, 1200);insert into attendance values (1003,7652, 4, 1200);insert into attendance values (1003,4631, 3, 1200);insert into attendance values (1008,3459, 4, 1995);insert into attendance values (1004,2299, 3, 1995);insert into attendance values (1004,6789, 4, 1995);insert into attendance values (1004,6263, 4, 1995);insert into attendance values (1007,9842, 3, 1200);insert into attendance values (1007,4631, 3, 1200);insert into attendance values (1005,6738, 4, 1000);insert into attendance values (1005,3459, 3, 1995);insert into attendance values (1006,6474, 2, 1200);insert into attendance values (1006,9835, 4, 1000);insert into attendance values (1007,9835, 4, 1200);insert into attendance values (1009,3459, 3, 4000);commit;create sequence inv_no_seq start with 10000 ;insert into invoiceselect inv_no_seq.nextval, co.company_id, trunc(sysdate), trunc(sysdate) + 30,CAST (MULTISET(select  s.student_lname, c.title, o.start_date , o.start_date + c.duration, a.amount_paidfrom offering o , attendance a, course c, student swhere o.offering_id=a.offering_idand o.course_id = c.course_idand s.student_id = a.student_idand co.company_id = s.company_id) AS INVOICE_ITEM_TABLE_TYPE)FROM COMPANY CO;commit;spool off <{POST_SNAPBACK}> omg Edited May 25, 2006 by aspnetguy Link to comment Share on other sites More sharing options...
mckenzie Posted May 25, 2006 Author Share Posted May 25, 2006 sorry if this cheeky to put all this on here. jus didnt know who to ask. bit unprofessional iknow Link to comment Share on other sites More sharing options...
Lights Out Posted May 25, 2006 Share Posted May 25, 2006 haha sorry. it is just a lot to read. Link to comment Share on other sites More sharing options...
pulpfiction Posted May 25, 2006 Share Posted May 25, 2006 Hi, When you have code this long put it in the CODE tag, it will be easier. Link to comment Share on other sites More sharing options...
mckenzie Posted May 25, 2006 Author Share Posted May 25, 2006 yeh that would've been a good idea. I'm a bit behind on my SQL so just rushed the post. This forum seems to calm me down so u won't get anymore of that wild posting out of me. With this task i have to add 2 users, ones called dev1 and the other ukacademic1. dev1 has a password named dev1 and ukacademic1 has a password ukacc1. dev1 has unlimited access but ukacademic1 has only the read only access to a view of the student, instructor and university tables but be restricted to the students/ instructors who have links with UK universities. how would i go about doing this? Link to comment Share on other sites More sharing options...
aspnetguy Posted May 25, 2006 Share Posted May 25, 2006 Hi, When you have code this long put it in the CODE tag, it will be easier.<{POST_SNAPBACK}> Actually any code should go in CODE tags and if it is long code then it should go in CODEBOX tags Link to comment Share on other sites More sharing options...
mckenzie Posted May 25, 2006 Author Share Posted May 25, 2006 ok thank god we got that code box thing out the way, phew was that a hum-dinger of a problem now back to adding 2 users and granting readonly access to 3 tables where they are linked to uk universtities. any clues? i'll be on here all nite. yep setting upcamp! Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now