You may encounter the following exception:
ORA-00947: not enough valueswhen you attempt to compile a PL/SQL unit containing a FORALL construct in conjunction with a collection based on a record data type:
DECLAREThe 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:
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;
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.
FORALL i IN 1..rmt_runs_tab.COUNT
INSERT INTO plsql_profiler_runs
VALUES rmt_runs_tab(i);
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:
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);
Hello Gary,
thanks for the tip!
I must say that the dml_table_expression_clause syntax looks really horrible to me ;-)
Flavio
Post a Comment