SQL Error: ORA-00947: not enough values Need Help Please-CodePudding

Can anyone please explain why am I getting not enough value error since I am using auto increment and not adding any value in the insert Thanks

CREATE TABLE Customer(
customerId INTEGER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1) primary key,
firstName VARCHAR2(25) not null,
middleName VARCHAR2(25),
lastName VARCHAR2(25) not null,
dateOfBirth DATE not null,
phoneNumber NUMBER(10) not null,
billingAddress VARCHAR2(35) not null
);

INSERT INTO Customer VALUES
('Sam', null, 'Siqi', (TO_DATE('18/12/1999', 'DD/MM/YYYY')),12345678, 
'Smith Street NSW');

CodePudding user response：

Try this:

INSERT INTO Customer VALUES (DEFAULT,'Sam', null, 'Siqi', (TO_DATE('18/12/1999', 'DD/MM/YYYY')),12345678, 'Smith Street NSW');

CodePudding user response：

This is a table you created. It contains 7 columns:

SQL> CREATE TABLE Customer
  2  (
  3     customerId       INTEGER
  4                        GENERATED ALWAYS AS IDENTITY
  5                           (      START WITH 1 INCREMENT BY 1)
  6                        PRIMARY KEY,
  7     firstName        VARCHAR2 (25) NOT NULL,
  8     middleName       VARCHAR2 (25),
  9     lastName         VARCHAR2 (25) NOT NULL,
 10     dateOfBirth      DATE NOT NULL,
 11     phoneNumber      NUMBER (10) NOT NULL,
 12     billingAddress   VARCHAR2 (35) NOT NULL
 13  );

Table created.

Your inserting attempt failed:

SQL> INSERT INTO Customer
  2       VALUES ('Sam',
  3               NULL,
  4               'Siqi',
  5               (TO_DATE ('18/12/1999', 'DD/MM/YYYY')),
  6               12345678,
  7               'Smith Street NSW');
INSERT INTO Customer
            *
ERROR at line 1:
ORA-00947: not enough values

Why did it fail? Because you inserted 6 values, while table has 7 columns. Always, but ALWAYS specify all columns involved. That includes select you write (i.e. avoid select *) and - in this case - insert. So:

SQL> INSERT INTO Customer (firstname,
  2                        middlename,
  3                        lastname,
  4                        dateofbirth,
  5                        phonenumber,
  6                        billingaddress)
  7       VALUES ('Sam',
  8               NULL,
  9               'Siqi',
 10               (TO_DATE ('18/12/1999', 'DD/MM/YYYY')),
 11               12345678,
 12               'Smith Street NSW');

1 row created.

SQL>

Now it works.

The fact that customerid is automatically generated doesn't mean that you can skip it.

Yes, you can use default (as Anand's answer suggests), but that's - in my opinion - bad practice. As I said: always name all columns you're working with.

SQL> INSERT INTO customer
  2       VALUES (DEFAULT,
  3               'Sam',
  4               NULL,
  5               'Siqi',
  6               (TO_DATE ('18/12/1999', 'DD/MM/YYYY')),
  7               12345678,
  8               'Smith Street NSW');

1 row created.

SQL>