Error compiling an Oracle PL / SQL trigger through SQL * PLUS

Question

I had a problem compiling an Oracle trigger via SQL * PLUS. I don’t think I'm stupid, but I don’t understand what the problem is.

We have a script installer, which is a batch file that creates / updates all objects in the database, calling SQLPLUS on several scripts, each of which contains one view, trigger, etc. Tables and views are created first, then triggers. The V_BS_GRIDFIELDS below may or may not be created at this point, or may be created later by another process. A view is an updatable view, so we have a trigger that fits on it to push updates for different tables, as shown 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 problem is that ORACLE SQL * PLUS seems to stop compiling the trigger after the first empty 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 delete an empty line in line 6, it will stop compiling at the first semicolon, in 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 many triggers created this way, and they all have spaces, semicolons, etc. and are created OK. I tested and saw the same problem on Oracle 9, 10, 11. Can anyone shed some light on this?

Thanks.

Answer 1

in the default settings, SQL * Plus will not work correctly with empty lines, you will need to run the following command:

 SQL> SET SQLBLANKLINES on 

See this other SO .

Update: I answered too quickly, an empty line does not seem to be a problem. I tried my code in my database and the problem seems to come from the FORCE keyword. 10gR2 documentation does not mention this keyword. A trigger compiles when it is deleted.