Updating more than 1 rows in a oracle procedure

Question

0.00/5 (No votes)

See more:

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

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;
/

What I have tried:

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

Posted 15-Jul-19 13:08pm

Member 13870077

Updated 15-Jul-19 17:20pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2019-07-15T17:20:00

The error comes from the first statement where you try to fetch values multiple rows while the receiving variables (V_CURRENT_DATE, V_DATE_ENCAN) can hold only a single value.

But beyond that, as far as I can see the WHERE clause of the UPDATE statement is based purely on the received single variable values, you do not use any columns to restrict the rows. In other words, the UPDATE statement would update all rows or no rows at all, depending on the values....

So if the idea is to is to do the update based on the values of individual rows, try something like

SQL

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_EN > 60;

END TP3_SP_ARCHIVER_ENCAN;
/