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;
Best Answer
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
orCREATE 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