Recently, there was a question asked on Metalink about how to update a large number of user preferences in one easy step. The original question was to see how to turn off the email preference for users, in an environment that had been cloned from production.

There are a couple of ways that you could do this, but my preferred way is through the WF_DIRECTORY APIs – I’m currently working with TCA a lot, and so the importance of using the supplied APIs is really being hammered home!

I’ve attached a script which loops through the records in the WF_USERS table, and updates the notification preference to ‘QUERY’ for all the records it finds – if you want to change different preference settings, then you just plug them into the script. Although the API is designed for use with ad-hoc roles, there is no reason why this does not work with standard roles. I tested it this morning on eBS release 11.5.10.2 on a 10g database running on Linux, and it worked fine for me.

Enjoy!

SQL script

This entry was posted on Friday, October 17th, 2008 at 3:32 pm and is filed under Oracle, Technical. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.