Oracle PL/SQL scripts with BizTalk Deployment Framework

In this article we describe the steps to prepare deployment and undeployment of Oracle PL/SQL scripts with BTDF. At the same time we will explain how certain things are achieved with BTDF. For example:

• copying additional files to the redistribution folder
• adding Custom Deploy Targets to your BTDF project file for (un)deployment
• preprocessing non-XML files

Disclaimer: This article is based on BizTalk Server 2013 R2, Deployment Framework v6.0 and Oracle Express v11.2.0.

The whole process of preparing a deployment and undeployment of PL/SQL scripts consists of the following steps:

• Store PL/SQL scripts in your solution
• Store variables for Oracle settings in the settings file
• Prepare copying the PL/SQL scripts to the file system
• Prepare a batch file for executing the PL/SQL scripts
• Prepare undeployment of the PL/SQL scripts

Store PL/SQL scripts in your solution

The first step is to store your PL/SQL scripts in a solution folder. For this it is convenient to create a solution folder called ‘PLSQL’ under your Deployment project. For sake of simplicity we’ll name the scripts following this convention:

Examples:

• CreateDBLogic.sql
• InsertData.sql
• RemoveDBLogic.sql

If you follow this naming convention, you’ll always have a maximum of 3 PL/SQL scripts per deployment project. You may also decide to create scripts for the different types of objects, like tables, stored procedures, packages and so on. While this results in more scripts, it gives you a better overview of what exactly is deployed.

Perform the following steps:

1. Open your BizTalk solution
2. Navigate to the folder which contains the Deployment project
3. Create a solution folder named ‘PLSQL’

The screenshot below shows a solution which contains a BizTalk project and a BTDF project for deployment. The BTDF project, called Deployment, contains a solution folder named ‘PLSQL’ which contains 3 PL/SQL scripts.

Store variables for Oracle settings in the settings file

Next we’ll create variables in the SettingsFileGenerator.xml file from the Deployment project. These variables contain the credentials under which the PL/SQL scripts will become (un)deployed, the (remote) server on which the PL/SQL scripts will be applied and the location of a utility called SQLPlus. SQLPlus is a command line utility which comes with Oracle and actually executes the PL/SQL scripts. All will be used later in this post.

Note: In BizTalk Deployment Framework v6.0 the file SettingsFileGenerator.xml is stored directly in the BTDF project folder, while in previous versions it was stored in the EnvironmentSettings folder.

Perform the following steps:

1. Open the SettingsFileGenerator.xml file in Excel
2. Create the following variables:
   1. SQLPlusLocation
   2. OracleDBServer
   3. OracleDbUser
   4. OracleDbPassword
3. Give each variable, per environment, the appropriate value

The variable ‘OracleDBServer’ refers to the (remote) server on which the PL/SQL scripts will be executed. The variable ‘SQLPlusLocation’ refers to the file location of the earlier mentioned command line tool SQLPlus. For writing this article a virtual machine was used on which Oracle Express v11.2.0 was installed. SQLPlus was found in:

C:oraclexeapporacleproduct11.2.0serverbin

Note: Make sure that the location you specify in the settings file ends with a backslash (‘’), otherwise an invalid command will be executed.

The screenshot below shows a settings file, which was opened in Excel and contains all variables.

Now that we have the PL/SQL scripts and some variables in place, we will continue with the preparation of copying the PL/SQL scripts to the target file system, preparing a batch file for executing the PL/SQL scripts and the preparation of the actual execution of the PL/SQL scripts.

Prepare copying the PL/SQL scripts to the file system

To be able to execute the PL/SQL scripts during (un)deployment, the scripts need to be copied to the file system of the server on which the (un)deployment is executed. Therefore we need to modify an already existing Target called ‘CustomRedist’.

Perform the following steps:

1. In the already opened Deployment project file, navigate to the Target called ‘CustomRedist’
2. Add the following XML to the Target:

Afterwards the Target will look like below:

Prepare a batch file for executing the PL/SQL scripts

