Switch case expression for oracle database versions

Question

I need to query the patch status on Oracle databases. Since Oracle version 12c the view sys.REGISTRY$HISTORY was replaced by the view DBA_REGISTRY_SQLPATCH. On older versions like 11g the view dba_registry_sqlpatch does not exist. The following query creates errors on oracle versions < 12c because the view dba_registry_sqlpatch does not exist. I need to build a query that runs on all oracle database versions. I can not use PL/SQL. I think it should be solved with a case expression.

/* Query for version < 11g: */
SELECT MIN (diff) diff, MIN (zeile) zeile
  FROM (SELECT TO_CHAR (TRUNC (SYSDATE - TRUNC (action_time)), '9999') DIFF,
                  'DIFF : '
               || TO_CHAR (TRUNC (SYSDATE - TRUNC (action_time)), '9999')
               || ' DAYS '
               || 'ACTION='
               || action
               || ' VERSION='
               || version
               || ' DATE='
               || TO_CHAR (action_time, 'yyyymmdd')
               || ' ID='
               || TO_CHAR (id, '09')
               || ' COMMENTS='
               || comments
               || ' PORT='
               || (SELECT DBMS_UTILITY.port_string
                     FROM DUAL)
                  ZEILE
          FROM sys.REGISTRY$HISTORY
         WHERE action_time = (SELECT MAX (action_time)
                                FROM sys.REGISTRY$HISTORY
                               WHERE action IN ('APPLY', 'ROLLBACK'))
        UNION ALL
        /*Query for version 12c: */
        (SELECT TO_CHAR (TRUNC (SYSDATE - TRUNC (action_time)), '9999') DIFF,
                   'DIFF : '
                || TO_CHAR (TRUNC (SYSDATE - TRUNC (action_time)), '9999')
                || ' DAYS '
                || 'ACTION='
                || action
                || ' VERSION='
                || version
                || ' DATE='
                || TO_CHAR (action_time, 'yyyymmdd')
                || ' ID='
                || TO_CHAR (patch_id)
                || ' COMMENTS='
                || description
                || ' PORT='
                || (SELECT DBMS_UTILITY.port_string
                      FROM DUAL)
                   ZEILE
           FROM dba_registry_sqlpatch
          WHERE action_time = (SELECT MAX (action_time)
                                 FROM dba_registry_sqlpatch
                                WHERE action IN ('APPLY', 'ROLLBACK')))
        UNION ALL
        /* Query for no patch installed: */
        SELECT (SELECT TO_CHAR (TRUNC (SYSDATE - TRUNC (created)), '9999')
                  FROM v$database)
                  DIFF,
                  'DIFF : '
               || (SELECT TO_CHAR (TRUNC (SYSDATE - TRUNC (created)), '9999')
                     FROM v$database)
               || ' DAYS ACTION=N./A. VERSION='
               || (SELECT SUBSTR (version, 1, 8)
                     FROM v$instance)
               || ' DATE='
               || (SELECT TO_CHAR (created, 'yyyymmdd')
                     FROM v$database)
               || ' ID= 99 COMMENTS='
               || (SELECT SUBSTR (version, 1, 8)
                     FROM v$instance)
               || ' PORT='
               || (SELECT DBMS_UTILITY.port_string
                     FROM DUAL)
                  ZEILE
          FROM DUAL)
 WHERE ROWNUM = 1;

Example output on an Oracle 12c database patched 11 days ago: DIFF : 11 DAYS ACTION=APPLY VERSION=12.1.0.2 DATE=20160429 ID=22809813 COMMENTS=WINDOWS DB BUNDLE PATCH 12.1.0.2.160419(64bit):22809813 PORT=IBMPC/WIN_NT64-9.1.0

12c works - only on older versions it is necessary to have a different handling. — r0tt, May 11 '16 at 07:56

Alex Poole · Accepted Answer · 2016-05-17T16:11:58.957

A case expression won't solve your problem. The tables being queried have to be known at parse time - you can't choose the table name dynamically when the query is executed, and a case will still get an ORA-00942 before the case is evaluated.

Assuming you only want the columns that exist in both the old table and the new view, you could use a bit of XML translation to get the data from whichever one exists:

