Oracle Database Query all database profiles with sessions_per_user greater than 40

I want to query all the database profiles I have with sessions_per_user > 40. The test data I am using is as follows:

   select PROFILE, LIMIT from DBA_PROFILES where resource_name = 'SESSIONS_PER_USER;
   Profile LIMIT
   DEFAULT  UNLIMITED
   ORA_STIG_PROFILE DEFAULT
   APP_USER 40
   TEST2    2
   TEST3    3
   TEST4    11
   TEST5    27
   TEST6    33
   TEST7    244
   TEST8    45
   TEST9    50
   TEST10   111
   TEST11   93
   TEST12   39
   TEST13   41

I started with this query

     select profile, limit from DBA_PROFILES where resource_name = 'SESSIONS_PER_USER' AND LIMIT >= 40;

Which results in ORA-01722: invalid number because you can't perform math against a string.

So I tried this:

     select profile, LIMIT from (select PROFILE, LIMIT from DBA_PROFILES where resource_name = 'SESSIONS_PER_USER' AND LIMIT != 'UNLIMITED' AND PROFILE != 'DEFAULT' AND LIMIT != 'DEFAULT') WHERE LIMIT = 40;

I'm still getting invalid number even though the second query has the default and unlimited ones filtered.

For most things this problem wouldn't exist because the column would be restricted to either exclusively a number or string but with this database setting I obviously can't change any of that and on the production system the values default and unlimited are used in practice.

Solution

The problem is that the column contains numbers and strings, as you have noticed.

So, if only you could get it to ignore the non-numbers for strings, you can solve the problem. As you've seen, where might not do the trick. SO, use case:

select profile, limit
from DBA_PROFILES
where resource_name = 'SESSIONS_PER_USER' AND
      (case when not regexp_like(limit, '[^0-9]') then cast(LIMIT as number)
       end) >= 40;

case guarantees the order of execution of its clauses, so the cast() only occurs on bona fide integers.