ODI and Dynamic SQL

I thought today we’d look at how executing dynamic SQL with ODI is both simple and effective. I ran into a scenario recently, where I had to make updates to an indeterminate number of tables. The number was indeterminate, but not the names of the tables. The table names were derived from a view, which contained all the table names that needed to be updated, and the values that needed to be changed. For argument’s sake, let’s say the source view looks like the screenshot below:

23_1_View

The view supposedly contains sensitive information regarding my associates’ finances. And if we look at those individual tables:

23_3_Will

23_2_Pete

As you can see, they are extremely wealthy individuals, who surely wouldn’t mind me updating a couple of records from those tables. So, my goal would be to update any tables that appear in the source view, that are “Chase” bank accounts and change the amount to, well, nothing.

Setting this up, is pretty simple, you will need a SQL query you would like to execute, in our case, an update statement which might look like the code below:

Of particular interest should be the “sqlstmt”  variable. We gather the results of a cursor (check out the link from the excellent Steven Feurstein). I use the explicit cursor as there will be multiple records coming through from the base query. You might also want to add exception handling based on your requirements.

All we need to do now, is set up a procedure within ODI.

23_4_ODI_1

We’ll plop in our code, into the Details section.

23_5_ODI_2

Set the Technology, Context and Schema as needed.

23_6_ODI_3

I just chose to run this command on the target (you can choose to set up commands on the source as well, if you need to). Further, you can also add ODI variables to make things more efficient.

Now, all you need to do, is run the procedure, to see the results flow through.

23_7_ODI_4

And we check the tables to make sure the procedure worked. As you can see, we’ve made a healthy deduction to their finances, particularly, to their “Chase” accounts.

23_8_Results

While the example is frivolous, and my code, atrocious, it does prove out some of the things you can do if you combine dynamic SQL and ODI.

About Vijay Kurian

Known as the Clem Fandango of EPM consulting, Vijay Kurian has been developing enterprise solutions for companies for the last 12 years (increment years if reading post-2015). Having worked with Essbase, Planning, DRM and other assorted technologies during that time, he’s made the frankly, average decision, to write about them. He hopes to contribute frequently to US Weekly, People and Sensible Chuckle magazines on improving reporting solutions, creating master data management systems and zzz…

Leave a Reply

Your email address will not be published. Required fields are marked *