Thursday, June 14, 2012

Dynamic IN clause for SQL Object for Oracle (DB >=10g)

We have a table that gets updated every 15 minutes with employee demographic data and Job Related Data for our Identity Management Tool. Having this table is very useful for reporting, since it has the latest information in it at all times.  One of the elements within the table is a list of Managed departments.  The list is simply a comma separated string of departments that look like this:  2000980, 2510901, 9120980, 7280980, 1510980, 1280980, 1200980, 1150980, 7120980.  The list is generated using our Department Security Tree and if a user has access to higher node in the tree, then this list is populated with all the departments that report to the higher node.  I thought it would be a useful field to utilize when creating department related content for Pagelets within our portal.  So I played around with creating a pagelet using the Chart API in iScript and using the managed department list where I manipulated the text to place quotes around each department and passed it as a bind variable to a SQL object like this below:

DEPTID IN (:1)  (Where :1 = '2000980', '2510901', '9120980', '7280980', '1510980', '1280980', '1200980', '1150980', '7120980')

The SQL did not return any values, nor did it error.  I had a SQL trace set and it looked correct, but I assumed the bind functionality was appending quotes to my string literal.  So I attempted to remove the first and last quotes on my string of departments and passed it to the SQL object.

DEPTID IN (:1)  (Where :1 = 2000980', '2510901', '9120980', '7280980', '1510980', '1280980', '1200980', '1150980', '7120980)

Once again I was meet with futility.  I asked our DBA if she could capture the SQL in the database, so I could see what was happening, but like many places a DBA time is a precious commodity.  So I was left to my own devices.  That is when I found this blog, on how to split a comma separated string and pass to IN clause of select statement.  Armed with this information I created a new SQL object and named it SQL.ZZ_LIST, that looked like this:

select regexp_substr(:1,'[^, ]+', 1, level) from dual
    connect by regexp_substr(:1, '[^, ]+', 1, level) is not null;

Please note that I had to modify the original regular expression from the blog from  '[^,]'+ to '[^, ]+' to account for the space that is in my list of managed departments.  Then I was able to modify my SQL object with the department IN list clause and passed it the string literal without adding any quotes around the value:  Like this below:

DEPTID IN(%SQL(SQL.ZZ_LIST,:1))   (Where :1 = 2000980, 2510901, 9120980, 7280980, 1510980, 1280980, 1200980, 1150980, 7120980)

And this worked brilliantly!

If you know a way to do this in other databases please leave a comment.