Database SQL Script Errors and Solutions

This document presents a series of SQL script errors encountered during database creation and data insertion, specifically within an Oracle environment. The errors include 'ORA-00955: name is already used by an existing object,' indicating attempts to create tables with existing names, 'ORA-00001: unique constraint violated,' which arises from inserting duplicate primary key values, 'ORA-00902: invalid datatype,' suggesting incorrect data types used in table definitions, and 'ORA-01861: literal does not match format string,' which occurs when date formats in insert statements do not match the expected format. Additionally, there is an 'ORA-00917: missing comma' error. The document provides the error messages and their causes, offering insights into common SQL issues and their resolutions.

Error starting at line : 1 in command -
create table Project_Type
(
ptype_id integer primary key,
ptype_name varchar(50)
)
Error report -
SQL Error: ORA-00955: name is already used by an existing object
00955. 00000 - "name is already used by an existing object"
*Cause:
*Action:
Error starting at line : 7 in command -
insert into project_type(ptype_id, ptype_name) values (1, 'Web')
Error report -
SQL Error: ORA-00001: unique constraint (ABM0721.SYS_C0024374) violated
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.
Error starting at line : 8 in command -
insert into project_type(ptype_id, ptype_name) values (2, 'Maintenance')
Error report -
SQL Error: ORA-00001: unique constraint (ABM0721.SYS_C0024374) violated
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.
Error starting at line : 9 in command -
insert into project_type(ptype_id, ptype_name) values (3, 'App')
Error report -
SQL Error: ORA-00001: unique constraint (ABM0721.SYS_C0024374) violated
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.
Error starting at line : 10 in command -
insert into project_type(ptype_id, ptype_name) values (4, 'Testing')
Error report -
SQL Error: ORA-00001: unique constraint (ABM0721.SYS_C0024374) violated
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.
Error starting at line : 13 in command -
create table project
(
Project_idvarchar(10) primary key,
Project_Namevarchar(100),

Project_Type_id integer,
Start_Date date,
End_Date date,
expect_finish_date date,
constraintp_fk foreign key(Project_Type_id) references Project_Type(ptype_id)
)
Error report -
SQL Error: ORA-00902: invalid datatype
00902. 00000 - "invalid datatype"
*Cause:
*Action:
Error starting at line : 24 in command -
insert into project(Project_id, Project_Name, Project_Type_id, Start_Date, End_Date,
expect_finish_date) values ('B1049', 'Health System', 1, '2016-06-01', '2016-09-01', '2016-09-02')
Error report -
SQL Error: ORA-01861: literal does not match format string
01861. 00000 - "literal does not match format string"
*Cause: Literals in the input must be the same length as literals in
the format string (with the exception of leading whitespace). If the
"FX" modifier has been toggled on, the literal must match exactly,
with no extra whitespace.
*Action: Correct the format string to match the literal.
Error starting at line : 26 in command -
insert into project(Project_id, Project_Name, Project_Type_id, Start_Date, End_Date,
expect_finish_date) values ('B1050', 'insurance System', 2, '2016-01-01', '2016-03-03', '2016-03-
06')
Error report -
SQL Error: ORA-01861: literal does not match format string
01861. 00000 - "literal does not match format string"
*Cause: Literals in the input must be the same length as literals in
the format string (with the exception of leading whitespace). If the
"FX" modifier has been toggled on, the literal must match exactly,
with no extra whitespace.
*Action: Correct the format string to match the literal.
Error starting at line : 28 in command -
insert into project(Project_id, Project_Name, Project_Type_id, Start_Date, End_Date,
expect_finish_date) values ('B1051', 'university System', 3, '2016-05-01', '2016-06-01', '2016-06-
05')
Error report -
SQL Error: ORA-01861: literal does not match format string
01861. 00000 - "literal does not match format string"
*Cause: Literals in the input must be the same length as literals in
the format string (with the exception of leading whitespace). If the
"FX" modifier has been toggled on, the literal must match exactly,
with no extra whitespace.
*Action: Correct the format string to match the literal.
Error starting at line : 30 in command -
create table Job_type
(
type_id varchar(5) primary key,
type_name varchar(50)
)
Error report -
SQL Error: ORA-00955: name is already used by an existing object

00955. 00000 - "name is already used by an existing object"
*Cause:
*Action:
Error starting at line : 36 in command -
insert into job_type(type_id, type_name) values ('JT1', 'Imaging')
Error report -
SQL Error: ORA-00001: unique constraint (ABM0721.SYS_C0024805) violated
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.
Error starting at line : 37 in command -
insert into job_type(type_id, type_name) values ('JT2', 'Web Developer')
Error report -
SQL Error: ORA-00001: unique constraint (ABM0721.SYS_C0024805) violated
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.
Error starting at line : 38 in command -
insert into job_type(type_id, type_name) values ('JT3', 'Maintenance')
Error report -
SQL Error: ORA-00001: unique constraint (ABM0721.SYS_C0024805) violated
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.
Error starting at line : 39 in command -
insert into job_type(type_id, type_name) values ('JT4', 'Training')
Error report -
SQL Error: ORA-00001: unique constraint (ABM0721.SYS_C0024805) violated
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.
Error starting at line : 42 in command -
create table Staff
(
Staff_IDvarchar(10) primary key,
Name varchar(50),
Job_Type_idvarchar(5),
Job_Grade integer,
constraints_fk foreign key(job_type_id) references Job_type(type_id)
)
Error report -
SQL Error: ORA-00902: invalid datatype
00902. 00000 - "invalid datatype"
*Cause:

*Action:
Error starting at line : 51 in command -
insert into staff(Staff_id, name, Job_Type_id, Job_Grade) values ('1014E', 'John Baker',
'JT1', 3)
Error report -
SQL Error: ORA-00001: unique constraint (ABM0721.SYS_C0024806) violated
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.
Error starting at line : 52 in command -
insert into staff(Staff_id, name, Job_Type_id, Job_Grade) values ('230R1', 'Marian Dowd',
'JT2', 1)
Error report -
SQL Error: ORA-00001: unique constraint (ABM0721.SYS_C0024806) violated
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.
Error starting at line : 53 in command -
insert into staff(Staff_id, name, Job_Type_id, Job_Grade) values ('0P625', 'Jennifer Tuff',
'JT3', 2)
Error report -
SQL Error: ORA-00001: unique constraint (ABM0721.SYS_C0024806) violated
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.
Error starting at line : 54 in command -
insert into staff(Staff_id, name, Job_Type_id, Job_Grade) values ('4T371', 'Paul Downing',
'JT4', 3 )
Error report -
SQL Error: ORA-00001: unique constraint (ABM0721.SYS_C0024806) violated
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.
Error starting at line : 55 in command -
insert into staff(Staff_id, name, Job_Type_id, Job_Grade) values ('1044E', 'Johnny Woods',
'JT1', 3)
Error report -
SQL Error: ORA-00001: unique constraint (ABM0721.SYS_C0024806) violated
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.
Error starting at line : 56 in command -

insert into staff(Staff_id, name, Job_Type_id, Job_Grade) values ('233R1', 'Mia S.', 'JT2', 1)
Error report -
SQL Error: ORA-00001: unique constraint (ABM0721.SYS_C0024806) violated
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.
Error starting at line : 57 in command -
insert into staff(Staff_id, name, Job_Type_id, Job_Grade) values ('0Q625', 'Jenny Duff',
'JT3', 2)
Error report -
SQL Error: ORA-00001: unique constraint (ABM0721.SYS_C0024806) violated
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.
Error starting at line : 58 in command -
insert into staff(Staff_id, name, Job_Type_id, Job_Grade) values ('6T371', 'Pal Sterling',
'JT4', 3 )
Error report -
SQL Error: ORA-00001: unique constraint (ABM0721.SYS_C0024806) violated
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.
Error starting at line : 61 in command -
create table Staff_skill
(
Staff_IDvarchar(10),
skill_name varchar(50),
primary key(staff_id, skill_name),
constraintss_fk foreign key(Staff_ID) references staff(Staff_ID)
)
Error report -
SQL Error: ORA-00902: invalid datatype
00902. 00000 - "invalid datatype"
*Cause:
*Action:
Error starting at line : 70 in command -
INSERT INTO Staff_skill(STAFF_ID, SKILL_NAME) VALUES('1014E', 'DATA IMAGING' )
Error report -
SQL Error: ORA-00001: unique constraint (ABM0721.SYS_C0024808) violated
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.
Error starting at line : 71 in command -
INSERT INTO Staff_skill(STAFF_ID, SKILL_NAME) VALUES('230R1', WEB DEVELOPMENT' );

INSERT INTO Staff_skill(STAFF_ID, SKILL_NAME) VALUES('0P625', 'OFFLINE
DEVELOPMENT' );
INSERT INTO Staff_skill(STAFF_ID, SKILL_NAME) VALUES('4T371', 'C++' );
INSERT INTO Staff_skill(STAFF_ID, SKILL_NAME) VALUES('1044E', 'BLACK BOX' );
INSERT INTO Staff_skill(STAFF_ID, SKILL_NAME) VALUES('233R1', 'HTML' );
INSERT INTO Staff_skill(STAFF_ID, SKILL_NAME) VALUES('0Q625', 'SQL' );
INSERT INTO Staff_skill(STAFF_ID, SKILL_NAME) VALUES('6T371', 'NOSQL' );
create table Project_Staff
(
Staff_IDvarchar(10),
Project_idvarchar(10),
primary key(staff_id, project_id),
constraint ps_fk1 foreign key(Staff_ID) references staff(Staff_ID),
constraint ps_fk2 foreign key(project_ID) references project(project_ID)
);
INSERT INTO PROJECT_STAFF(STAFF_ID, PROJECT_ID) VALUES('1014E', 'B1049' );
INSERT INTO PROJECT_STAFF(STAFF_ID, PROJECT_ID) VALUES('230R1', 'B1049' );
INSERT INTO PROJECT_STAFF(STAFF_ID, PROJECT_ID) VALUES('0P625', 'B1049' );
INSERT INTO PROJECT_STAFF(STAFF_ID, PROJECT_ID) VALUES('4T371', 'B1049' );
INSERT INTO PROJECT_STAFF(STAFF_ID, PROJECT_ID) VALUES('1044E', 'B1050' );
INSERT INTO PROJECT_STAFF(STAFF_ID, PROJECT_ID) VALUES('233R1', 'B1050' );
INSERT INTO PROJECT_STAFF(STAFF_ID, PROJECT_ID) VALUES('0Q625', 'B1051' );
INSERT INTO PROJECT_STAFF(STAFF_ID, PROJECT_ID) VALUES('6T371', 'B1050' );
INSERT INTO PROJECT_STAFF(STAFF_ID, PROJECT_ID) VALUES('6T371', 'B1051' );
create table Software
(
Software_ID integer primary key,
Software_namevarchar(50)
);
INSERT INTO SOFTWARE(SOFTWARE_ID, SOFTWARE_NAME) VALUES (1001, 'Encryption
Protocol Suite');
INSERT INTO SOFTWARE(SOFTWARE_ID, SOFTWARE_NAME) VALUES (1002, 'Autodesk
Maya');
INSERT INTO SOFTWARE(SOFTWARE_ID, SOFTWARE_NAME) VALUES (1003, 'Rational
rose');
INSERT INTO SOFTWARE(SOFTWARE_ID, SOFTWARE_NAME) VALUES (1004, 'Sql Server');
create table Hardware
(
Hardware_ID integer primary key,
Hardware_namevarchar(50)
);
INSERT INTO HARDWARE(HARDWARE_ID, HARDWARE_NAME) VALUES (101, 'Games
Monitor');
INSERT INTO HARDWARE(HARDWARE_ID, HARDWARE_NAME) VALUES (102, 'Web Cam');
INSERT INTO HARDWARE(HARDWARE_ID, HARDWARE_NAME) VALUES (103, 'Scanner');
INSERT INTO HARDWARE(HARDWARE_ID, HARDWARE_NAME) VALUES (104, 'External
Drive');
INSERT INTO HARDWARE(HARDWARE_ID, HARDWARE_NAME) VALUES (105, 'Hard Disk
5GB');

INSERT INTO HARDWARE(HARDWARE_ID, HARDWARE_NAME) VALUES (106, 'Flat Screen
Monitor');
INSERT INTO HARDWARE(HARDWARE_ID, HARDWARE_NAME) VALUES (107, 'Imagines
tool');
create table Project_hware
(
Project_idvarchar(10),
Hardware_ID integer,
primary key(project_id, hardware_id),
constraint ph_fk1 foreign key(hardware_ID) references hardware(hardware_ID),
constraint ph_fk2 foreign key(project_ID) references project(project_ID)
);
insert into Project_hware(project_id, hardware_id) values('B1049', 101);
insert into Project_hware(project_id, hardware_id) values('B1049', 102);
insert into Project_hware(project_id, hardware_id) values('B1049', 103);
insert into Project_hware(project_id, hardware_id) values('B1049', 104);
insert into Project_hware(project_id, hardware_id) values('B1050', 101);
insert into Project_hware(project_id, hardware_id) values('B1050', 102);
insert into Project_hware(project_id, hardware_id) values('B1051', 101);
insert into Project_hware(project_id, hardware_id) values('B1051', 104);
insert into Project_hware(project_id, hardware_id) values('B1051', 102);
insert into Project_hware(project_id, hardware_id) values('B1051', 103);
insert into Project_hware(project_id, hardware_id) values('B1050', 107);
insert into Project_hware(project_id, hardware_id) values('B1051', 106);
create table Project_sware
(
Project_idvarchar(10),
Software_ID integer,
primary key(project_id, software_id),
constraint psw_fk1 foreign key(software_ID) references software(software_ID),
constraint psw_fk2 foreign key(project_ID) references project(project_ID)
);
insert into Project_sware(project_id, software_id) values('B1049', 1001);
insert into Project_sware(project_id, software_id) values('B1049', 1002);
insert into Project_sware(project_id, software_id) values('B1049', 1003);
insert into Project_sware(project_id, software_id) values('B1051', 1001);
insert into Project_sware(project_id, software_id) values('B1051', 1002);
insert into Project_sware(project_id, software_id) values('B1050', 1001);
insert into Project_sware(project_id, software_id) values('B1050', 1004);
Error at Command Line : 71 Column : 67
Error report -
SQL Error: ORA-00917: missing comma
00917. 00000 - "missing comma"
*Cause:
*Action: