-- table script
CREATE TABLE employees
(
EMPLOYEE_ID NUMBER(6) NOT NULL,
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25) NOT NULL,
SALARY NUMBER(8,2),
HIREDATE DATE,
DEPARTMENT_ID NUMBER(4)
);
CREATE SEQUENCE emp_seq --Sequence creation
START WITH 1
INCREMENT BY 1
NOCACHE;
Question: when I tried to run this insert query, I get an error "not enough values":
INSERT INTO employees (employee_id, last_name, hiredate)
VALUES ((SELECT emp_seq.nextval, 'Smith', SYSDATE FROM dual));
Can someone explain why?
I tried running nested select statement individually, getting desired result, but when tried both queries together I'm getting that error.
Sample table and sequence:
SQL> CREATE TABLE employees(
2 employee_id NUMBER(6) NOT NULL, ---table script
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(25) NOT NULL,
5 salary NUMBER(8,2),
6 hiredate DATE,
7 department_id NUMBER(4));
Table created.
SQL> CREATE SEQUENCE emp_seq --Sequence creation
2 START WITH 1
3 INCREMENT BY 1
4 NOCACHE;
Sequence created.
Indeed, your insert won't work because ...
SQL> INSERT INTO employees
2 (employee_id, last_name, hiredate)
3 VALUES ( (SELECT emp_seq.NEXTVAL, 'Smith', sysdate FROM dual) );
VALUES ( (SELECT emp_seq.NEXTVAL, 'Smith', sysdate FROM dual) )
----------------------------------------------------
... all of this is considered to be employee_id
*
ERROR at line 3:
ORA-00947: not enough values
But, if you put it this way:
SQL> INSERT INTO employees
2 (employee_id, last_name, hiredate)
3 (SELECT emp_seq.NEXTVAL, 'Smith', sysdate FROM dual);
1 row created.
Or this:
SQL> INSERT INTO employees
2 (employee_id, last_name, hiredate)
3 VALUES ( emp_seq.NEXTVAL, 'Jones', sysdate );
1 row created.
Then it works.
SQL> SELECT * FROM employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY HIREDATE DEPARTMENT_ID
----------- -------------------- ------------------------- ---------- ------------------- -------------
1 Smith 27.10.2024 10:03:44
2 Jones 27.10.2024 10:03:57
SQL>
On the other hand, if your database version allows it (I guess/hope you aren't on 11g or lower?), you could have used identity column and let Oracle handle IDs:
SQL> DROP TABLE employees;
Table dropped.
SQL> CREATE TABLE employees(
2 employee_id NUMBER(6) GENERATED ALWAYS AS IDENTITY NOT NULL, ---table script
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(25) NOT NULL,
5 salary NUMBER(8,2),
6 hiredate DATE,
7 department_id NUMBER(4));
Table created.
SQL> INSERT INTO employees
2 (last_name, hiredate)
3 VALUES ('Smith', sysdate );
1 row created.
SQL> SELECT * FROM employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY HIREDATE DEPARTMENT_ID
----------- -------------------- ------------------------- ---------- ------------------- -------------
1 Smith 27.10.2024 10:09:18
SQL>