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:
The view supposedly contains sensitive information regarding my associates’ finances. And if we look at those individual tables:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DECLARE sqlstmt VARCHAR2 (200); CURSOR all_dup IS SELECT tbl_name, acct_name FROM BANK_DETAILS_V; -- Base query v_tbl_name all_dup%ROWTYPE; BEGIN OPEN all_dup; LOOP FETCH all_dup into v_tbl_name; EXIT WHEN all_dup%NOTFOUND; sqlstmt := 'update ' || v_tbl_name.tbl_name || ' set amt = null where acct_name = ''Chase'''; -- careful with those quotes EXECUTE IMMEDIATE sqlstmt; COMMIT; END LOOP; CLOSE all_dup; END; |
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.
We’ll plop in our code, into the Details section.
Set the Technology, Context and Schema as needed.
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.
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.
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.
