That's Fetching!

Sometimes, despite the best of intentions, things don't go exactly as planned. And that's really annoying when you're writing database-centric applications and the presumptions you made regarding the data turn out to be ... wrong. One possible undesired outcome revolves around returning more rows than initially expected. And Oracle has a couple of ways to tell us that we need to re-think how our code processes that data.

Let's build a couple of tables and use them to illustrate these points:

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 ref_data(
  2        data_id number,
  3        data_set number
  4  );

Table created.

SQL> 

So, let's now load those tables with data:

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> 

Now comes the fun part: let's use a PL/SQL block to update some of the records in the LOTSA_DATA table. We'll use the RETURNING INTO clause to attempt to retrieve values from the modified records:

SQL> declare
  2        dataid number;
  3        dataset number;
  4        dval varchar2(40);
  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 into dataid, dataset, dval;
 10  end;
 11  /
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows 
ORA-06512: at line 6 


SQL> 

It appears we've updated far more than one row, and Oracle wasn't exactly happy about that. We expected to modify one row of data, and we updated a considerably greater number than that, so the variables which were intended to contain the returned values couldn't process that request. Oracle throws the ORA-01422 error to indicate we'd overflow the placeholders and, well, it won't allow that to happen. Can we fix this so it will work? Certainly; we'll use a collection and BULK COLLECT instead:

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  /
26  16  Test data statement 16 
43  16  Test data statement 33 
60  16  Test data statement 50 
77  16  Test data statement 67
94  16  Test data statement 84
111  16  Test data statement 101 
128  16  Test data statement 118 
145  16  Test data statement 135
162  16  Test data statement 152
179  16  Test data statement 169
349  16  Test data statement 339
366  16  Test data statement 356
383  16  Test data statement 373
400  16  Test data statement 390
417  16  Test data statement 407
434  16  Test data statement 424
451  16  Test data statement 441
468  16  Test data statement 458
485  16  Test data statement 475
502  16  Test data statement 492
519  16  Test data statement 509
536  16  Test data statement 526
553  16  Test data statement 543
570  16  Test data statement 560
587  16  Test data statement 577
604  16  Test data statement 594
621  16  Test data statement 611
638  16  Test data statement 628
655  16  Test data statement 645
672  16  Test data statement 662
689  16  Test data statement 679
706  16  Test data statement 696
723  16  Test data statement 713
740  16  Test data statement 730
757  16  Test data statement 747
[... lots more data here ...]
7591  16  Test data statement 7581
7608  16  Test data statement 7598
7625  16  Test data statement 7615
7642  16  Test data statement 7632
[... and more here ...]
9580  16  Test data statement 9570
9597  16  Test data statement 9587
9614  16  Test data statement 9604
9631  16  Test data statement 9621
9648  16  Test data statement 9638
9665  16  Test data statement 9655
9682  16  Test data statement 9672
9699  16  Test data statement 9689
9716  16  Test data statement 9706
9733  16  Test data statement 9723
9750  16  Test data statement 9740
9767  16  Test data statement 9757
9784  16  Test data statement 9774
9818  16  Test data statement 9808
9835  16  Test data statement 9825
9852  16  Test data statement 9842
9869  16  Test data statement 9859
9886  16  Test data statement 9876
9903  16  Test data statement 9893
9920  16  Test data statement 9910
9937  16  Test data statement 9927
9954  16  Test data statement 9944
9971  16  Test data statement 9961
9988  16  Test data statement 9978
10005  16  Test data statement 9995

PL/SQL procedure successfully completed.

SQL> 

Voila!! No error generated, and the update succeeded. What a comforting thought. (Note that not all of the result set has been included, as it was a LONG list.)

(If this error is generated by internal code accessing data dictionary views [say, when using the exp or imp utilities] it's likely that one or more views have been corrupted and need to be rebuilt. The easist way to do that is to:

Shutdown the database
Take a cold backup
Start it in restricted mode
Run $ORACLE_HOME/rdbms/admin/catalog.sql to rebuild the data dictionary

Do not proceed until you have a good cold backup of the database in its current state, as something could go horribly wrong with the catalog rebuild requiring a restore and a call to Oracle support.

This isn't likely to occur, but stranger things have happened. If you're not comfortable with this then contact Oracle support and have them assist you in resolving the issue.)

There are occasions when Oracle errors checking for extra rows in an exact fetch and returns an ORA-01423 message. This error is the 'tip of the iceberg', as other errors which caused/contribute to this are reported. After receiving one of these errors it's necessary to check the entire error stack to reveal the underlying problem.

Of course an ORA-01422 isn't the only error Oracle can generate when there are too many rows:

SQL> select data_id, data_set, data_val, proc_dt
  2  from lotsa_data
  3  where data_id = (select data_id from ref_data where data_set = 16)
  4  /
where data_id = (select data_id from ref_data where data_set = 16)
                 *
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row 


SQL> 

This one is easier to fix, as it requires only a change from '=' to 'IN':

SQL> select data_id, data_set, data_val, proc_dt
  2  from lotsa_data
  3  where data_id IN (select data_id from ref_data where data_set = 16)
  4  /

[Lots of data returned]

SQL>

It's good to know the data an application can generate, but it's also good to know how to fix coding blunders and missteps should they arise because the data didn't match the initial assumptions; someone (yes, maybe even you) may make a mistake. It's no crime, we all make them. And being able to recover from them is the key to successful application design and implementation.

And that, in the vernacular of old, is 'fetching'.