How do I run multiple scripts one after another in Oracle?

Question

I have the following script:

ALTER TABLE ODANBIRM 
ADD (OBID NUMBER(10, 0) );
----------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER TR_OB_INC 
BEFORE INSERT ON ODANBIRM
FOR EACH ROW 
BEGIN
  SELECT SEQ_OB.NEXTVAL INTO :NEW.OBID FROM DUAL;
END;

-----------------------------------------------------------------------------

DECLARE
CURSOR CUR IS
SELECT ROWID AS RID FROM ODANBIRM;

  RC CUR%ROWTYPE;
BEGIN
  OPEN CUR;

 LOOP
  FETCH CUR INTO RC;

    EXIT WHEN CUR%NOTFOUND;

  UPDATE ODANBIRM SET OBID=SEQ_OB.NEXTVAL WHERE ROWID=RC.RID;
   END LOOP;

 CLOSE CUR;
  COMMIT;
 END;

As you can see I have three different scripts (I've seperated them with dashed lines.) If I run this the first script runs but the second script, where I want to create a trigger fails saying "Encountered symbol "DECLARE"". If I take the trigger creation script away I get no error and the first and the last scripts run with no problem. What do I have to do to run them all without getting errors?

EDIT: I then realised that the second script should be just like this:

UPDATE ODANBIRM SET OBID=SEQ_OB.NEXTVAL;

So doing this simple thing in a loop is a waste of time and inefficient. I've once heard that one should use as much SQL and as less PL SQL as possible for efficiency. I think it's a good idea.

xQbert · Accepted Answer · 2015-12-22T22:36:25.247

8

I think it's / on the line immediately following the end of a script. It's needed on ALL end of script lines for PL blocks, including the last. so...

However, do not put it on SQL statements; as it will run it twice (as Benoit points out in comments below!)

ALTER TABLE ODANBIRM 
ADD (OBID NUMBER(10, 0) );
/

CREATE OR REPLACE TRIGGER TR_OB_INC 
BEFORE INSERT ON ODANBIRM
FOR EACH ROW 
BEGIN
  SELECT SEQ_OB.NEXTVAL INTO :NEW.OBID FROM DUAL;
END;
/

DECLARE
CURSOR CUR IS
SELECT ROWID AS RID FROM ODANBIRM;

  RC CUR%ROWTYPE;
BEGIN
  OPEN CUR;

 LOOP
  FETCH CUR INTO RC;

    EXIT WHEN CUR%NOTFOUND;

  UPDATE ODANBIRM SET OBID=SEQ_OB.NEXTVAL WHERE ROWID=RC.RID;
   END LOOP;

 CLOSE CUR;
  COMMIT;
 END;
 /

edited Dec 22 '15 at 22:36

answered May 15 '12 at 05:23

xQbert

34,733
2
41
62

You're my saviour. It's "/". Thank you. – Mikayil Abdullayev May 15 '12 at 07:31
3

-1. `/` after an ALTER statement will repeat it (or at least try). `/` should be put **only** after PL/SQL blocks (`DECLARE...`, `BEGIN...`, and anything touching a {TYPE/TYPE BODY/PACKAGE/PACKAGE BODY/FUNCTION/PROCEDURE/TRIGGER}). Other statements are SQL, and finished by semicolon. Here no harm, but if you put a slash after an SQL UPDATE statement, you'll play it twice, and that can be VERY harmful. – Benoit May 15 '12 at 07:52
Neither did I! Good to know and a worth while comment – xQbert May 15 '12 at 14:48

score 1 · Answer 2 · answered May 15 '12 at 07:44

1

Shouldn't your anonymous block just be:

UPDATE ODANBIRM
SET    OBID=SEQ_OB.NEXTVAL;

answered May 15 '12 at 07:44

David Aldridge

51,479
8
68
96

I know, I then realised and changed it to the same query as you said. AT first I was thinking that without doing this in a loop I can't achieve sequence nextvalue. Then I trued and saw that update query itself acts like a loop for a sequence. – Mikayil Abdullayev May 15 '12 at 07:59
1

Nextval is indeed a bit quirky. If you only wanted to update the existing values and didn't need the sequence for future values you could just set obid=rownum – David Aldridge May 15 '12 at 08:54
Yeah, experience is experience. I would never thought about it. Very good approach. – Mikayil Abdullayev May 15 '12 at 09:35

How do I run multiple scripts one after another in Oracle?

2 Answers2