java.sql.SQLSyntaxErrorException: ORA-00947: not enough values On Insert Statement

Question

I created following table:

BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE "MY2XSYEHT" (
    pKy NUMBER(19, 0) PRIMARY KEY AUTOINCREMENT,
    cTm NUMBER(19, 0), uTm NUMBER(19, 0), oTm NUMBER(19, 0), eTm NUMBER(19, 0),
    oID VARCHAR2(32), kID VARCHAR2(32), idx NUMBER(3,0), typ NUMBER(3,0),
    nVl FLOAT(24), sVl VARCHAR2(64), tVl VARCHAR2(4000) )';
  EXCEPTION WHEN OTHERS THEN
    IF SQLCODE != -955 THEN RAISE;
  END IF;
END;

Than created a sequence and trigger on primary key:

CREATE SEQUENCE "MY2XSYEHT_SEQ" START WITH 1001 INCREMENT BY 1 CACHE 10

CREATE OR REPLACE TRIGGER "MY2XSYEHT_TRIGGER"
BEFORE INSERT ON "MY2XSYEHT"
FOR EACH ROW
DECLARE
BEGIN
  IF( :NEW.pKy IS NULL ) THEN 
    :NEW.pKy := "MY2XSYEHT_SEQ".nextval
  END IF;
END;

When executing following MERGE statement I am getting "ORA-00947: not enough values"

MERGE INTO "MY2XSYEHT" destTable
USING (SELECT ? oID, ? kID, ? idx, ? typ, ? nVl, ? sVl, ? tVl FROM DUAL) srcTable
ON (destTable.oID=srcTable.oID
  and destTable.kID=srcTable.kID
  and destTable.idx=srcTable.idx)
WHEN MATCHED THEN UPDATE SET destTable.typ=srcTable.typ,
  destTable.nVl=srcTable.nVl,
  destTable.sVl=srcTable.sVl,
  destTable.tVl=srcTable.tVl
WHEN NOT MATCHED THEN INSERT VALUES (srcTable.oID, srcTable.kID, srcTable.idx,
  srcTable.typ, srcTable.nVl, srcTable.sVl, srcTable.tVl)

What are all the "?" Is this executed through JDBC or other oracle client? Next time please format your code. You are inserting 7 values when there are 12 columns in the table. — OldProgrammer, Aug 17 '15 at 16:23

score 1 · Accepted Answer · answered Aug 17 '15 at 16:48

As OldProgrammer pointed out, your table has 12 columns and your insert is only populating 7 of them. You either need to provide values for all the columns, or specify which columns you're populating - which is much better even if you are populating everything, as it makes it clearer what is happening and avoids potential problems with column ordering.

So it looks like you want to be doing:

WHEN NOT MATCHED THEN INSERT (oID, kID, idx, typ, nVl, sVl, tVl)
  VALUES (srcTable.oID, srcTable.kID, srcTable.idx,
    srcTable.typ, srcTable.nVl, srcTable.sVl, srcTable.tVl)

Your trigger will auto-populate pKy, and the other four columns will be inserted as null.

java.sql.SQLSyntaxErrorException: ORA-00947: not enough values On Insert Statement

1 Answers1