CODEWITHSUNDEEP
oracle-database
Eraph
Eraph

Reputation: 1029

Mixing DDL and DML statements in a single script

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:

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);

Upvotes: 1

Views: 2415

Answers (1)

David Aldridge
David Aldridge

Reputation: 52346

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

Upvotes: 2

Related Questions