title: Oracle Pl/SQL trigger compilation error via SQL*PLUS
body: <p>I have a problem with compiling an Oracle trigger via SQL*PLUS - I don't think I'm being dumb but I can't see what the problem is.</p>
  
  <p>We have an installer script which is essentially a batch file which creates/refreshes all the objects in the database by calling SQLPLUS on multiple scripts, each containing one view, trigger, etc. The tables and views are created first, then then triggers. The <code>V_BS_GRIDFIELDS</code> view below may or may not be created at this point, or may be created later by a different process. The view is an updatable view, so we have a trigger placed on it to push updates to different tables, as below:</p>
  
  <pre><code>CREATE OR REPLACE FORCE TRIGGER TR_INSTUPD_BS
    INSTEAD OF INSERT OR UPDATE OR DELETE 
    ON V_BS_GRIDFIELDS
  FOR EACH ROW
  BEGIN
  
    IF INSERTING OR DELETING THEN
      NULL;
    END IF;
  
    IF UPDATING THEN
      -- Can only change these fields
      IF (:OLD.VISIBLE &lt;&gt; :NEW.VISIBLE) OR (:OLD.COMPULSORY &lt;&gt; :NEW.COMPULSORY) THEN 
  
        -- Source Table = BS_GRIDFIELDS
        IF (:OLD.SOURCE_TYPE = 0) THEN
  
          UPDATE BS_GRIDFIELDS BS_GF
             SET BS_GF.VISIBLE    = :NEW.VISIBLE,
                 BS_GF.COMPULSORY = :NEW.COMPULSORY
           WHERE BS_GF.FIELD_NAME = :OLD.FIELD_NAME;
  
        END IF;
      END IF;
    END IF;
  END;
  </code></pre>
  
  <p>The issue is that oracle SQL*PLUS seems to stop compiling the trigger after the first blank line, on line 6:</p>
  
  <pre><code>SQL&gt; @"TR_INSTUPD_BS.sql";
  SP2-0734: unknown command beginning "IF INSERTI..." - rest of line ignored.
  SP2-0042: unknown command "NULL" - rest of line ignored.
  SP2-0042: unknown command "END IF" - rest of line ignored.
  SP2-0734: unknown command beginning "IF UPDATIN..." - rest of line ignored.
  SP2-0044: For a list of known commands enter HELP
  and to leave enter EXIT.
  SP2-0734: unknown command beginning "IF (:OLD.V..." - rest of line ignored.
  SP2-0734: unknown command beginning "IF (:OLD.S..." - rest of line ignored.
  SP2-0552: Bind variable "OLD" not declared.
  SP2-0042: unknown command "END IF" - rest of line ignored.
  SP2-0042: unknown command "END IF" - rest of line ignored.
  SP2-0042: unknown command "END IF" - rest of line ignored.
  SP2-0042: unknown command "END" - rest of line ignored.
  SP2-0044: For a list of known commands enter HELP
  and to leave enter EXIT.
  </code></pre>
  
  <p>If you remove the blank line on line 6, it seems to stop compiling at the first semicolon, on line 7:</p>
  
  <pre><code>SQL&gt; @"TR_INSTUPD_BS.sql";
  
  Warning: Trigger created with compilation errors.
  
  SP2-0042: unknown command "END IF" - rest of line ignored.
  SP2-0734: unknown command beginning "IF UPDATIN..." - rest of line ignored.
  SP2-0734: unknown command beginning "IF (:OLD.V..." - rest of line ignored.
  SP2-0734: unknown command beginning "IF (:OLD.S..." - rest of line ignored.
  SP2-0552: Bind variable "OLD" not declared.
  SP2-0042: unknown command "END IF" - rest of line ignored.
  SP2-0042: unknown command "END IF" - rest of line ignored.
  SP2-0042: unknown command "END IF" - rest of line ignored.
  SP2-0042: unknown command "END" - rest of line ignored.
  SP2-0044: For a list of known commands enter HELP
  and to leave enter EXIT.
  SQL&gt;
  </code></pre>
  
  <p>We have lots of triggers created in this way, and all of them have spaces, semicolons, etc, and get created OK. I've tested and seen the same issue on Oracle 9, 10, 11. Can anyone shed light on this?</p>
  
  <p>Thanks.</p>