Take the 2-minute tour ×
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. It's 100% free, no registration required.

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;
share|improve this question
    
Take a look here - it might be of help. –  Vérace Jun 29 at 15:07

1 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

share|improve this answer
1  
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 at 22:10

Your Answer

 
discard

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.