Updating more than 1 row - Oracle SQL Procedure

Question

I was wondering if it was possible to update more than 1 row with a procedure, im not sure why this one isnt working. Its working only if theres only 1 row in my table. But if there's more than 1 row i get the usual error message :

ORA-01422: exact fetch returns more than requested number of rows

I'm honestly not sure why this isnt working. Is it possible a procedure cannot update more than 1 row at once ?

create or replace procedure TP3_SP_ARCHIVER_ENCAN
is

    V_CURRENT_DATE date;
    V_DATE_ENCAN date;

begin
    select sysdate, DATE_FIN_ENC into V_CURRENT_DATE, V_DATE_ENCAN 
from 
TP2_ENCAN;

    update TP2_ENCAN
    set EST_ARCHIVEE_ENC = 1,
    STATUT_ENC = 'Archivé'
    where V_CURRENT_DATE - V_DATE_ENCAN > 60;


end TP3_SP_ARCHIVER_ENCAN;
/

I'm excepting to archive every ENCAN that has been closed for a duration of 60+ days. everytime i run this procedure i just want to update those.

Full error message :

Error report - ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "C##JALAC144.TP3_SP_ARCHIVER_ENCAN", line 8 ORA-06512: at line 1 01422. 00000 - "exact fetch returns more than requested number of rows" *Cause: The number specified in exact fetch is less than the rows returned. *Action: Rewrite the query or change number of rows requested

Does your error message give you a line number where the error is occurring? Hint: I don't think it's the update statement that is throwing the error. — Sam M, Jul 15 '19 at 23:37
Possible duplicate of [PL/SQL ORA-01422: exact fetch returns more than requested number of rows](https://stackoverflow.com/questions/19779483/pl-sql-ora-01422-exact-fetch-returns-more-than-requested-number-of-rows) — Sam M, Jul 15 '19 at 23:38

score 0 · Answer 1 · answered Jul 16 '19 at 00:56

This line is your problem:

select sysdate, DATE_FIN_ENC into V_CURRENT_DATE, V_DATE_ENCAN from TP2_ENCAN;

You are selecting DATE_FIN_ENC into a scalar variable that can hold exactly one value. You can select "1" into "x". You can't select "1" and "2" into "x" at the same time. So you get the error you're getting.

If I understand your problem correctly, you probably want this, with no initial select:

update TP2_ENCAN
   set EST_ARCHIVEE_ENC = 1,
       STATUT_ENC = 'Archivé'
 where SYSDATE - DATE_FIN_ENC > 60;

Ahh i see, for some reason i thought the select into statement had to be used in a procedure, thanks — user9939473, Jul 16 '19 at 03:06

score 0 · Answer 2 · edited Jul 16 '19 at 06:09

0

Via your code you just want to update record base on current date. Therefore, you do not need to use parameter. Using the update script is enough.

Create or replace procedure TP3_SP_ARCHIVER_ENCAN is:

begin
    update TP2_ENCAN
    set EST_ARCHIVEE_ENC = 1,
    STATUT_ENC = 'Archivé'
    where sysdate - DATE_FIN_ENC > 60;

end 
TP3_SP_ARCHIVER_ENCAN;

edited Jul 16 '19 at 06:09

double-beep

5,031
17
33
41

answered Jul 16 '19 at 03:12

Soth Sopheak Trea

1

Updating more than 1 row - Oracle SQL Procedure

2 Answers2