Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have Oracle DB 11g Enterprise Edition and I want to execute sql script from my java code. I am reading this sql script from a .sql file and the script contains statements such as create table, create type, alter table, drop type, drop procedure, insert into as well as pl/sql blocks with begin and end and all such constructs.

My question is, how to separate out individual sql statements for execution from java code? In MS SQL I could simply separate these sql statements by GO keyword, whereas in Oracle db there is no such separator. So normally what is used as separator in Oracle db scripts?

As an exmple say I have following create trigger block in my sql script along with other sql statements. Here I have semicolons which I can not consider as the SQL Statement terminating charter (PL/SQL block can itself contain multiple semicolon-separated statements). Which means I can not use ; similar to GO (in MS SQL).

   ON SQLVersionHistory
    :NEW.SequenceNumber := SQLVersionHistory_SeqNum.NEXTVAL;
share|improve this question
Take a look here - it might be of help. – Vérace Jun 29 '14 at 15:07

What creates your sql script file? Is the sql script file creation modifiable?

If you can modify the sql script file creation, the easiest way is to append your own separator characters to your script between the statements. (Comments should be a good idea)

If not, if you're just receiving a bunch of sql or pl/sql statements in a unmodifiable-source plain text file, then you should code your own parsing algorithm. You must keep in mind that EVERY sql statement end with semicolon (;), but since PL/SQL blocks can contains several sql statements they must end with a forwarded slash (/) by itself in a new line.

The confusing part is that some SQL statements are, in fact, stored PL/SQL blocks, like CREATE PROCEDURE or CREATE TRIGGER (complete list in documentation book "Oracle® Database PL/SQL Language Reference" : )


  • ALTER, SELECT, DROP, GRANT etc.. statements end with semicolon ;
  • CREATE TABLESPACE, CREATE USER, CREATE TABLE, etc statements end with semicolon ;
  • CREATE PROCEDURE, CREATE PACKAGE, CREATE TRIGGER, etc statements end with slash /
  • DECLARE, BEGIN (annonymous pl/sql blocks) statements end with slash /



share|improve this answer
Ending an sql statements with a ; or a / is only a convention used by the Oracle tool sqlplus. I assume that the OP is about sqlplus scripts but maybe it is not. – miracle173 Mar 23 '14 at 22:10

Would it be possible for you to split each statement/block ending in a ; and create a stored procedure for each?

This would allow you to directly call each stored procedure as a "function" from java, like a mini script.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.