CODEWITHSUNDEEP
plsqloracle11goracle-sqldeveloper
user3544027
user3544027

Reputation: 41

Updating columns within a Cursor For Loop

I'm running a CURSOR FOR LOOP, and among the things I'm trying to do, is to update a column for each record, with the day on which the loop was run. I've included the following statement within the loop

UPDATE SALES_TABLE
SET SETTLEMENTDATE = SYSDATE
WHERE RECEIPTNO= R1.RECEIPTNO;

In this case, RECEIPTNO is a PK, if that makes any difference. Any idea what I'm doing wrong? The SETTLEMENTDATE column just remains NULL.

UPDATED WITH GREATER FOR LOOP VISIBILITY

FOR R1 IN c_dbfData LOOP

...

UPDATE SALES_TABLE
SET SETTLED = 'Y'
WHERE RECEIPTNO = R1.RECEIPTNO;

UPDATE SALES_TABLE
SET SETTLEMENTDATE = SYSDATE
WHERE RECEIPTNO= R1.RECEIPTNO;

END LOOP;

All I've excluded with the ellipsis is just writing to a file, which works fine.

Upvotes: 0

Views: 94

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

I suggest you modify your code as follows:

DBMS_OUTPUT.PUT_LINE('Entering update loop');

DECLARE
  nRows_read  NUMBER := 0;
BEGIN
  FOR R1 IN c_dbfData LOOP
    nRows_read := nRows_read + 1;

    DBMS_OUTPUT.PUT_LINE('In loop, R1.RECEIPTNO=' || R1.RECEIPTNO);

    BEGIN
       ...  -- put your file logic here
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error in file logic: SQLCODE=' || SQLCODE ||
                           '  SQLERRM=' || SQLERRM);
        RAISE;
    END;

    BEGIN
      UPDATE SALES_TABLE
        SET SETTLED = 'Y'
        WHERE RECEIPTNO = R1.RECEIPTNO;

      DBMS_OUTPUT.PUT_LINE('  First update successful for RECEIPTNO=' ||
                           R1.RECEIPTNO ||  
                           ', ' || SQL%ROWCOUNT || ' rows updated');
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error in first UPDATE for RECEIPTNO=' ||
                             R1.RECEIPTNO || ': SQLCODE=' || SQLCODE ||
                             '  SQLERRM=' || SQLERRM);
        RAISE;
    END;

    BEGIN
      UPDATE SALES_TABLE
        SET SETTLEMENTDATE = SYSDATE
        WHERE RECEIPTNO= R1.RECEIPTNO;

      DBMS_OUTPUT.PUT_LINE('  Second update successful for RECEIPTNO=' ||
                           R1.RECEIPTNO ||  
                           ', ' || SQL%ROWCOUNT || ' rows updated');
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error in second UPDATE for RECEIPTNO=' ||
                             R1.RECEIPTNO || ': SQLCODE=' || SQLCODE ||
                             '  SQLERRM=' || SQLERRM);
        RAISE;
    END;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Update loop complete, ' || nRows_read || ' rows read');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error caught in outer handler after ' || nRows_read ||
                         'rows: SQLCODE=' || SQLCODE ||
                         '  SQLERRM=' || SQLERRM');
    RAISE;
END;

Then examine the DBMS_OUTPUT from the above. You should ideally see a list of the RECEIPTNO's which were processed, and a line for each update noting how many rows were altered by the update.

Alternatively, if you're using a tool such as PL/SQL Developer which provides easy access to the Oracle PL/SQL debugger you could leave out the internal PUT_LINE's and just set breakpoints on the PUT_LINE calls in the EXCEPTION blocks and then run your procedure under the debugger to see if it hits either of the breakpoints.

Share and enjoy.

Upvotes: 2

doberkofler
doberkofler

Reputation: 10341

I it's not a problem with the commit logic, something must be off with the where clause (RECEIPTNO=R1.RECEIPTNO). Either R1.RECEIPTNO does not contain the values you would expect or the table sales_table is not populated as you expect.

Generally speaking: - it would be twice as fast, to combine the two update sin your example - even faster if you can update in a single statement without the procedural loop

Upvotes: 0

Related Questions