Mixing DDL and DML statements in a single script

Question

I'm writing a script that will add an ID column to a table, including all the sequences and triggers to keep it automatically up to date. It will perform the following:

Add a column to a table
Create a sequence
Create a trigger
Update existing rows to use sequence values
Set the field to not nullable

The problem I have is with the line in bold - it would be an UPDATE statement in a DDL script. The error I get is:

PLS-00103: Encountered the symbol "UPDATE"

I've tried wrapping the UPDATE in a BEGIN and END block, with no success.

Is it possible to include an UPDATE statement in a DDL script?

Here's what I have so far:

ALTER TABLE RETAILER ADD (RETAILER_ID NUMBER );

CREATE SEQUENCE RETAILER_ID_SEQ;

CREATE OR REPLACE TRIGGER RETAILER_ADD_TRG
BEFORE INSERT ON RETAILER
FOR EACH ROW
BEGIN
  SELECT RETAILER_ID_SEQ.NEXTVAL INTO :new.RETAILER_ID FROM dual;
END;

-- Doesn't like this part...
UPDATE RETAILER SET RETAILER_ID = RETAILER_ID_SEQ.NEXTVAL;
COMMIT;

ALTER TABLE RETAILER MODIFY (RETAILER_ID NOT NULL);

You need a `/` after the `create trigger` statement: http://stackoverflow.com/a/10207695/330315 — , Sep 18 '15 at 06:49
Hi @a_horse_with_no_name, can you write this as an answer so I can accept it? Cheers. — Eraph, Sep 28 '15 at 05:38

David Aldridge · Answer 1 · 2015-09-18T07:00:29.613

2

An efficient way of setting that value would be:

UPDATE RETAILER SET RETAILER_ID = ROWNUM;

... and then creating the sequence, reading the number of rows in RETAILER to set the START WITH value (a bit of trivial PL/SQL and dynamic SQL).

12c supports:

CREATE SEQUENCE RETAILER_ID_SEQ;
ALTER TABLE RETAILER ADD (RETAILER_ID NUMBER DEFAULT RETAILER_ID_SEQ.NEXTVAL NOT NULL );

... by the way, so no need for defining your own trigger.

http://docs.oracle.com/database/121/SQLRF/statements_3001.htm

The DEFAULT expression can include the sequence pseudocolumns CURRVAL and NEXTVAL, as long as the sequence exists and you have the privileges necessary to access it. Users who perform subsequent inserts that use the DEFAULT expression must have the INSERT privilege on the table and the SELECT privilege on the sequence. If the sequence is later dropped, then subsequent insert statements where the DEFAULT expression is used will result in an error. If you are adding a new column to a table, then the order in which NEXTVAL is assigned to each existing row is nondeterministic

edited Sep 18 '15 at 07:00

answered Sep 18 '15 at 06:47

David Aldridge

51,479
8
68
96

Did you mean `rownum` rather than `rowid`? I don't see how a `rowid` would fit in an integer `retailer_id` column... – Justin Cave Sep 18 '15 at 06:48
Hah -- yes of course. Only been awake 5 minutes. Cheers. – David Aldridge Sep 18 '15 at 06:50
In any case, the real issue is the missing `/` as @a_horse_with_no_name mentioned. Then both solutions work, but using `RETAILER_ID_SEQ.NEXTVAL` instead of `ROWNUM` has the advantage that the sequence is already up-to-date after the `UPDATE` statement. – Andreas Fester Sep 18 '15 at 06:55
1

"Only been awake 5 minutes" . The first SO answer of the day is always the most enjoyable one ;-) – APC Sep 18 '15 at 07:14
Some good advice in here, but the problem was to do with mixing DDL and DML statements, which can be resolved by adding a slash after statements that are followed by a change in concerns. – Eraph Sep 20 '15 at 22:49

Mixing DDL and DML statements in a single script

1 Answers1

Linked