Oracle Pl/SQL trigger compilation error via SQL*PLUS

Question

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.

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 V_BS_GRIDFIELDS 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:

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 <> :NEW.VISIBLE) OR (:OLD.COMPULSORY <> :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;

The issue is that oracle SQL*PLUS seems to stop compiling the trigger after the first blank line, on line 6:

SQL> @"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.

If you remove the blank line on line 6, it seems to stop compiling at the first semicolon, on line 7:

SQL> @"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>

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?

Thanks.

Answer 1

Hi Kieran,

in its default setting SQL*Plus won't deal properly with blank lines, you need to issue the following command:

SQL> SET SQLBLANKLINES on

See this other SO.

Update: I answered too fast, the blank line doesn't seem to be the problem here. I tried your code on my database and the issue seems to come from the FORCE keyword. The 10gR2 documentation doesn't mention this keyword. The trigger compiles when you remove it.