/ AUTOMATION

Executing Stored Procedures with Output Parameters through vRO

I’ve been recently working with a customer on automating the execution of stored procedures within an Oracle database. This is nothing new through vRealize Orchestrator by using the OOB JDBC plugin, which makes it quite easy to accomplish. There is even a few blogs out there showing examples on the code required to execute a stored procedure that has input parameters.

However, what happens when the stored procedure you need to execute, also has output parameters that you want to retrieve post execution?

JDBC requires output parameters (parameters specified as OUT or INOUT when you create a stored procedure), to be specified before statements can be executed. The problem is, there is nothing within the vRO API Explorer for the JDBCConnection on how to do this…

Thanks to the help from Alex Day, There is a few extra methods required for a successful execution of a stored procedure, as well as retrieving the output. So let’s dig into what that looks like!

So, the first step is to register the output parameters for your stored procedure. To do this, you will need to have a PreparedStatement object created. This will allow us to use the .registerOutParameter() method, this method requires 2 parameters; an Integer for the parameterIndex & a sqlType, which can be a JDBCType.OTHER or a SQLType that is supported by the JDBC driver. Further information can be found here Callable Statements

So the SQLType is the interesting part, when it comes to vRO! The version of the JDBC driver within vRO doesn’t seem to work when specifying SQL Types, such as VARCHAR, INTEGER or CURSOR. However it does support passing the SQL Type as an Integer, also referred to as the Type code, which identifies the generic SQL type. Now the question is, what are the codes for the different SQL Types? Here is the extensive list for all types within the Java.Sql Package: SQL Types

So let’s break down what that looks like in the example below. The stored procedure has 2 parameters, we will treat parameter 1 as an input parameter and treat parameter 2 as an output parameter.

var storedProcedure = "{call myStoredProcedure(?, ?)}";
var main = new JDBCConnection();
var con = main.getConnection(dbConnectionString, dbUser, dbPassword);
var preparedStatement = con.prepareCall(storedProcedure);
preparedStatement.setString(1, myInputValue);

//public static final int VARCHAR = 12
preparedStatement.registerOutParameter(2, 12);

Notice that we specify the parameterIndex as 2 (being the second parameter in our stored procedure) and 12 as the Type Code for VARCHAR. Now I can execute the stored procedure and retrieve the value of the output parameter using the following commands:

preparedStatement.execute();
var output = preparedStatement.getString(2);
System.log(‘output: ’ + output)

Some of the code types are specific to the type of database you are executing your stored procedure on. For example, for oracle databases the code for SQL Type CURSOR is -10.

For a CURSOR Type the output parameter would return a ResultSet object, which we can then use .Next() method to move the CURSOR down one row from it’s current position and use the .getString() OR .getStringAt() method (depending on if you know the column name or not) to retrieve the values from the stored procedure, as per the example below.

//public static final int REF_CURSOR = -10;
preparedStatement.registerOutParameter(2, -10);
preparedStatement.execute();

var resultSet = preparedStatement.getCursor(2);
while (resultSet.next()) {
    System.log(resultSet.getStringAt(1));
    System.log(resultSet.getStringAt(2));
}

And there you have it, a quick and easy way of executing and retrieving data back from stored procedures with output parameters.