CODEWITHSUNDEEP
oracle-database
Lola Parola
Lola Parola

Reputation: 93

How to run multiple statements in one sqlscript for Oracle

I'm trying to run a few statements in one sql file but it does not work.

declare 
  new_sequence INTEGER;
begin 
  select LAST_NUMBER + 1 
    into new_sequence 
    from user_sequences 
   where SEQUENCE_NAME = 'MLTS_SEQUENCE';

  execute immediate 'Create sequence Table_SEQ start with '
      || new_sequence ||' increment by 1';
end;

If i run this block with the option 'execute as one statement' in the eclipse database plugin it works.

How can i mark the sqlscript to run each of these blocks as one statement, to execute the script later with sqlplus or something different than eclipse?

I tried GO in front and / at the end but that also didn't work.

Upvotes: 0

Views: 9288

Answers (2)

DazzaL
DazzaL

Reputation: 21973

As long as it's on its own line and left aligned, it should be ok in sql*plus:

SQL> create sequence MLTS_SEQUENCE start with 1 cache 20;

Sequence created.

SQL> select MLTS_SEQUENCE.nextval from dual;

   NEXTVAL
----------
         1

SQL> declare
  2    new_sequence INTEGER;
  3  begin
  4    select LAST_NUMBER + 1
  5      into new_sequence
  6      from user_sequences
  7     where SEQUENCE_NAME = 'MLTS_SEQUENCE';
  8
  9    execute immediate 'Create sequence Table_SEQ start with '
 10        || new_sequence ||' increment by 1';
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> select Table_SEQ.nextval from dual;

   NEXTVAL
----------
        22

Upvotes: 1

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23737

The proper format for sqlplus is the following: 

declare
  ...
begin
  ...
end;
/

Upvotes: 1

Related Questions