Careful coding is a must in application development but, occasionally, the best laid plans go astray. Enter the ORA-00947 error, caused by queries and insert statements having too few values. Let’s look at a few examples of how this can happen.
A simple query can become a nightmare if the subquery doesn’t return the proper number of values:
SQL> select e.empno, e.ename, d.loc, m.ename
2 from emp e , emp m, dept d
3 where m.empno = e.mgr
4 and d.deptno = e.deptno
5 and e.deptno = m.deptno
6 and (d.deptno, d.dname) in (select deptno from emp)
7 /
and (d.deptno, d.dname) in (select deptno from emp)
*
ERROR at line 6:
ORA-00947: not enough values
SQL>
Yes, this was an obvious example, but it does illustrate the point. Insert statements are also not immune to returning such an error, through the values clause:
SQL> create table yingsplut(
2 hargen number,
3 neebo number,
4 snerm varchar2(75)
5 );
Table created.
SQL>
SQL> insert into yingsplut
2 values (17, 3)
3 /
insert into yingsplut
*
ERROR at line 1:
ORA-00947: not enough values
SQL>
Or through a select statement:
SQL> insert into yingsplut
2 select empno, sal from emp
3 /
insert into yingsplut
*
ERROR at line 1:
ORA-00947: not enough values
SQL>
Materialized view refreshes can also be affected if the materialized view was created with ‘select * from …’ and the source table definition has changed since the materialized view was created. The solution here is to recreate the materialized view using a query listing all of the desired columns explicitly. Yes, in some cases that won’t stop base table definition changes from ‘hosing’ your refresh, but simply adding columns to a base table definition won’t result in the refresh job generating an error.
Normally this error wouldn’t arise from such simple code; having a table with a large number of columns, though, can easily provide insert statements that miss the mark by one or more values simply because the developer lost count of default or NULL values:
SQL> create table valtest(
2 val_key number,
3 val1 varchar2(12),
4 val2 varchar2(12),
5 val3 varchar2(12),
6 val4 varchar2(12),
7 val5 varchar2(12),
8 val6 varchar2(12),
9 val7 varchar2(12),
10 val8 varchar2(12),
11 val9 varchar2(12),
12 val10 varchar2(12),
13 val11 varchar2(12),
14 val12 varchar2(12),
15 val13 varchar2(12),
16 val14 varchar2(12),
17 val15 varchar2(12),
18 val16 varchar2(12),
19 val17 varchar2(12),
20 val18 varchar2(12),
21 val19 varchar2(12),
22 val20 varchar2(12),
23 val21 varchar2(12),
24 val22 varchar2(12),
25 val23 varchar2(12),
26 val24 varchar2(12),
27 val25 varchar2(12),
28 val26 varchar2(12),
29 val27 varchar2(12),
30 val28 varchar2(12),
31 val29 varchar2(12),
32 val30 varchar2(12),
33 val31 varchar2(12),
34 val32 varchar2(12),
35 val33 varchar2(12),
36 val34 varchar2(12),
37 val35 varchar2(12),
38 val36 varchar2(12),
39 val37 varchar2(12)
40 );
Table created.
SQL>
SQL> insert into valtest
2 values (1,
3 'This',
4 'is',
5 'the',
6 'first',
7 'record',
8 'inserted',
9 'into',
10 'my',
11 'table',
12 'and',
13 'it''s',
14 'a',
15 'really',
16 'long',
17 'one',
18 'because',
19 'I',
20 'wanted',
21 'to',
22 'be',
23 'as',
24 'difficult',
25 'as',
26 'possible',
27 NULL,
28 NULL,
29 NULL,
30 NULL,
31 NULL);
insert into valtest
*
ERROR at line 1:
ORA-00947: not enough values
SQL>
PL/SQL blocks are not immune to this, either, when using BULK COLLECT if the collection is defined absent a column or two:
SQL> create table lotsa_data(
2 data_id number,
3 data_set number,
4 data_val varchar2(40),
5 proc_dt date
6 );
Table created.
SQL>
SQL> create table less_data(
2 data_id number,
3 data_set number,
4 data_val varchar2(40)
5 );
Table created.
SQL>
SQL> create table ref_data(
2 data_id number,
3 data_set number
4 );
Table created.
SQL>
SQL> begin
2 for i in 1..10000 loop
3 insert into lotsa_data
4 values(i, mod(i, 17), 'Test data statement '||i, sysdate);
5 insert into ref_data
6 values(i, mod(i,17));
7 end loop;
8
9 commit;
10
11 end;
12 /
PL/SQL procedure successfully completed.
SQL>
SQL> declare
2 type ldat_tab_typ is table of less_data%rowtype index by binary_integer;
3
4 d_tab ldat_tab_typ;
5 begin
6 update lotsa_data
7 set data_id = data_id + 10
8 where data_set = 16
9 returning data_id, data_set, data_val, proc_dt bulk collect into d_tab;
10
11 for i in 1..d_tab.count loop
12 dbms_output.put_line(d_tab(i).data_id||' '||d_tab(i).data_set||' '||d_tab(i).data_val);
13 end loop;
14 end;
15 /
returning data_id, data_set, data_val, proc_dt bulk collect into d_tab;
*
ERROR at line 9:
ORA-06550: line 9, column 79:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 6, column 9:
PL/SQL: SQL Statement ignored
SQL>
[If you go the other way, and have too many columns in your collection an ORA-00913 appears:
SQL> declare
2 type ldat_tab_typ is table of lotsamore_data%rowtype index by binary_integer;
3
4 d_tab ldat_tab_typ;
5 begin
6 update lotsa_data
7 set data_id = data_id + 10
8 where data_set = 16
9 returning data_id, data_set, data_val, proc_dt bulk collect into d_tab;
10
11 for i in 1..d_tab.count loop
12 dbms_output.put_line(d_tab(i).data_id||' '||d_tab(i).data_set||' '||d_tab(i).data_val);
13 end loop;
14 end;
15 /
returning data_id, data_set, data_val, proc_dt bulk collect into d_tab;
*
ERROR at line 9:
ORA-06550: line 9, column 79:
PL/SQL: ORA-00913: too many values
ORA-06550: line 6, column 9:
PL/SQL: SQL Statement ignored
SQL>
The solution to both conditions is the same, so read on.]
Troubleshooting such code is a time-consuming process, as the offending VALUES statements, SELECT queries or collection variables need to be carefully examined and the argument count/column count verified with the number of columns in the inserted table. Let’s take the very first example and fix it:
SQL> create table ref_dept
2 as select deptno, dname, loc
3 from dept
4 where deptno in (10, 30, 70);
Table created.
SQL>
SQL> select e.empno, e.ename, d.loc, m.ename
2 from emp e , emp m, dept d
3 where m.empno = e.mgr
4 and d.deptno = e.deptno
5 and e.deptno = m.deptno
6 and (d.deptno, d.dname) in (select deptno, dname from ref_dept)
7 /
EMPNO ENAME LOC ENAME
---------- ---------- ------------- ----------
7934 MILLER NEW YORK CLARK
7782 CLARK NEW YORK KING
7499 ALLEN CHICAGO BLAKE
7654 MARTIN CHICAGO BLAKE
7900 JAMES CHICAGO BLAKE
7844 TURNER CHICAGO BLAKE
7521 WARD CHICAGO BLAKE
7 rows selected.
SQL>
Notice we created a new reference table, and populated it with the desired values. The reference table was then used in the subquery to return the proper number of values, along with the correct department numbers and names, to return the expected result set. Sharp eyes will note that we didn’t need the reference table as we could have queried the DEPT table again with the qualifying WHERE clause:
SQL> select e.empno, e.ename, d.loc, m.ename
2 from emp e , emp m, dept d
3 where m.empno = e.mgr
4 and d.deptno = e.deptno
5 and e.deptno = m.deptno
6 and (d.deptno, d.dname) in (select deptno, dname from dept where deptno in (10,30,70))
7 /
EMPNO ENAME LOC ENAME
---------- ---------- ------------- ----------
7934 MILLER NEW YORK CLARK
7782 CLARK NEW YORK KING
7499 ALLEN CHICAGO BLAKE
7654 MARTIN CHICAGO BLAKE
7900 JAMES CHICAGO BLAKE
7844 TURNER CHICAGO BLAKE
7521 WARD CHICAGO BLAKE
7 rows selected.
SQL>
Correcting the INSERT statement takes a bit more time, since the column count and value count must match. But time and patience are rewarded:
SQL> insert into valtest
2 values (1,
3 'This',
4 'is',
5 'the',
6 'first',
7 'record',
8 'inserted',
9 'into',
10 'my',
11 'table',
12 'and',
13 'it''s',
14 'a',
15 'really',
16 'long',
17 'one',
18 'because',
19 'I',
20 'wanted',
21 'to',
22 'be',
23 'as',
24 'difficult',
25 'as',
26 'possible',
27 'since',
28 'my',
29 'goldfish',
30 'can''t',
31 'dance',
32 'after',
33 'thirteen',
34 'cups',
35 'of',
36 'grape',
37 'Kool-Aid',
38 'from',
39 'Duluth');
1 row created.
SQL>
Fixing the last example takes nothing more than changing the PL/SQL table to contain all of the referenced columns:
SQL> declare
2 type ldat_tab_typ is table of lotsa_data%rowtype index by binary_integer;
3
4 d_tab ldat_tab_typ;
5 begin
6 update lotsa_data
7 set data_id = data_id + 10
8 where data_set = 16
9 returning data_id, data_set, data_val, proc_dt bulk collect into d_tab;
10
11 for i in 1..d_tab.count loop
12 dbms_output.put_line(d_tab(i).data_id||' '||d_tab(i).data_set||' '||d_tab(i).data_val);
13 end loop;
14 end;
15 /
349 16 Test data statement 339
366 16 Test data statement 356
383 16 Test data statement 373
400 16 Test data statement 390
[... much more output here ...]
9393 16 Test data statement 9383
9410 16 Test data statement 9400
9427 16 Test data statement 9417
9444 16 Test data statement 9434
9461 16 Test data statement 9451
9478 16 Test data statement 9468
9495 16 Test data statement 9485
9512 16 Test data statement 9502
9529 16 Test data statement 9519
PL/SQL procedure successfully completed.
SQL>
There is nothing more satisfying than the joy of a job well done. And there’s nothing more frustrating than having an error like an ORA-00947 which somehow eludes correction. Sometimes it’s best to let another pair of eyes peruse the code, as a fresh viewpoint can help reveal mistakes often overlooked by the original programmer, and can be of great assistance in fixing any problematic code. I’m not afraid to ask for help. You shouldn’t be, either.
Now, let’s code!
Oracle Tips and Tricks -- David Fitzjarrell 
Leave a comment