Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2005
    Posts
    17

    Question Unanswered: Error enabling trigger via script

    CREATE OR REPLACE PROCEDURE FMEP.SP_ENABLE_PK_TRIGGERS AS
    BEGIN
    alter trigger fin.bank_account_bf_i_tr enable;
    END;

    Error(3,2): PLS-00103: Encountered the symbol "ALTER" when expecting one of the following: begin case declare exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe The symbol "declare was inserted before "ALTER" to continue.


    Also, is there a way that I can enable all triggers that are valid?
    There are a number of triggers that are disabled and have syntax errors.. I want to loop through the trigger list and enable only triggers that have no errors.

    OR
    How do I loop throught the trigger list and try to enable all triggers ignoring compile errors..


    Thanks for your expertise... I have 3 weeks to complete insane tasks... create stored procedures.. modify data... and oh.. learn PL/SQL and Oracle.!!!

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Look into EXECUTE IMMEDIATE.
    -cf

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Code:
    CREATE OR REPLACE PROCEDURE FMEP.SP_ENABLE_PK_TRIGGERS AS
    
    cursor triggers as
    select owner,trigger_name
    from all_triggers
    where status='DISABLED';
    
    BEGIN
    for pnt in triggers loop
      begin
        execute immediate 'alter trigger '||pnt.owner||'.'||pnt.TRIGGER_name||' enable';
      EXCEPTION
        WHEN OTHERS THEN
          NULL;
      END;
    END LOOP;
    
    END;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Aug 2005
    Posts
    17
    Error generated

    Error(4,8): PLS-00103: Encountered the symbol "TRIGGERS" when expecting one of the following: := . ( @ % ;

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Sorry, I had a typo.

    Code:
    CREATE OR REPLACE PROCEDURE FMEP.SP_ENABLE_PK_TRIGGERS AS
    
    cursor get_triggers is
    select owner,trigger_name
    from all_triggers
    where status='DISABLED';
    
    BEGIN
    for pnt in get_triggers loop
      begin
        execute immediate 'alter trigger '||pnt.owner||'.'||pnt.TRIGGER_name||' enable';
      EXCEPTION
        WHEN OTHERS THEN
          NULL;
      END;
    END LOOP;
    
    END;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Aug 2005
    Posts
    17

    Thumbs up

    Thank you so much.
    Saved my bacon.!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •