Hi Tim,
You like already said, with delimiter and delimiterType you can do the stuff you want. And yes, that requires multiple execution-blocks. So it looks like you've configured that part of the pom as it should. Now the what-you-call annoying part: Maven does not support "running by executionId" by design. It's idea is to bind plugin goals to a specific phase. The solution is to use a profile, where you can put your sql-m-p. I believe it would even work if you define a defaultGoal in the build-section. By running 'mvn -PprofileId' you will explicitly run this profile. And by default the sql-m-p can be skipped. Depending on your profileId the number of chars can be even less than both 'mvn sql:execute' and 'mvn validate' ;) -Robert > Date: Sat, 19 Jun 2010 00:38:03 +0100 > From: [email protected] > To: [email protected] > Subject: [mojo-user] sql-maven-plugin: sql files need different delimiter > configurations for packages and stored proceduees > > mvn sql:execute was working just fine for all out table definitions > and our insert statements using the default delimiter ";" semicolon. > > For oracle, "create table"/"alter table"/"insert into table values" > statements all end in a semicolon. We have a couple of hundred of > these scripts to create database tables and insert data. The plugin > sends the sql to the database every time it sees a semicolon. So far > so good. Works a treat. Our scripts can also be run directly in the > oracle sqlplus tool too which is great. > > So, my project just started writing some oracle stored procedures and > functions in separate files. Now stored procedures contain internal > semicolons as well as the usually the final ones and so the sql > execution fails, if the default delimiter is used. So we need to > change the delimiter and or the delimiterType > *just*for*the*stored*procedure*files*. Fine. > > I would have thought that this was a common scenario for oracle and > db2 at least, as it keeps the scripts compatible with the databases > default tools (oracle sqlplus for example). > > But the only way I have found to make this work is really ugly and I > was hoping there is a simpler way. It uses "executions" of which I > need three. The middle one installs the stored procedures using a > delimiter of "/". The third one switches back to ; semicolon as it > uses inserts and the occasional stored procedure call. > > One other annoyance (apart from the overcomplicated pom.xml) is that I > now have to say "mvn validate" instead of "mvn sql:execute". I > couldn't work out how to get the three executions to execute as part > of the same sql:execute goal, only as part of the same phase. > > Is there a cleaner way of doing this? Should I use an ant task for > this instead (via the plugin)? > > (The obvious simple solution to me would be to use a delimiter > attribute within the srcFile tag. <srcFile > delimiter="nondeaultdelimiter">) > > Anyway, here is the relevant extract of my pom.xml. > > <plugin> > <groupId>org.codehaus.mojo</groupId> > <artifactId>sql-maven-plugin</artifactId> > <executions> > <execution> > <id>tables-etc</id> > <phase>validate</phase> > <goals> > <goal>execute</goal> > </goals> > <configuration> > <srcFiles> > <!-- Tables (must list files in order to satisfy integrity > constraints) --> > <srcFile>src/main/sql/tables/audit_log.sql</srcFile> > ... > > <!-- Views --> > <srcFile>src/main/sql/views/sup_user_authorities.sql</srcFile> > ... > > </srcFiles> > </configuration> > </execution> > <execution> > <id>packages-and-sprocs</id> > <phase>validate</phase> > <goals> > <goal>execute</goal> > </goals> > <configuration> > <delimiter>/</delimiter> > <srcFiles> > <!-- Packages and sprocs --> > <srcFile>src/main/sql/packages/tools.sql</srcFile> > <srcFile>src/main/sql/sprocs/insertauditlog.sql</srcFile> > ... > </srcFiles> > </configuration> > </execution> > <execution> > <id>seed-data</id> > <phase>validate</phase> > <goals> > <goal>execute</goal> > </goals> > <configuration> > <delimiter>;</delimiter> > <srcFiles> > <!-- insert initial data --> > <srcFile>src/main/sql/seeddata/user.sql</srcFile> > ... > </srcFiles> > </configuration> > </execution> > </executions> > </plugin> > > Cheers > Tim > > --------------------------------------------------------------------- > To unsubscribe from this list, please visit: > > http://xircles.codehaus.org/manage_email > > _________________________________________________________________ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
