0

I am consistently getting an error telling me that there are too many values being inserted however this is clearly not the case. Can anybody help me. Here is my code. Currently the error message is INSERT INTO a2_account VALUES * ERROR at line 1: ORA-00913: too many values

    DROP TABLE a2_loanr;
    DROP TABLE a2_accr;
    DROP TABLE a2_customer;
    DROP TABLE a2_account;
    DROP TABLE a2_loan;
    DROP TABLE a2_bankbranch;
    DROP TABLE a2_bank;

    CREATE TABLE a2_bank (
           routingcode   VARCHAR(200)  PRIMARY KEY,
           name          VARCHAR(200)  NOT NULL,
           address       VARCHAR(200)  NOT NULL
    );

    INSERT INTO a2_bank VALUES
    ( '123456',' Stan walker', '3 gladstone rd');
    INSERT INTO a2_bank VALUES
    ( '123556',' Sam ben', '5 gladstone rd');
    INSERT INTO a2_bank VALUES
    ( '1256',' Stacy talker', '4 gladstone rd');


    CREATE TABLE a2_bankbranch (
    branch_num     VARCHAR(200)  PRIMARY KEY,
    name           VARCHAR(200)  NOT NULL,
    address        VARCHAR(200)  NOT NULL
    );
    INSERT INTO a2_bankbranch VALUES
    ( 'Ben Heir', '5', '3 gladstone rd');
    INSERT INTO a2_bankbranch VALUES
    ( 'Kane Wen', '4', '28 stevee rd');

    CREATE TABLE a2_loan (
    loan_num       VARCHAR(200)  PRIMARY KEY,
    type           VARCHAR(200)  NOT NULL,
    amount         VARCHAR(200)  NOT NULL,
    contract_date  DATE          NOT NULL
    );

    INSERT INTO a2_loan VALUES
    ( '323', 'Mortgage', '$2000000', TO_DATE('11-03-1994', 'DD-MM-YYYY') );
    INSERT INTO a2_loan VALUES
    ( '33', 'Car', '$2000', TO_DATE('12-08-1994', 'DD-MM-YYYY') );
    INSERT INTO a2_loan VALUES
    ( '3243', 'Pesonal', '$875', TO_DATE('14-06-1994', 'DD-MM-YYYY') );
    INSERT INTO a2_loan VALUES
    ( '6', 'Mortgage', '$400500', TO_DATE('11-06-1994', 'DD-MM-YYYY') );

    CREATE TABLE a2_account (
    acc_num       VARCHAR(20)  PRIMARY KEY,
    type           VARCHAR(20)  NOT NULL,
    balance         VARCHAR(10)  NOT NULL
    );
    INSERT INTO a2_account VALUES
    ( '2539267332', 'Savings', '20');
    INSERT INTO a2_account VALUES
    ( '8237893378', 'Cash', '300');
    INSERT INTO a2_account VALUES
    ( '2378723936', 'Cheque', '75');

    CREATE TABLE a2_customer (
    ird_num         CHAR(8)  PRIMARY KEY, 
    name            VARCHAR(200)  NOT NULL,
    address         VARCHAR(200)  NOT NULL,
    phone           VARCHAR(20)
    );
    INSERT INTO a2_customer VALUES
    ( '25362672', 'Stan Yel', '5 Wanna way', '02010201');
    INSERT INTO a2_account VALUES
    ( '83783783', 'Cam Birch', '34 Trada st', '02302020202');
    INSERT INTO a2_account VALUES
    ( '23723367', 'Jeff King', '5 Queens st', '38982383');
    INSERT INTO a2_account VALUES
    ( '54637822', 'John Smith', '24 Queen st', '38922383');


    CREATE TABLE a2_accr (
    ird_num                CHAR(8)  NOT NULL UNIQUE,
    account_num            CHAR(10)  NOT NULL UNIQUE
    );
    INSERT INTO a2_accr VALUES
    ( '25362672', '2537626722');
    INSERT INTO a2_accr VALUES
    ( '83783783', '8237832783');

    CREATE TABLE a2_loanr (
    ird_num                CHAR(8)  NOT NULL UNIQUE,
    loan_num            CHAR(10)  NOT NULL UNIQUE
    );
    INSERT INTO a2_loanr VALUES
    ( '54637822', '323');
    INSERT INTO a2_loanr VALUES
    ( '23723367', '33');

    COMMIT;
