How to separate out individual sql statements from Oracle SQL script for execution from java code

Question

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

CREATE OR REPLACE TRIGGER SQLVersionHistory_SeqNum_TRG
   BEFORE INSERT 
   ON SQLVersionHistory
   FOR EACH ROW
   BEGIN
    :NEW.SequenceNumber := SQLVersionHistory_SeqNum.NEXTVAL;
   END;

Answer 1

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" : http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/sqlstatements.htm#LNPLS99979 )

(e.g.

• 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 /

)

Regards

Answer 2

According to https://stackoverflow.com/questions/29961595/sql-server-go-equivalent-in-oracle, ";" and "/" are the Oracle equivalent of SQL Server's GO. For your particular example, since this is a block which can contain a ";", you would need to close it with a "/". Note that for a statement, adding a trailing "/" will repeat the statement, so the following:

commit;
/

...does two commits. For how to know which is which, see vegatripy's answer(+1).

Answer 3

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.