SHOW ERRORS terminates execution

Question

I have a script to create table and related structures

DROP TABLE CDR.ExtDL_JobStatus;

-- 
-- TABLE: CDR.ExtDL_JobStatus 
--

CREATE TABLE CDR.ExtDL_JobStatus(
    Id             NUMBER(38, 0)    NOT NULL,
    ShortName      NUMBER(38, 0)    NOT NULL,
    Description    NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PK_ExtDL_JobStatus PRIMARY KEY (Id)
)
;



SHOW ERRORS;
Declare NumOfSequences NUMBER :=0;
Begin
  Select COUNT(*)
  INTO NumOfSequences
  FROM All_Sequences
  WHERE 1=1
    And upper (Sequence_Owner) = upper ('CDR')
    And upper (Sequence_Name) = upper ('ExtDL_JobStatus_Seq');
  If NumOfSequences > 0 Then
    Execute IMMEDIATE 'DROP SEQUENCE CDR.ExtDL_JobStatus_Seq';
  End If;
End;
/
CREATE SEQUENCE CDR.ExtDL_JobStatus_Seq
    INCREMENT BY 1
    START WITH 1
    NOMAXVALUE 
    NOMINVALUE 
;
/

SHOW ERRORS;
Declare NumOfTriggers NUMBER :=0;
Begin
  SELECT COUNT(*)
  INTO NumOfTriggers
  FROM All_Triggers
  WHERE 1=1
    And upper (Owner) = upper ('CDR')
    And upper (Trigger_Name) = upper ('ExtDL_JobStatus_SeqTrg');
  If NumOfTriggers > 0 Then
    Execute IMMEDIATE 'DROP TRIGGER CDR.ExtDL_JobStatus_SeqTrg';
  End If;
End;
/
CREATE TRIGGER CDR.ExtDL_JobStatus_SeqTrg
BEFORE INSERT
ON CDR.ExtDL_JobStatus
    FOR EACH ROW
    WHEN (new.Id IS NULL)
    BEGIN
        SELECT ExtDL_JobStatus_Seq.nextval into :new.Id from dual;
    END;


/
SHOW ERRORS;


insert into CDR.ExtDL_JobStatus (SHORTNAME, Description) VALUES (1, 1);
insert into CDR.ExtDL_JobStatus (SHORTNAME, Description) VALUES (1, 1);
insert into CDR.ExtDL_JobStatus (SHORTNAME, Description) VALUES (1, 1);
select * FROM CDR.ExtDL_JobStatus

When I run it with SHOW ERRORS (there are multiple places where this exists), it only creates the table and is done.

DROP TABLE CDR.ExtDL_JobStatus succeeded.
CREATE TABLE succeeded.

When I remove all the show errors, here is what is returned

DROP TABLE CDR.ExtDL_JobStatus succeeded.
CREATE TABLE succeeded.
anonymous block completed
CREATE SEQUENCE succeeded.
anonymous block completed
TRIGGER CDR.ExtDL_JobStatus_SeqTrg Compiled.
1 rows inserted
1 rows inserted
1 rows inserted
ID                     SHORTNAME              DESCRIPTION            
---------------------- ---------------------- ---------------------- 
1                      1                      1                      
2                      1                      1                      
3                      1                      1                      

3 rows selected

Why is the execution terminated in the first case with SHOW ERRORS?

Answer 1

Try removing the ';' after each SHOW ERRORS.

The SQL*Plus commands like SET, SHOW ERRORS, etc don't require a ; and it is possible that the presence of the ; may be re-running the previous command in the buffer (i.e. CREATE SEQUENCE) which would cause an 'Object already exists' error. (I am feeling slightly too lazy to fire up Oracle just to confirm this).

Personally, I always specify the full command - i.e.

SHOW ERRORS TRIGGER ExtDL_JobStatus_SeqTrg