6
  • 1
    working fine for me, what version of oracle are you running? Commented Aug 20, 2015 at 13:00
  • This is a literal copy-paste of the code that you're running, and the error message definitely refers to this table? Are there any triggers or materialised views defined against the table? Commented Aug 20, 2015 at 13:03
  • Oracle Database 11g Release 11.2.0.2.0 - 64bit Production Commented Aug 20, 2015 at 13:03
  • 1
    You need to post a reasonable sample. Nobody would use varchar(200) for a balance :) Show us the actual code that causes the issue, ideally removing as much as you can while the issue still appears. In any case, I'd avoid using insert without specifying the column list - it's just way too fragile. Commented Aug 20, 2015 at 13:03
  • show the error message Commented Aug 20, 2015 at 13:11

3 Answers 3

1

The problem is in the INSERTS after the creation of table a2_customer, looks like you have copied and pasted some INSERTS and not changed the table name

CREATE TABLE a2_customer (
ird_num         CHAR(8)  PRIMARY KEY, 
name            VARCHAR(200)  NOT NULL,
address         VARCHAR(200)  NOT NULL,
phone           VARCHAR(20)
);
INSERT INTO a2_customer VALUES
( '25362672', 'Stan Yel', '5 Wanna way', '02010201');
INSERT INTO a2_account VALUES
( '83783783', 'Cam Birch', '34 Trada st', '02302020202');
INSERT INTO a2_account VALUES
( '23723367', 'Jeff King', '5 Queens st', '38982383');
INSERT INTO a2_account VALUES
( '54637822', 'John Smith', '24 Queen st', '38922383');

should be

CREATE TABLE a2_customer (
ird_num         CHAR(8)  PRIMARY KEY, 
name            VARCHAR(200)  NOT NULL,
address         VARCHAR(200)  NOT NULL,
phone           VARCHAR(20)
);
INSERT INTO a2_customer VALUES
( '25362672', 'Stan Yel', '5 Wanna way', '02010201');
INSERT INTO a2_customer VALUES
( '83783783', 'Cam Birch', '34 Trada st', '02302020202');
INSERT INTO a2_customer VALUES
( '23723367', 'Jeff King', '5 Queens st', '38982383');
INSERT INTO a2_customer VALUES
( '54637822', 'John Smith', '24 Queen st', '38922383');
Sign up to request clarification or add additional context in comments.

3 Comments

Cheers! Seriously dumb of me!
easily done, could you mark the answer as correct please
You can avoid this kind of error in future by not wrapping your insert lines. Just format each command on a single line, and vertically align the common elements of each command, and that typo will leap out at you as an unexpected difference in the visual pattern of the code.
0

Well..

these 3 lines have 4 values and the underline table only have 3 columns:

INSERT INTO a2_account VALUES
( '83783783', 'Cam Birch', '34 Trada st', '02302020202');
INSERT INTO a2_account VALUES
( '23723367', 'Jeff King', '5 Queens st', '38982383');
INSERT INTO a2_account VALUES
( '54637822', 'John Smith', '24 Queen st', '38922383');

Adjust this and all will be fine.

Comments

0

Here's your problem:

CREATE TABLE a2_customer (
    ird_num         CHAR(8)  PRIMARY KEY, 
    name            VARCHAR(200)  NOT NULL,
    address         VARCHAR(200)  NOT NULL,
    phone           VARCHAR(20)
    );
    INSERT INTO a2_customer VALUES
    ( '25362672', 'Stan Yel', '5 Wanna way', '02010201');
    INSERT INTO a2_account VALUES
    ( '83783783', 'Cam Birch', '34 Trada st', '02302020202');
    INSERT INTO a2_account VALUES
    ( '23723367', 'Jeff King', '5 Queens st', '38982383');
    INSERT INTO a2_account VALUES
    ( '54637822', 'John Smith', '24 Queen st', '38922383');

You create customer and then try insert into account.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.