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.

This entry was posted on Tuesday, September 28th, 2010 at 3:56 pm and is filed under Functional, 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.