select x.*
from (
  select dbms_xmlgen.getxml(q'[select to_char(action_time, 'YYYY-MM-DD"T"HH24:MI:SS.FF9')
      as action_time, action, version, id as patch_id, comments as description
    from sys.REGISTRY$HISTORY]') as data
  from dba_tables
  where table_name = 'REGISTRY$HISTORY'
  and not exists (select null from dba_views where view_name = 'DBA_REGISTRY_SQLPATCH')
  union all
  select dbms_xmlgen.getxml(q'[select to_char(action_time, 'YYYY-MM-DD"T"HH24:MI:SS.FF9')
      as action_time, action, version, patch_id, description
    from DBA_REGISTRY_SQLPATCH]') as data
  from dba_views
  where view_name = 'DBA_REGISTRY_SQLPATCH'
) t
cross join xmltable('/ROWSET/ROW' passing xmltype(t.data)
  columns action_time timestamp path 'ACTION_TIME',
    action varchar2(30) path 'ACTION',
    version varchar2(30) path 'VERSION',
    patch_id number path 'PATCH_ID',
    comments varchar2(100) path 'DESCRIPTION'
) x;

And then replace select x.* with whatever you want to do with the data, essentially plugging that into you existing query, adding a union to get the unpatched version information:

...
union all
select vd.created as action_time, 'N/A' as action, substr(vi.version, 1, 8) as version,
  99 as patch_id, substr(vi.version, 1, 8) as description
from v$database vd
cross join v$instance vi;

The to_char() is to get the timestamp value into the ISO format that is expected in XML. The dbms_xmlgen() calls convert the data from either table/view into an XML representation; and the XMLTable() converts it back. Which seems a bit pointless, but it lets you not know the object name until runtime.

