Profile Validation and Timeouts

I’m currently migrating my code archives from one hard drive to my new QNAP network attached storage device, and found a whole load of useful SQL scripts that have been sitting around waiting for me to do something with them.  Amongst them was this one….

Many Workflow processes require a timeout which is dynamic, and the way that I typically recommend my clients to do this is to define a profile option to store the value, and then reference this in the code.  Unfortunately, quite a few Oracle environments that I have seen do this but provide no validation on the profile, and no clear indication of what the value means – setting the profile to 1 might mean one second, one hour, one day…

So, here’s some quick example code of SQL validation that can be put on a profile option to validate against a list of values of timeouts in days, up to 10 days:

SQL="SELECT decode ( timeout, 0, 'No timeout', meaning ) meaning
,      timeout
into :visible_option_value, :profile_option_value
FROM   ( SELECT TO_CHAR(level - 1)||' days'   meaning
         ,      level - 1                     timeout
         FROM   dual
         CONNECT BY LEVEL <= 11 )"
HEADING=""Timeout Days"(15)"

And here’s a longer version, which provides a list of timeouts in hours from no timeout to 10 days:

SQL="SELECT DECODE ( timeout_period, 0,'No timeout',meaning)   meaning
,      timeout_period
into :visible_option_value, :profile_option_value
                  WHEN (level-1) < 24 THEN
                    CASE WHEN (level-1) = 1 THEN '1 hour'
                      TO_CHAR(level-1)||' hours'
                    CASE WHEN MOD((level-1),24) = 0 THEN
                      CASE WHEN level = 25 THEN '1 day'
                        TO_CHAR(TRUNC((level-1)/24))||' days'
                      CASE WHEN TRUNC((level-1)/24) = 1 THEN
                        CASE WHEN MOD((level-1),24) = 1
                          THEN '1 day and 1 hour'
                          '1 day and '||MOD((level-1),24)||' hours'
                        CASE WHEN MOD((level-1),24) = 1
                          THEN TO_CHAR(TRUNC((level-1)/24))
                               ||' days and 1 hour'
                          ||' days and '
                          ||MOD((level-1),24)||' hours'
                  END        meaning
         ,      (level-1) / 24   timeout_period
         FROM dual
         CONNECT BY LEVEL <= 241 )"
HEADING=""Timeout Period"(15)"

Enjoy! The SQL file for the above examples can be found here.

Deleting Accounts from Email Center

I recently had to remove some email accounts from Oracle Email Center, because the environment had been cloned from one instance to another and referenced obsolete accounts.  An easy task, one might think – if they have never worked with EMC before…  Oracle produce some guidance on how to clear the accounts out, but I’ve never had a complete list of instructions before, so thought I’d document it here.  I’m sure that there are ways to remove the accounts purely functionally, but if there is, I’ve not found it – the instructions here include a mix of functional changes and direct table updates, so as ever you use it at your own risk.

Step 1 – Ensure that there are no Agents assigned to the email account

Using the Email Center Administrator responsibility, navigate to the agents association page (path Email Center Administration > Administration > Email Accounts > Associations > Agents).  Choose the “Assign Agents to Accounts” drop-down, then choose the email account that you want to remove and click the Go button.  Remove the agents from the account(s) if you can.

If there are emails in the agent’s inbox, you will need to log in as that person, navigate to their in box and remove the emails that are sitting there.  Even if you do this, though, you may well find that you still can’t remove the agent from the account – so we’ll do a direct table update to ensure that there aren’t any emails there!

Identify the email account ID that you are looking for by running the following SQL:

SELECT email_account_id
FROM   iem_mstemail_accounts
Where  email_address =  '<enter mailbox address here>'

Then identify the agent by running:

SELECT agent_id
FROM   iem_agents
WHERE  email_account_id = <email_ account id from above SQL>

Before deleting the records, I would recommend creating a backup table at this stage, e.g.

CREATE TABLE xx_iem_agents_backup AS SELECT * FROM iem_agents;

Then force the removal of the agent from the account by running the following SQL:

DELETE FROM iem_agents
WHERE  email_account_id = <email account id>
AND    agent_id = <the associated agent id>;


So, whether you have reached this point from functional configuration or deleting the records in the Email Center tables, you should now have no agents associated with the account that you want to delete.

Step 2 – Remove the rules from the accounts

