Skip to Main Content
  • Questions
  • Executing ddl statements with triggers in batch mode

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, jjj.

Asked: May 23, 2017 - 8:34 am UTC

Last updated: May 24, 2017 - 1:24 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

We generate in our application a ddl script, which we execute later on, wrapping it by jdbcTemplate (calling from java).
Everything was great until we added some triggers into the ddl script.

The execution fails with the error message:
"...error occurred during batching: ORA-00955: name is already used by an existing object
; nested exception is java.sql.BatchUpdateException: error occurred during batching: ORA-00955: name is already used by an existing object..."

But everything is fine if we set the batch size in our application to 1.
And some intersting observations: I can eexecute all the ddl statements by copy and paste to sql developer and 'run script' - everything works.
But if I compare the create trigger there with the one I got created via my app - the second case shows a not compiling trigger, because of missing semicolon (;) at the end:
-------------------------------------------
create or replace TRIGGER trg_abstractcontact26sn_ID
BEFORE INSERT ON abstractcontact26sn
FOR EACH ROW WHEN (new.ID IS NULL)
BEGIN
SELECT seq_abstractcontact26sn_ID.nextval INTO :new.ID FROM dual;
END //- missing semicolon, sql developer complains
-------------------------------------------

the part of source script:
-------------------------------
CREATE SEQUENCE seq_abstractcontact26sn_ID;

--comment
CREATE TABLE abstractcontact26sn
(
ID NUMBER(20,0) ,
timestamp TIMESTAMP(3),
changinguser VARCHAR2(255 CHAR),
context VARCHAR2(4000),
payload VARCHAR2(4000),
operationtype NUMBER(20,0),
PRIMARY KEY (ID)
);


-- comment
CREATE OR REPLACE TRIGGER trg_abstractcontact26sn_ID
BEFORE INSERT ON abstractcontact26sn
FOR EACH ROW WHEN (new.ID IS NULL)
BEGIN
SELECT seq_abstractcontact26sn_ID.nextval INTO :new.ID FROM dual;
END;
/

--comment
CREATE TABLE another tables......
---------------------------------

Can anyone help here? There is nothing about inserts/updates, only creation of some tables, indexes and triggers. In batch mode...
Before every try I'm making sure everything is dropped before.
Again - it works fine even being called from my app, but without batch mode.

and Connor said...

When you say

"END //- missing semicolon, sql developer complains"

well..actually, *anything* would complain, because you must have that semi-colon.

As long as you have semi-colons correct, ie, you need them for *code*, eg triggers but not for table etc, then the ddl should be fine. eg

SQL> begin
  2  execute immediate 'CREATE SEQUENCE seq_abstractcontact26sn_ID';
  3
  4  execute immediate 'CREATE TABLE abstractcontact26sn
  5  (
  6  ID NUMBER(20,0) ,
  7  timestamp TIMESTAMP(3),
  8  changinguser VARCHAR2(255 CHAR),
  9  context VARCHAR2(4000),
 10  payload VARCHAR2(4000),
 11  operationtype NUMBER(20,0),
 12  PRIMARY KEY (ID)
 13  )';
 14
 15  execute immediate
 16  'CREATE OR REPLACE TRIGGER trg_abstractcontact26sn_ID
 17  BEFORE INSERT ON abstractcontact26sn
 18  FOR EACH ROW WHEN (new.ID IS NULL)
 19  BEGIN
 20  SELECT seq_abstractcontact26sn_ID.nextval INTO :new.ID FROM dual;
 21  END;
 22  ';
 23
 24  end;
 25  /

PL/SQL procedure successfully completed.



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library