Thursday, November 27, 2008

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.

2 comments:

SydOracle said...

The record based insert works with 9i.
The trick to bypass the error is as follows:

FORALL i IN 1..rmt_runs_tab.COUNT
INSERT INTO
(select runid, related_run, run_owner, run_date, run_comment, ..., spare1 from plsql_profiler_runs)
VALUES rmt_runs_tab(i);

Byte64 said...

Hello Gary,
thanks for the tip!

I must say that the dml_table_expression_clause syntax looks really horrible to me ;-)

Flavio

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles