ora-01422 Fetch Size

Question

I am getting the ORA-1422 error. Here is the error:

Connecting to the database Quantum Train. ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "TRAIN.UPDATE_MASTER_TO_NULL", line 26 ORA-06512: at line 2 Process exited. Disconnecting from the database Quantum Train.

PROCEDURE UPDATE_MASTER_TO_NULL

is

-- This gets the pnm_auto_keys for the records in the warehouse and location with the specified  manufacturer

    Cursor Csr is
    Select pnm.loc_auto_key
    from parts_master pnm join warehouse whs on pnm.whs_auto_key = whs.whs_auto_key
    where whs.warehouse_code = 'SHOP' and
            pnm.loc_auto_key <> '39';
    --      pnm.loc_auto_key <> '39' and
    --      pnm.loc_auto_key <> '26' and
    --      pnm.loc_auto_key <> '14';
        
     
          
     loc_key integer;

    Begin

    For i in Csr Loop

-- Now get the loc_auto_key for your new location

    Select loc2.loc_auto_key
    into loc_key
    From PARTS_MASTER loc2
    Where loc2.loc_auto_key is null;

--  Assigne the new loc_auto_key to the selected record.

    Update parts_master pnm2
    Set pnm2.loc_auto_key = loc_key
    where pnm2.loc_auto_key = i.loc_auto_key;

    End Loop;

    Commit;

    End UPDATE_MASTER_TO_NULL;

Thanks,

Jeff

Does [this](https://stackoverflow.com/q/19779483/4808122) help? You are selecting more than one row in the `SELECT INTO`.. — Marmite Bomber, Oct 02 '20 at 15:31
If I had to do a wild guess , did you just forget to put the where clause while selecting from `PARTS_MASTER` as `loc2.loc_auto_key = i.loc_auto_key`? — Sujitmohanty30, Oct 02 '20 at 15:39
I didn't think I was returning more than 1 row. I see the where clause in this. — Jeff, Oct 02 '20 at 16:05
What is that query supposed to find though? The select list and where clause refer to the same column so it can only ever return zero, one or many null values. The comment refers to a 'new location', so should you be filtering on some other column? — Alex Poole, Oct 02 '20 at 16:47
I think that is the problem. I am finding the data on the same field as I am updating. I can't really update on different criteria. — Jeff, Oct 02 '20 at 17:44

score 0 · Answer 1 · answered Oct 02 '20 at 15:33

The problem is that this select statement is returning more than one row

Select loc2.loc_auto_key
into loc_key
From PARTS_MASTER loc2
Where loc2.loc_auto_key is null;

If you modify that query to return only one row, then your code should work.

Also, the entire procedure can be replaced and improved in efficiency by using a single update statement similar to this:

UPDATE parts_master pm
   SET pm.loc_auto_key =
           (SELECT *
              FROM (SELECT pm2.loc_auto_key
                      FROM PARTS_MASTER pm2
                     WHERE pm2.loc_auto_key IS NULL)
             WHERE ROWNUM = 1)
 WHERE     pm.whs_auto_key IN (SELECT w.whs_auto_key
                                 FROM warehouse w
                                WHERE w.warehouse_code = 'SHOP')
       AND pm.loc_auto_key <> '39';

Hi EJ, I thought I was returning only one row for the loop. Not sure what I am missing. I like your code I am just not used to it yet. When compiling I get the error "encountered the symbol UPDATE..... — Jeff, Oct 02 '20 at 16:11
Hi EJ, I am getting the hang of your code and it is a bit slower but easier to work with than a loop, csr. — Jeff, Oct 02 '20 at 17:45

ora-01422 Fetch Size

1 Answers1