[groovy-user] How to execute oracle sql scripts (containing multiple sql statements) using groovy.sql.Sql?

[Peti Koch]

Hi all,

I'd like to execute a little oracle sql script (containing multiple
sql statements) using groovy.sql.Sql.

I put the sql script in a groovy multiline string

static final String createTableDefDirX2 = '''\
CREATE TABLE DEF_DIRX2
(
"DIRX_STAND" VARCHAR2(20 BYTE) NOT NULL DISABLE,
"GESCHAEFTSROLLE" VARCHAR2(100 BYTE) NOT NULL ENABLE,
"APPLIKATIONSROLLE" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"SYRIUSUMGEBUNG" VARCHAR2(30 BYTE) NOT NULL ENABLE
);

CREATE INDEX DEF_DIRX2_GESCHAEFTSROLLE ON DEF_DIRX2
( "GESCHAEFTSROLLE" );
CREATE INDEX DEF_DIRX2_DIRX_STAND ON DEF_DIRX2
( "DIRX_STAND" );
CREATE INDEX DEF_DIRX2_SYRIUSUMGEBUNG ON DEF_DIRX2
( "SYRIUSUMGEBUNG" );
CREATE INDEX DEF_DIRX2_APPLIKATIONSROLLE ON DEF_DIRX2
( "APPLIKATIONSROLLE" )
'''

When I execute the script like this

def createDefDirX(){
Sql sql = createGroovySql()
try{
sql.execute(SecurityDbDdls.createTableDefDirX2)
}
finally{
sql.close()
}
}

I get
21.09.2012 09:56:40 groovy.sql.Sql execute
WARNUNG: Failed to execute: CREATE TABLE DEF_DIRX2
...
because: ORA-00911: Ungültiges Zeichen

The ';' seems to be the problem.

Ok, workaround... split the script by hand (using ';' to split) and
execute each statement separately:

def createDefDirX(){
Sql sql = createGroovySql()
try{
SecurityDbDdls.createTableDefDirX.split(";").each { sql.execute(it) }
}
finally{
sql.close()
}
}


Question: Isn't there a more elegant solution than splitting the sql
script by hand?
I'd like to execute the script in one direct attempt.

Thanks for your answer & best regards,
Peti

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

[Dinko Srkoc]

I seem to remember having the same problem with Oracle and ';'. That
was a couple of years back, so I don't remember the details.

According to one SO answer[1] including multiple statements in one
`execute` call is not supported by Oracle JDBC driver (well, the
question was about `SELECT` statements). On the other hand, another
answer[2] suggests to enclose your statements within `BEGIN` and
`END`.

I don't have an Oracle DB at hand, so I can't try it, but would be
interested in the result of the `BEGIN`/`END` experiment.

Cheers,
Dinko

[1]: http://stackoverflow.com/a/8327417
[2]: http://stackoverflow.com/a/8082858

[Peti Koch]

Hi Dinko,

thanks for the fast reply!

I just tried wrapping the script into a BEGIN END block (-> PL/SL),
but I couldn't put more that one statement inside the EXCUTE IMMEDIATE

This works with one sql statement:

BEGIN
EXECUTE IMMEDIATE ('
CREATE TABLE DEF_DIRX5
(
DIRX_STAND VARCHAR(20) not null ,
GESCHAEFTSROLLE VARCHAR(100) not null,
APPLIKATIONSROLLE VARCHAR(20) not null,
SYRIUSUMGEBUNG VARCHAR(30) not null
)
');
END;
/

As soon as I add a ';' (optionally followed by a second statement) I
get -> ORA-00911: Ungültiges Zeichen

So, I see no way at the moment to execute a complete oracle sql script
containing multiple sql statements directly in one method call using
groovy.sql.Sql.

I'll stick to the solution with splitting the script myself, which is fine.

Best regards,
Peti

2012/9/21 Dinko Srkoc <dinko...@gmail.com>:

[Dinko Srkoc]

Hi Peti,

thanks for coming back with the findings. It looks like one cannot use
multiple statements with Oracle after all.

Word of caution regarding splitting the script on semicolons. If the
script contained stored procedures/functions, simple splitting
wouldn't work because procedures contain semicolons themselves.

Cheers,
Dinko