By using a batch file for executing the PL/SQL scripts you achieve a couple of advantages:

• The command line is kept simple
• Credentials which are used by SQLPlus are hidden because they are put into the batch file

This step describes how the creation of a batch file will be prepared. We will create and prepare a text file for preprocessing, which, during deployment will become transformed into a batch file for executing the PL/SQL scripts. Preprocessing is necessary to be able to use the variables from the settings file. So we actually create a text file with placeholders which refer to those variables.

That text file will look like this:

exit | ${SQLPlusLocation}sqlplus -S ${OracleDBUser}/${OracleDBPassword}@${OracleDBServer} %1

Let´s explain a little bit about what´s happening here. This command starts SQLPlus from a given location (${SQLPlusLocation}), uses the given credentials (${OracleDBUser}/${OracleDBPassword}) and executes the PL/SQL script ‘%1’, which will be set later in this article on the given server (${OracleDBServer}). The parameter ‘-S’ hides the initial banner and command prompts, while the ‘@’-sign is used to execute the PL/SQL script on a remote server. As you can see, the command contains references to all 4 variables which we declared earlier in the settings file. The ‘exit |’ command makes sure that SQLPlus is closed after the PL/SQL script has been executed.

Perform the following steps:

1. Create a text file called ExecuteDbScript.txt in the PLSQL folder within your Deployment project
2. Add the text as shown above to the text file
3. Save and close the text file

Afterwards the BTDF project will look like this:

Now we have arrived at the point where we will preprocess the previously created text file. To do so, we need to add a Custom Post Deploy Target to the Deployment project file. This Target will contain the commands to preprocess the text file. After this step the PL/SQL scripts are almost ready to be deployed.

Perform the following steps:

1. Navigate to the lower end of the project file, just before the Project closing tag and create a Target called ‘CustomPostDeployTarget’. For this you’ll have to add the following XML:
   <TargetName=”CustomPostDeployTarget”>
   </Target>
2. Next add the following lines:

Afterwards the XML will look like this:

The previously created Custom Deploy Target will now become extended with the commands to execute the PL/SQL scripts.

Perform the following steps:

1. Navigate to the Custom Deploy Target called ‘CustomPostDeployTarget’.
2. Next add a line to the Custom Deploy Target for each PL/SQL file you want to deploy. It’s possible to add extra informational message lines (). These lines should look like this:

After this step the entire Custom Deploy Target might look like this:

Undeployment of the PL/SQL scripts

To make sure everything is cleaned up nicely after removal of the BizTalk application, the final step is to prepare for undeploying the PL/SQL stuff during undeployment of the BizTalk Application. Undeployment scripts contain the reverse actions of the deployment scripts like:

• revoking user permissions to execute Stored Procedures
• dropping Stored Procedures, tables and other objects

As with the deployment, the earlier defined variables are used to define on which server the T-SQL scripts will be executed.

To achieve all this we need to add a Custom Post Undeploy Target.

Perform the following steps:

1. Navigate to the lower end of the project file, just before the Project closing tag ()) and create a Target called ‘CustomPostUnDeployTarget’. Therefore you’ll have to add the following XML:
   <TargetName=”CustomPostUnDeployTarget”>
   </Target>
2. Now add a line for each PL/SQL script you want to execute during undeployment.  You can also add an extra informational message.

Afterwards the custom undeploy target may look like this:

Summary

This concludes the entire preparation of deployment and undeployment of Oracle PL/SQL scripts. Let’s end with a couple of possible improvements and recommendations:

1. To make the solution more robust, you could modify the PL/SQL creation scripts to check if the objects that are going to be created already exist. If they do, first delete the object and then recreate it. This will especially be a big time saver while you are still in the development and unit testing phase
2. The ExecuteDbScript.bat file which was created during deployment will not be deleted automatically when undeploying. By adding a delete task to the Custom Undeploy Target you can explicitly remove the file during undeployment
3. Create the PL/SQL scripts with a tool like TOAD or SQL Developer. This saves you the hassle of having to write the scripts entirely on your own