As the columns are slightly different (ID, COMMENTS vs. PATCH_ID, DESCRIPTION) this gets separate XML from either the table or the view via a union all, but not from both as that would give an invalid XML document. In 12c it looks like REGISTRY$HISTORY will be empty, but in case it isn't it won't get any data from that if DBA_REGISTRY_SQLPATCH exists. (I'm being a bit lazy and not checking ownership, so someone else creating a table with that name would be an issue, but easily fixed). It aliases the columns names so they appear the same whichever table/view it ends up using, allowing the XML to be unpacked.

Putting that together with your string formatting, eliminating the subqueries, and using the last analytic function to only keep the most recent row, you can end up with something like:

select to_char (trunc (sysdate - trunc (max(action_time))), '9999') diff,
  'DIFF : ' || to_char (trunc (sysdate - trunc (max(action_time))), '9999') || ' DAYS'
    || ' ACTION=' || max(action) keep (dense_rank last order by action_time)
    || ' VERSION=' || max(version) keep (dense_rank last order by action_time)
    || ' DATE=' || to_char (max(action_time), 'yyyymmdd')
    || ' ID=' || to_char (max(patch_id) keep (dense_rank last order by action_time), '09')
    || ' COMMENTS=' || max(comments) keep (dense_rank last order by action_time)
    || ' PORT=' || dbms_utility.port_string zeile
from (
  select x.* from (
    select dbms_xmlgen.getxml(q'[select to_char(action_time, 'YYYY-MM-DD"T"HH24:MI:SS.FF9')
        as action_time, action, version, id as patch_id, comments as description
      from sys.REGISTRY$HISTORY]') as data
    from dba_tables
    where table_name = 'REGISTRY$HISTORY'
    and not exists (select null from dba_views where view_name = 'DBA_REGISTRY_SQLPATCH')
    union all
    select dbms_xmlgen.getxml(q'[select to_char(action_time, 'YYYY-MM-DD"T"HH24:MI:SS.FF9')
        as action_time, action, version, patch_id, description
      from DBA_REGISTRY_SQLPATCH]') as data
    from dba_views
    where view_name = 'DBA_REGISTRY_SQLPATCH'
  ) t
  cross join xmltable('/ROWSET/ROW' passing xmltype(t.data)
    columns action_time timestamp path 'ACTION_TIME',
      action varchar2(30) path 'ACTION',
      version varchar2(30) path 'VERSION',
      patch_id number path 'PATCH_ID',
      comments varchar2(100) path 'DESCRIPTION'
  ) x
  union all
  select vd.created as action_time, 'N./.A' as action, substr(vi.version, 1, 8) as version,
    99 as patch_id, substr(vi.version, 1, 8) as comments
  from v$database vd
  cross join v$instance vi
);

Tested on 11.2.0.4 and 10.2.0.5, but I don't have an unpatched instance or a 12c instance to verify it behaves as you expect for those.

I think a case expression can be used (I just showed how). The table names are, in fact, known at parse time, that's not an issue - they are hard-coded in the query. Which table is used can be selected with the condition in the case expression. — , May 10 '16 at 15:36
@mathguy - the table name is known but it doesn't exist, so it will error? — Alex Poole, May 10 '16 at 15:42
Oh.... **good point!** Still my bad habits from procedural language programming... I am sure you are right, the parser will not be happy. I just had another bad thought: save the table names in a small table and select them from there - but again that won't fly, since the parser wants table names, not something that is pulled from a table at runtime. Too bad, it was a clever idea! (if I say so myself). Thanks! — , May 10 '16 at 15:48
The only other thing I'd considered was creating a dummy `dba_registry_sqlpatch` view in the 11g DB, but that's a really bad idea - a complete no-no in the SYS schema, but even elsewhere it'll cause confusion (e.g. if/when the DB is moved to 12c). And if PL/SQL isn't allowed then adding objects probably isn't either, though that isn't stipulated. — Alex Poole, May 10 '16 at 15:55
I was not able to get it working yet - I think the problem is that REGISTRY$HISTORY and DBA_REGISTRY_SQLPATCH do not have the same columns. — r0tt, May 11 '16 at 15:06
I thought they were the same... oh, description instead of comments? ([From the docs](https://docs.oracle.com/database/121/REFRN/GUID-F6D2BC12-606C-41FE-B9E8-F3702CD32F89.htm). That makes it a bit more complicated, yes. — Alex Poole, May 11 '16 at 15:17
@r0tt - I've updated with a union version that ought to work in 12c. — Alex Poole, May 11 '16 at 15:29
Thank you. I tested it on 12c with the latest patch April 2016. It works when I edit the time format and remove the FF9. I still get an error when no patch is installed. This needs to be added similar to the last union all of my question post. — r0tt, May 17 '16 at 14:45
@r0tt - not sure why the format is a problem, `action_time` is `timestamp(6)` in both versions so that shouldn't error. If you're using this as the basis to get the time diffs as in your original query then you still union that with the not-patched query. — Alex Poole, May 17 '16 at 15:40
@r0tt - added the union to get the base install info, and a wrapper around that to get the most recent data, formatted how you wanted (I think). I've left the FF9, I guess you can remove that if it's causing you a problem, and you're unlikely to have two patches within the same second. — Alex Poole, May 17 '16 at 16:13
Really nice - I will try it on different databases versions. — r0tt, May 18 '16 at 06:23
I still get errors on the case of no patch installed: ORA-06502: PL/SQL: numerischer oder Wertefehler ORA-06512: in "SYS.XMLTYPE", Zeile 272 ORA-06512: in Zeile 1 06502. 00000 - "PL/SQL: numeric or value error%s" *Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. I didn't really found a solution yet. — r0tt, Jun 12 '16 at 10:16

score 0 · Answer 2 · 2016-05-10T15:52:40.497

Edit: As Alex Poole shows in a comment (to his answer rather than to mine), what I describe below WILL NOT WORK. It is actually a good illustration of what will NOT work in this kind of situation.

I am leaving it here just so people who may have seen this already have a chance to see it's no good. I will delete the answer after a while.

Thank you Alex for pointing it out!

-

Clearly you can write your own queries, so I will only show here one way to do the "switch" expression you asked about. I only have version 11 (free edition) so I can't fully test, but this should work. To find the version of the Oracle DB your session is in, you can query the view V$VERSION. On my machine, I see the Oracle version is shown as:

SQL> select * from v$version where banner like 'Oracle%';

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

Assuming v$version didn't change in Oracle 12c (that is: there is still a view v$version, the column is still called banner, and the Oracle DB version is shown like Oracle Database 12c ....), to get just the action_time you could do something like this:

select case 
    regexp_substr((select banner from v$version where banner like 'Oracle%'), '\d{1,2}') 
           when '11' then (select action_time from sys.REGISTRY$HISTORY)
           when '12' then (select action_time from dba_registry_sqlpatch)
           end   as action_time ...

You don't need to write a case expression for the Oracle version for every bit of data from the "registry" table - you can build the full strings in the two branches of the case expression. You may tweak this to accommodate the "no patch installed" branch as well.

Good luck!

The subquery is still parsed, so in 11g the `from dba_registry_sqlpatch` throws ORA-00942? — Alex Poole, May 10 '16 at 15:41
OK... Would it work if one creates a dummy table "dba_registry_sqlpatch" under 11g, with nothing in it, just so the parser will be pleased? Although, even if that works, perhaps it's a bad practice... — , May 10 '16 at 15:53
Ha, I just referred to that on a comment on my answer... it would probably work but I don't think it's a good idea. And if you had that the OP's original query would work anyway. — Alex Poole, May 10 '16 at 15:56

Switch case expression for oracle database versions

2 Answers2