This is the one step where you should be able to do this functionally.  Navigate to the rules association page (Email Center Administration > Administration > Email Accounts > Associations > Processing Rules), and choose the account that you are deleting.  Make sure that there are no rules associated with the account – if there are, then expand the entry and remove them by clicking on the bin icon.

Step 3 – Empty the folders

Check whether Email Center is looking at the folders for that account – navigate to Email Center Administration >Monitoring > Download Processor and see whether there are any emails that the system is looking at.

If there are any messages on the server, then you should delete them if they are no longer needed – to do this, I open the account in my email client and remove the messages from the inbox, Oracle Processed and Oracle Retry folders.  Once they are empty, the Download Processor should show that there are no messages there.

Step 4 – Make the account inactive and delete it

Theoretically, this is the last step and should be straightforward.  To do this functionally, navigate to Email Center Administration > Administration > Email Accounts > Summary and find the account that you are trying to delete.  Edit the configuration and change it from Active to Inactive, then return to the summary screen.  If all is well, then the bin icon will be enabled and you can now delete the account.  If not, then we are back into the world of SQL updates to remove the account information…

Firstly, check whether there are emails being processed for the mailbox by running the following SQL:

SELECT email_account_id
,      message_id
FROM   iem_rt_proc_emails
WHERE  email_account_id = ( SELECT email_account_id
FROM   iem_mstemail_accounts
WHERE  email_address = '<enter mailbox address here>' );

If there are no rows returned, then identify the email account ID by running the subquery above:

SELECT email_account_id
FROM   iem_mstemail_accounts
WHERE  email_address = '<enter mailbox address here>';

Make a note of the account ID before moving onto the next section.

If there were any records returned, then delete them from the table (again, I recommend making a backup of the data first!):

CREATE TABLE xx_iem_rt_proc_emails_backup AS SELECT * FROM iem_rt_proc_emails;

DELETE FROM iem_rt_proc_emails
WHERE  email_account_id = <id you made a note of> ;


Next, check for any messages that are in the pre-processing stage:

SELECT email_account_id
,      message_id
FROM   iem_rt_preproc_emails
WHERE  email_account_id = <id you made a note of> ;

If no rows are returned do nothing, but if one or more rows are returned, then do the following:

CREATE TABLE xx_iem_rt_preproc_emails_backup AS SELECT * FROM iem_rt_preproc_emails;

DELETE FROM iem_rt_preproc_emails
WHERE  email_account_id = <id you made a note of> ;


That should be all that you need to do, and the accounts should now be able to be deleted.  Navigate to Email Center Administration > Administration > Email Accounts > Summary and check whether the bin icon for the email account is now enabled.  If it is, then delete the account from the front-end.  If not, then there is one final “force” that you can do from the back end:

UPDATE iem_mstemail_accounts
SET    deleted_flag = 'Y'
WHERE  deleted_flag = 'N'
AND    email_account_id = <id you made a note of> ;

The account is now removed from Email Center.

Follow up to Enabling sound on an Acer TravelMate 3000 under Linux Mint

I know it’s been ages since the last post, but I’ve been pretty manic – sorry.

Just a quick follow up to my April post about switching sound on on my old TravelMate 3000 on Linux Mint…..

The hard drive crashed last week and had to be binned, so I replaced it with an old one I had kicking about (which may not last too long, either!).  I downloaded Linux Mint 9 from the official site and installed it from the DVD.

And the sound worked fine with no need to do anything else!

Top work from the Mint guys – saved me from hacking about again to get sound working 🙂

By continuing to use the site, you agree to the use of cookies. more information

In common with almost all professionally run websites, this website logs the IP address of each visitor in order to keep it running reliably. This is also essential for protecting the website and its visitors from malicious attacks, including infection with malware.

This website provides information as a service to visitors such as yourself, and to do this reliably and efficiently, it sometimes places small amounts of information on your computer or device (e.g. mobile phone). This includes small files known as cookies. The cookies stored by this website cannot be used to identify you personally.

We use cookies to understand what pages and information visitors find useful, and to detect problems such as broken links, or pages which are taking a long time to load.

We sometimes use cookies to remember a choice you make on one page, when you have moved to another page if that information can be used to make the website work better. For example:
- avoiding the need to ask for the same information several times during a session (e.g. when filling in forms), or
- remembering that you have logged in, so that you don’t have to re-enter your username and password on every page.

You can prevent the setting of cookies by adjusting the settings on your browser (see your browser Help for how to do this). Be aware that disabling cookies will affect the functionality of this and many other websites that you visit.