I'm getting ORA-00928 trying to insert new data

Question

I'm probably making a rookie mistake here, but when I try to insert new data, I get the ORA-00928 error code. Take a look:

INSERT ALL
INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'GLOCK', '1982', 'PISTOLS', 'AUSTRIA')
INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'BERETTA', '1526', 'PISTOLS', 'ITALY')
INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'SMITH AND WESSON', '1856', 'PISTOLS', 'UNITED STATES')
INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'MOSSBERG', '1919', 'SHOTGUNS', 'UNITED STATES')
INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'BENELLI', '1967', 'SHOTGUNS', 'UNITED STATES')
INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'BROWNING', '1878', 'SHOTGUNS', 'UNITED STATES')
INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'WINCHESTER', '1866', 'RIFLES', 'UNITED STATES')
INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'BUSHMASTER', '1973', 'RIFLES', 'UNITED STATES')
INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'REMINGTON', '1816', 'RIFLES', 'UNITED STATES')
INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'KLASHNIKOV', '1807', 'RIFLES', 'UNITED STATES');

What am I doing wrong? Any insight would be helpful.

You're missing the `SELECT 1 FROM DUAL` at the end: https://stackoverflow.com/questions/39576/best-way-to-do-multi-row-insert-in-oracle/93724#93724 — TiiJ7, Aug 03 '20 at 07:45

score 1 · Answer 1 · answered Aug 03 '20 at 07:46

Add this at the end of your statement:

SELECT * FROM dual

ORA-00928 means "missing SELECT keyword". The INSERT ALL statement requires it, so it has the form:

INSERT ALL
  INTO t1 (col1, col2, ...) VALUES (val1, val2, ...)
  INTO t1 (col1, col2, ...) VALUES (val1, val2, ...)
  INTO t1 (col1, col2, ...) VALUES (val1, val2, ...)
SELECT * FROM dual

vishwampandya · Answer 2 · 2020-08-03T14:07:29.920

Year_founded column must be an INT type column, so don't use '' while inserting a value into INT type column.
Add SELECT * FROM DUALat the end.

Here is an example of the same using your query :

    INSERT ALL
    INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
    VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'GLOCK', 1982, 'PISTOLS', 'AUSTRIA')
    INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
    VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'BERETTA', 1526, 'PISTOLS', 'ITALY')
    INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
    VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'SMITH AND WESSON', 1856, 'PISTOLS', 'UNITED STATES')
    INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
    VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'MOSSBERG', 1919, 'SHOTGUNS', 'UNITED STATES')
    INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
    VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'BENELLI', 1967, 'SHOTGUNS', 'UNITED STATES')
    INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
    VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'BROWNING', 1878, 'SHOTGUNS', 'UNITED STATES')
    INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
    VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'WINCHESTER', 1866, 'RIFLES', 'UNITED STATES')
    INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
    VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'BUSHMASTER', 1973, 'RIFLES', 'UNITED STATES')
    INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
    VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'REMINGTON', 1816, 'RIFLES', 'UNITED STATES')
    INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
    VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'KLASHNIKOV', 1807, 'RIFLES', 'UNITED STATES')

    SELECT 1 FROM DUAL;

Thank you! Unfortunately, after that fix, I came across an ORA-00001 error. Evidently, by using a multi line insert with a sequence, the system tries to assign the same number to each row (due to the event occuring at the same time), violating the constraint. Alas, it looks like I have to go with single-row inserts. — Lubin, Aug 03 '20 at 23:13
Welcome, just wanted to know that the solution that I posted was the correct solution for your ORA-00928 error that you posted right? @Lubin — vishwampandya, Aug 04 '20 at 02:26
There's is another way that you can do the same work in the single query, you can simply rewrite the multi-table INSERT ALL into a single INSERT with multiple rows concatenated by UNION ALL. Here's the reference to the solution: https://stackoverflow.com/a/60459273/11617388 — vishwampandya, Aug 04 '20 at 02:35

score 0 · Answer 3 · answered Aug 03 '20 at 08:33

Complete script(';' should be after DUAL)

INSERT ALL
INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'GLOCK', '1982', 'PISTOLS', 'AUSTRIA')
INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'BERETTA', '1526', 'PISTOLS', 'ITALY')
INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'SMITH AND WESSON', '1856', 'PISTOLS', 'UNITED STATES')
INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'MOSSBERG', '1919', 'SHOTGUNS', 'UNITED STATES')
INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'BENELLI', '1967', 'SHOTGUNS', 'UNITED STATES')
INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'BROWNING', '1878', 'SHOTGUNS', 'UNITED STATES')
INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'WINCHESTER', '1866', 'RIFLES', 'UNITED STATES')
INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'BUSHMASTER', '1973', 'RIFLES', 'UNITED STATES')
INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'REMINGTON', '1816', 'RIFLES', 'UNITED STATES')
INTO MANUFACTURER ( MANUFACTURER_ID, NAME, YEAR_FOUNDED, SPECIALTY, ORIGIN)
VALUES (MANUFACTURER_ID_SEQ.NEXTVAL, 'KLASHNIKOV', '1807', 'RIFLES', 'UNITED STATES')
select * from dual;

I'm getting ORA-00928 trying to insert new data

3 Answers3