Oracle Database Sql Certified Associate · Free Practice Question Medium
Question 24
Scenario: You are trying to insert a new employee record into the EMPLOYEES table. The HIRE_DATE column in the table is defined as DATE, but you are attempting to insert a string literal '01-JAN-2025' without any explicit conversion.
- INSERT INTO EMPLOYEES (employee_id, first_name, last_name, hire_date, salary)
- VALUES (207, 'Alice', 'Smith', '01-JAN-2025', 65000);
Problem: The INSERT statement fails with an ORA error.
Which of the following is the most likely cause of the error?
-
A
The
salaryvalue (65000) is too high for theSALARYcolumn. -
B
There is an implicit data type conversion failure for the
hire_datecolumn. -
C
The
employee_id207 already exists. -
D
The
EMPLOYEEStable has a missing primary key constraint.
Reveal correct answer
Correct answer: B
Explanation
Most Likely Cause of Error: There is an implicit data type conversion failure for the hire_date column.
Analyze the INSERT statement and the scenario:
Statement:
- INSERT INTO EMPLOYEES (employee_id, first_name, last_name, hire_date, salary)
- VALUES (207, 'Alice', 'Smith', '01-JAN-2025', 65000);
Table Schema Info:
hire_datecolumn is of typeDATE'01-JAN-2025'is a string literal, not aDATEtype
Oracle attempts implicit conversion, but it's not guaranteed for all string formats to DATE. Explicit conversion using TO_DATE() is required for reliable inserts of string literals into DATE columns.
The implicit conversion of strings to DATE depends on the current NLS_DATE_FORMAT session setting. If the format 'DD-MON-YYYY' doesn’t match the system’s expected date format (e.g., 'YYYY-MM-DD'), the conversion will fail with an ORA-01861 or similar error.
To avoid this, you should explicitly convert the string using TO_DATE():
- TO_DATE('01-JAN-2025', 'DD-MON-YYYY')
Why the Other Options Are Unlikely:
"The employee_id 207 already exists"
→ Would raise a unique constraint violation, but the question points to a data type error, not a uniqueness issue."The salary value (65000) is too high for the SALARY column"
→ No such issue is stated, and 65000 is a reasonable salary."The EMPLOYEES table has a missing primary key constraint"
→ This would not cause an error on insert. It might allow duplicates, but it wouldn't block the insert.
A. The error is not caused by the salary value being too high for the SALARY column. Oracle would not fail the INSERT statement due to the salary value exceeding a limit, as long as it is within the defined data type range.
B. The most likely cause of the error is an implicit data type conversion failure for the hire_date column. When trying to insert a string literal '01-JAN-2025' into a DATE column without explicit conversion, Oracle will encounter a data type mismatch and fail the INSERT statement.
C. The error is not related to the employee_id already existing in the table. The primary key violation would result in a different error message, not an ORA error due to data type conversion failure.
D. The error is not related to a missing primary key constraint on the EMPLOYEES table. While a primary key constraint is important for data integrity, its absence would not directly cause the ORA error in this scenario.
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
