Thursday, 14 August 2014

Cursor with Update statement in pl/sql


Set Serveroutput On
DECLARE
  cnt number := 0;
  CURSOR c1 IS SELECT distinct segment1,attribute7,organization_id FROM mtl_system_items_b
                 where segment1 in ('PRF3-105XXC',
'PRIME-3U-6152',
'PREF-3R-12005-PT16')
AND ATTRIBUTE7=1651
AND organization_id in(5760,
5204)
ORDER BY segment1;

BEGIN
cnt := 0;
For i in C1 loop
    update mtl_system_items_b
    set attribute7=1713,
    last_update_date=sysdate,
    last_updated_by='1016485'
    where segment1=i.segment1
    and attribute7=i.attribute7
    and organization_id=i.organization_id;
    cnt := cnt +1;
END LOOP;
DBMS_OUTPUT.PUT_LINE (cnt || 'row(s) updated from mtl_system_items_b');

EXCEPTION
WHEN OTHERS
THEN
   DBMS_OUTPUT.PUT_LINE ('Updation Failed' || SQLERRM);
Rollback;
END;
/

No comments:

Post a Comment