The SP is working fine in TOAD but not working in SQL*PLUS. It is complaining about the new keyword being used in the trigger. I am using SET SQLBLANKLINES ON statement as well as I have read it might because of the blank space but still getting error, not sure what is wrong with syntax of the trigger looks fine to me but it is giving the below error

SET SQLBLANKLINES ON;
SET DEFINE OFF;

BEGIN 
DECLARE
  cnt_master   NUMBER;
BEGIN
   SELECT COUNT (1)
   INTO cnt_master
   FROM all_objects
   WHERE
         UPPER (object_type) = UPPER ('TABLE')
         AND UPPER (object_name) = UPPER ('USER_LNG_DAILY_LIMITS_COMP')
         AND UPPER (Owner) IN (SELECT SYS_CONTEXT ('USERENV','CURRENT_SCHEMA') FROM DUAL);

  IF cnt_master = 0
  THEN
     BEGIN
        EXECUTE IMMEDIATE ('
        CREATE TABLE USER_LNG_DAILY_LIMITS_COMP
        (
            COMPONENT_ID      NUMBER(10) PRIMARY KEY NOT NULL, 
            TITLE             VARCHAR2 (255),
            USER_ID           NUMBER(10),
            LAST_UPDATE       DATE
        )');
        EXECUTE IMMEDIATE
           ('GRANT DELETE, INSERT, SELECT, UPDATE ON USER_LNG_DAILY_LIMITS_COMP TO OLF_USER');
        EXECUTE IMMEDIATE
           ('GRANT SELECT ON  USER_LNG_DAILY_LIMITS_COMP TO OLF_READONLY');
     END;
  END IF;
END;
END;

                
 BEGIN 
 DECLARE
   cnt_master   NUMBER;
 BEGIN
   SELECT COUNT (1)
   INTO cnt_master
   FROM all_objects
   WHERE
         UPPER (object_type) = UPPER ('SEQUENCE')
         AND UPPER (object_name) = UPPER ('USER_LNG_DAILY_LIMITS_COMP_SQ')
         AND UPPER (Owner) IN (SELECT SYS_CONTEXT ('USERENV','CURRENT_SCHEMA') FROM DUAL);

  IF cnt_master = 0
  THEN
     BEGIN
        EXECUTE IMMEDIATE
           ('CREATE SEQUENCE USER_LNG_DAILY_LIMITS_COMP_SQ START WITH 1 INCREMENT BY 1');
     END;
  END IF;
 END;
 END;


CREATE OR REPLACE trigger USER_LNG_DAILY_LIMITS_COMP_TR
BEFORE INSERT ON USER_LNG_DAILY_LIMITS_COMP
FOR EACH ROW
BEGIN
      SELECT USER_LNG_DAILY_LIMITS_COMP_SQ.NEXTVAL INTO :new.COMPONENT_ID FROM dual;          
END;

CodePudding user response：

You need a slash (/) on its own on a new line to terminate and run a PL/SQL block. From the SQL*Plus documentation:

SQL*Plus stores the subprograms you enter in the SQL buffer. Execute the current subprogram with a RUN or slash (/) command. A semicolon (;) is treated as part of the PL/SQL subprogram and will not execute the command.

So your script structure would need to be:

SET SQLBLANKLINES ON;
SET DEFINE OFF;

DECLARE
  cnt_master   NUMBER;
BEGIN
  SELECT COUNT (1)
  INTO cnt_master
  FROM all_objects
  WHERE
        UPPER (object_type) = UPPER ('TABLE')
        AND UPPER (object_name) = UPPER ('USER_LNG_DAILY_LIMITS_COMP')
        AND UPPER (Owner) IN (SELECT SYS_CONTEXT ('USERENV','CURRENT_SCHEMA') FROM DUAL);

  IF cnt_master = 0
  THEN
     BEGIN
        EXECUTE IMMEDIATE ('
        CREATE TABLE USER_LNG_DAILY_LIMITS_COMP
        (
            COMPONENT_ID      NUMBER(10) PRIMARY KEY NOT NULL, 
            TITLE             VARCHAR2 (255),
            USER_ID           NUMBER(10),
            LAST_UPDATE       DATE
        )');
        EXECUTE IMMEDIATE
           ('GRANT DELETE, INSERT, SELECT, UPDATE ON USER_LNG_DAILY_LIMITS_COMP TO OLF_USER');
        EXECUTE IMMEDIATE
           ('GRANT SELECT ON  USER_LNG_DAILY_LIMITS_COMP TO OLF_READONLY');
     END;
  END IF;
END;
/
                
DECLARE
  cnt_master   NUMBER;
BEGIN
  SELECT COUNT (1)
  INTO cnt_master
  FROM all_objects
  WHERE
        UPPER (object_type) = UPPER ('SEQUENCE')
        AND UPPER (object_name) = UPPER ('USER_LNG_DAILY_LIMITS_COMP_SQ')
         AND UPPER (Owner) IN (SELECT SYS_CONTEXT ('USERENV','CURRENT_SCHEMA') FROM DUAL);

  IF cnt_master = 0
  THEN
     BEGIN
        EXECUTE IMMEDIATE
           ('CREATE SEQUENCE USER_LNG_DAILY_LIMITS_COMP_SQ START WITH 1 INCREMENT BY 1');
     END;
  END IF;
END;
/

CREATE OR REPLACE trigger USER_LNG_DAILY_LIMITS_COMP_TR
BEFORE INSERT ON USER_LNG_DAILY_LIMITS_COMP
FOR EACH ROW
BEGIN
  :new.COMPONENT_ID := USER_LNG_DAILY_LIMITS_COMP_SQ.NEXTVAL;          
END;
/

-- other commands

Not that the body of a trigger is also PL/SQL, so that should be terminated and run with a slash as well as the anonymous blocks.

I've taken out a redundant level of begin/end from each of those blocks - no functional difference really but they were just not needed. I've also changed the sequence value assignment (as @pmdba noted) - unless you' using a really old version of Oracle you don't need the context switch to select that from dual.