SQL*PLUS Trigger has not compiled but no errors shown up

Question

I am trying to create sequence and trigger by using sqlplus and everything is written in term of bash script. My code is as following

su -p oracle -c "$ORACLE_HOME/bin/sqlplus -l mydb/mypass@localhost:1521/xe << !
    create sequence SEQ_NAME start with 1000 maxvalue 9999;

    create or replace trigger TRG_NAME
    before insert on TABLE_NAME
    for each row
    begin
        select SEQ_NAME.nextval into :new.MY_ID from dual;
    end;
!
"

After executing the above command, I expected to receive some logs look like

Sequence created

Trigger compiled

But only the log Sequence created appears. As a result, only the sequence is created and trigger is not created. I know that by checking on SQL Developer tool

The thing is when I use SQL Developer tool and execute the script

create sequence SEQ_NAME start with 1000 maxvalue 9999;

create or replace trigger TRG_NAME
before insert on TABLE_NAME
for each row
begin
select SEQ_NAME.nextval into :new.MY_ID from dual;
end;

Then everything works fine! Sequence and Trigger are created!

Any idea for this problem?

Unrelated, but: the `su` seems useless, why can't you run `sqlplus` directly? — , Jan 07 '20 at 12:54
More explanation at https://stackoverflow.com/questions/8406835/oracle-sql-plus-how-to-end-command-in-sql-file — Dave Costa, Jan 07 '20 at 13:42

UltraCommit · Accepted Answer · 2020-01-07T13:31:54.397

2

Try this, and let us know, please.

su -p oracle -c "$ORACLE_HOME/bin/sqlplus -l mydb/mypass@localhost:1521/xe << !
    create sequence SEQ_NAME start with 1000 maxvalue 9999
    /
    create or replace trigger TRG_NAME
    before insert on TABLE_NAME
    for each row
    begin
        select SEQ_NAME.nextval into :new.MY_ID from dual;
    end;
    /
!
"

edited Jan 07 '20 at 13:31

answered Jan 07 '20 at 12:52

UltraCommit

2,236
7
43
61

thanks for your answer, nearly correct! You missed `;` after `end`. Otherwise, I got `Warning: Trigger created with compilation errors.`. What is the purpose of `/`? – Uvuvwevwevwe Jan 07 '20 at 13:30
Just corrected adding semicolon after 'end' word in the trigger definition. – UltraCommit Jan 07 '20 at 13:32
2

@trdngy: https://stackoverflow.com/questions/1079949/when-do-i-need-to-use-a-semicolon-vs-a-slash-in-oracle-sql/10207695#10207695 – Jan 07 '20 at 13:32
2

In SQL*Plus, the slash symbol forces the EXECUTION of a statement (example: create statement) or a pl/sql block (example: the create block for the trigger). – UltraCommit Jan 07 '20 at 13:33
2

Please read the following very interesting explanation of the difference between semicolon and slash: https://stackoverflow.com/a/10207695/297267 – UltraCommit Jan 07 '20 at 13:36

SQL*PLUS Trigger has not compiled but no errors shown up

1 Answers1