Creato da pastuweb.com
Share My Page
My Social Accounts
Account FaceBook Account LinkedIn Account Twitter Account Google Plus Account Git Hub

Here there is the procedure:

CREATE OR REPLACE PROCEDURE <user>.Ricompila_triggers IS

  cmd    varchar2(255) := null;
  retval number := 0;
  DESCRIPTION varchar2(255):=null;

  CURSOR SelTrigger IS
    select uo.object_name
      from user_objects uo, user_triggers ut
     where uo.object_name = ut.trigger_name
       and uo.status = 'INVALID'
       and uo.object_type = 'TRIGGER'
       and ut.status = 'DISABLED';

  Rec SelTrigger%ROWTYPE;

begin

  OPEN SelTrigger;
  LOOP
    FETCH SelTrigger
      into Rec;
    EXIT WHEN SelTrigger%NOTFOUND;
  
    cmd := 'ALTER TRIGGER ' || Rec.object_name || ' COMPILE';
    DBMS_OUTPUT.PUT_LinE('Comando --> ' || cmd);
    retval := plsql_system(cmd);
    DBMS_OUTPUT.PUT_LinE('Comando eseguito --> ' || retval);
  
  END LOOP;

EXCEPTION
  when others then
    DBMS_OUTPUT.PUT_LinE('Non va il comando ' || cmd);
    DESCRIPTION := sqlerrm(sqlcode);
    dbms_output.put_line (description);    
END;
/

Average (1 Vote)
The average rating is 5.0 stars out of 5.