How to run multiple statements in one sqlscript for Oracle

Question

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.

GO is sybase / sqlserver syntax. "/" at the end is right (it has to be the 1st character on a line by itself). what error occurred when you ran via sqlplus with a / after the block — DazzaL
– DazzaL, Commented Mar 8, 2013 at 15:14
"but it does not work." is not enough detail. If you want us to help you you need to describe what happens, including any error messages you get. Also, please be clear about what you are running where. — APC
– APC, Commented Mar 8, 2013 at 16:49

Matt Borja · Accepted Answer · 2016-05-23 23:04:22Z

1

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

edited May 23, 2016 at 23:04

Matt Borja

1,5971 gold badge18 silver badges40 bronze badges

answered Mar 8, 2013 at 15:16

DazzaL

22.1k3 gold badges52 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lola Parola Over a year ago

Thanks you're right it does work, must have had some error in my script. Should it work also with squirrel/eclipse etc.? because there i get "PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: := . ( @ % ; not null range default character"

DazzaL Over a year ago

@LolaParola that error would suggest you've ommitted the ";" after END ie you had END then / on the next line ?

Lola Parola Over a year ago

Everything works fine now, thanks! Had to change the statement seperator from ; to / in the squirrel "session properties -> sql"

Mikhail T. Over a year ago

What about comments? Can one embed comments in a foo.sql? What's the syntax? Thanks!

Egor Skriptunoff · Accepted Answer · 2013-03-08 15:20:25Z

1

The proper format for sqlplus is the following:

declare
  ...
begin
  ...
end;
/

answered Mar 8, 2013 at 15:20

Egor Skriptunoff

23.8k2 gold badges38 silver badges68 bronze badges

Collectives™ on Stack Overflow

How to run multiple statements in one sqlscript for Oracle

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related