ORA-00947: not enough values

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

You may encounter the following exception:

ORA-00947: not enough values

when you attempt to compile a PL/SQL unit containing a FORALL construct in conjunction with a collection based on a record data type:

DECLARE
TYPE prf_runs_type     IS TABLE OF plsql_profiler_runs%ROWTYPE;
rmt_runs_tab           prf_runs_type;
...
BEGIN
...
FORALL i IN 1..rmt_runs_tab.COUNT
INSERT INTO plsql_profiler_runs
  (runid, related_run, run_owner, run_date, run_comment, ..., spare1)
VALUES rmt_runs_tab(i);
...
END;

The problem with the FORALL statement above is in that i am specifying the list of fields to be inserted whereas the PL/SQL parser is expecting a simpler syntax:

FORALL i IN 1..rmt_runs_tab.COUNT
INSERT INTO plsql_profiler_runs
VALUES rmt_runs_tab(i);

Note that bulk operations involving record data types are perfectly legal, however i could not verify if this type of constructs is valid on Oracle versions prior to 10gR2.

ORA-00947 is also returned in trivial situations like INSERT statements containing an insufficient number of parameters in the VALUES clause or too few columns in a SELECT list as in these examples:

INSERT INTO plsql_profiler_runs
(runid, run_owner)
VALUES(1);

INSERT INTO plsql_profiler_runs
(runid, run_owner)
SELECT 1 FROM dual;

See message translations for ORA-00947 and search additional resources.