Oracle Tips and Tricks — David Fitzjarrell

October 1, 2008

I Need More Stuff

Filed under: General — dfitzjarrell @ 13:10

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!

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: