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 )"
COLUMN="Meaning(15)"
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
FROM   ( SELECT CASE
                  WHEN (level-1) < 24 THEN
                    CASE WHEN (level-1) = 1 THEN '1 hour'
                    ELSE
                      TO_CHAR(level-1)||' hours'
                    END
                  ELSE
                    CASE WHEN MOD((level-1),24) = 0 THEN
                      CASE WHEN level = 25 THEN '1 day'
                      ELSE
                        TO_CHAR(TRUNC((level-1)/24))||' days'
                      END
                    ELSE
                      CASE WHEN TRUNC((level-1)/24) = 1 THEN
                        CASE WHEN MOD((level-1),24) = 1
                          THEN '1 day and 1 hour'
                        ELSE
                          '1 day and '||MOD((level-1),24)||' hours'
                        END
                      ELSE
                        CASE WHEN MOD((level-1),24) = 1
                          THEN TO_CHAR(TRUNC((level-1)/24))
                               ||' days and 1 hour'
                        ELSE
                          TO_CHAR(TRUNC((level-1)/24))
                          ||' days and '
                          ||MOD((level-1),24)||' hours'
                        END
                      END
                    END
                  END        meaning
         ,      (level-1) / 24   timeout_period
         FROM dual
         CONNECT BY LEVEL <= 241 )"
COLUMN="Meaning(15)"
HEADING=""Timeout Period"(15)"

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

1 thought on “Profile Validation and Timeouts

Comments are closed.

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.

Close