A few years ago I created what I called a InfraPatch framework, to do preparations on an infrastructure as a pre-requisite for the deployment of services and/or applications. It can run WLST scripts for creating datasouces, jms-queues, etc. It also supported the running of database scripts, but it required an sqlplus installation, for instance using the instant client. Since it was part of a release/deploy toolset, where the created release is to be deployed by an IT admin on a test, acceptance or production environment, I had to rely on a correct Oracle/instant client installation on an agreed location.
I'm in the process of revamping that framework and renamed to InfraPrep, since preparing an infrastructural environment makes it more clear what it does. (It does not patch a system with Oracle patches...).
Now I'm at the point that I have to implement the support of running database scripts. The framework uses ANT, which in fact is Java. And SQLcl has two big advantages that makes it ideal for me to use in my InfraPrep framework:
- It is incredibly small: it's only 19MB! And that includes the ojdbc and xmlparser jars. Since i used ANT from a FusionMiddleWare home, I could make it even smaller!
- It is Java, so I can leverage the java ant task.
- Download and unzip SQLcl into my Ant project and add a sqlcl.home property:
sqlcl.home=${basedir}/sqlcl - The actual sqlcl jar file and add the sqlcl.jar property for that:
sqlcl.jar=oracle.sqldeveloper.sqlcl.jar - The main class file = oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli
Then of course in my environment property file I need the user name, password and database url.
Something like:
DWN.dbUrl=(description=(address=(host=darlin-vce-db.org.darwinit.local)(protocol=tcp)(port=1521))(connect_data=(service_name=orcl))) DWN.dbUserName=dwn_owner DWN.dbPassword=dwn_owner
I used a TNS-style database URL, since it is the same as used in the creation of the corresponding DataSource. And it can be reused to connect with SQLcl.
Now, to make it easier to use and to abstract the plumbing in a sort of ANT task, I crated a macrodef:
<!-- Create Add Outbound connection pool to DB adapter-->
<macrodef name="runDbScript">
<attribute name="dbuser"/>
<attribute name="dbpassword"/>
<attribute name="dburl"/>
<attribute name="dbscript"/>
<sequential>
<logMessage message="DatabaseUrl: @{dburl}" level="info"/>
<logMessage message="DatabaseUser: @{dbuser}" level="info"/>
<logMessage message="DatabasePassword: ****" level="info"/>
<property name="dbConnectStr" value='@{dbuser}/@{dbpassword}@"@{dburl}"'/>
<property name="dbScript.absPath" location="@{dbscript}"/>
<property name="dbScriptArg" value="@${dbScript.absPath}"/>
<logMessage message="Run Database script: ${dbScriptArg}" level="info"/>
<record name="${log.file}" action="start" append="true"/>
<java classname="oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli" failonerror="true" fork="true">
<arg value="${dbConnectStr}"/>
<arg value="${dbScriptArg}"/>
<classpath>
<pathelement location="${sqlcl.home}/lib/${sqlcl.jar}"/>
</classpath>
</java>
<record name="${log.file}" action="stop"/>
</sequential>
</macrodef>
</project>
In this macrodefinition, I first build up a database connect string using the username, password and database url:
<property name="dbConnectStr" value='@{dbuser}/@{dbpassword}@"@{dburl}"'/>
Then I use a little trick to create an absolute path of the dbscript path: <property name="dbScript.absPath" location="@{dbscript}"/>
The trick is in the location attribute of the property.And since that now is a property instead of an attribute, I circumvented the need for escaping the @ character:
<property name="dbScriptArg" value="@${dbScript.absPath}"/>
The logmessage task you see is another macrodef I use: <macrodef name="logMessage">
<attribute name="message" default=""/>
<attribute name="level" default="debug"/>
<sequential>
<echo message="@{message}" level="@{level}"></echo>
<echo file="${log.file}" append="true"
message="@{message}${line.separator}" level="@{level}"></echo>
</sequential>
</macrodef>
It both echo's the output to the console and to a log file.
Since I want the output of the java task into the same log file, I enclosed the java task with record tasks to start and stop the appending of the output-stream to the log file.
The java task is pretty simple, referencing the jar file in the classpath and providing the connect string and the script run argument as two separate arguments.
There are however two important properties:
- failonerror="true": I want to quit my ANT scripting when the database script fails.
- fork="true": when providing the exit statement in the sql script, SQLcl tries to quit the JVM. This is not allowed, because it runs by default in the same JVMas ANT. Not providing the exit statement in the script will keep the thread in SQLcl, which is not acceptable. So, forking the JVM will allow SQLcl to quit properly.
<propertycopy name="dbUser" from="${database}.dbUserName"/>
<propertycopy name="dbUrl" from="${database}.dbUrl"/>
<propertycopy name="dbPassword" from="${database}.dbPassword"/>
<runDbScript dbuser="${dbUser}" dbpassword="${dbPassword}" dburl="${dbUrl}" dbscript="${prep.folder}/${dbScript}"/>
Where these properties are used:
database=DWN dbScript=sample.sql
Ant the sample.sql file:
select * from global_name; exit;
And this works like a charm:
runPrep:
[echo] Script voor uitvoeren van database script.
[echo] Environment:
[echo] Prep folder: ../../infraPreps/BpmDbS0004
[echo] Load prep property file ../../infraPreps/BpmDbS0004/BpmDbS0004.properties
[echo] Run Script
[echo] DatabaseUrl: (description=(address=(host=darlin-vce-db.org.darwinit.local)(protocol=tcp)(port=1521))(connect_data=(service_name=orcl)))
[echo] DatabaseUser: dwn_owner
[echo] DatabasePassword: ****
[echo] Run Database script: @c:\temp\FMWReleaseAll\DWN\1.0.0\infraPreps\BpmDbS0004\sample.sql
[java]
[java] SQLcl: Release 17.3.0 Production on do dec 21 11:18:50 2017
[java]
[java] Copyright (c) 1982, 2017, Oracle. All rights reserved.
[java]
[java] Connected to:
[java] Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
[java] With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
[java] Data Mining and Real Application Testing options
[java]
[java]
[java] GLOBAL_NAME
[java] --------------------------------------------------------------------------------
[java] ORCL
[java]
[java]
[java] Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
[java] With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
[java] Data Mining and Real Application Testing options
[echo] Done running preperations.
BUILD SUCCESSFUL
Total time: 12 seconds
One thing to be arranged though is the fetch of the username/password from the commandline, instead of properties. This can be as follows:
<input message="Enter database user for environment ${database}: " addproperty="db.user"/>
<input message="Enter password for user ${db.user}: " addproperty="db.password">
<handler classname="org.apache.tools.ant.input.SecureInputHandler"/>
</input>
