SQL query to run on different versions of Oracle DB-CodePudding

i am trying to modify the the below query to run on all the versions of Oracle Database. The 'LAST_LOGIN' column is not available in Oracle 11g,but the rest of the columns are common in 11g,12c and 19 versions.

select username, profile, account_status,  to_char(ctime,'DD-MON-YYYY HH24:MI:SS') CTIME,
       to_char(ptime,'DD-MON-YYYY HH24:MI:SS') PTIME, DEFAULT_TABLESPACE,
       TEMPORARY_TABLESPACE, EXTERNAL_NAME, AUTHENTICATION_TYPE,
       to_char(LAST_LOGIN,'DD-MON-YYYY HH24:MI:SS') LAST_LOGIN
from dba_users a,
     sys.user$ b
where b.name ( )= a.username

Any help is greatly appreciated.Thanks.

CodePudding user response：

i am trying to modify the the below query to run on all the versions of Oracle Database.

Looking at the underlying source code for the dba_users view, the last_login value is coming from sys.user$, specifially the spare6 column, which has data type DATE. From the way it's being manipulated that is stored as UTC;; last_login converts it to a timestamp (oddly with to_char/to_date rather than cast), uses from_tz to state that's UTC, and converts to the session time zone.

So instead of

to_char(LAST_LOGIN,'DD-MON-YYYY HH24:MI:SS') LAST_LOGIN

you could use

to_char(from_tz(cast(b.spare6 as timestamp), 'UTC')
  at time zone sessiontimezone, 'DD-MON-YYYY HH24:MI:SS') LAST_LOGIN

Or if you're OK with having the value in its original UTC time, rather than your own session time zone, you could just do:

to_char(b.spare6, 'DD-MON-YYYY HH24:MI:SS') LAST_LOGIN_UTC

I don't know when spare6 was added but it was there in 11g; if you really mean "all versions" then there will presumably be some older version where that stops existing and this query won't work either; but I don't know if that's 10g, 9i, or even earlier.

SQL query to run on different versions of Oracle DB

More generally, to have different SQL for different versions you would need to use some form of dynamic SQL, possibly with conditional compilation.

CodePudding user response：

You can use conditional compilation to make a function (or procedure) with different functionality depending on the Oracle version.

For example:

CREATE FUNCTION test_conditional_compilation
RETURN SYS_REFCURSOR
IS
  cur SYS_REFCURSOR;
BEGIN
  OPEN cur FOR
    SELECT 1 AS a,
           $IF DBMS_DB_VERSION.VER_LE_11_2
           $THEN NULL
           $ELSE 2
           $END AS b
    FROM   DUAL;

  RETURN cur;
END;
/

Then:

DECLARE
  cur SYS_REFCURSOR;
  a INT;
  b INT;
BEGIN
  cur := test_conditional_compilation();
  
  LOOP
    FETCH cur INTO a, b;
    EXIT WHEN cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(a || ', ' || COALESCE(TO_CHAR(b), 'NULL'));
  END LOOP;
END;
/

In Oracle 11g, outputs:

1, NULL

db<>fiddle here

and, in Oracle 21, outputs:

1, 2

db<>fiddle here

For your code, that could be:

CREATE FUNCTION get_details
RETURN SYS_REFCURSOR
IS
  cur SYS_REFCURSOR;
BEGIN
  OPEN cur FOR
    SELECT username,
           profile,
           account_status,
           to_char(ctime,'DD-MON-YYYY HH24:MI:SS') AS CTIME,
           to_char(ptime,'DD-MON-YYYY HH24:MI:SS') AS PTIME,
           DEFAULT_TABLESPACE,
           TEMPORARY_TABLESPACE,
           EXTERNAL_NAME,
           AUTHENTICATION_TYPE,
           $IF DBMS_DB_VERSION.VER_LE_11_2
           $THEN NULL
           $ELSE to_char(LAST_LOGIN,'DD-MON-YYYY HH24:MI:SS')
           $END AS LAST_LOGIN
    FROM   dba_users a
           LEFT OUTER JOIN sys.user$ b
           ON (b.name = a.username);
END;
/

If you just want the query to run and have a NULL value for the missing column in Oracle 11. If you can source the column from somewhere else (as per Alex Poole's answer) then you can use that for Oracle 11 instead and use conditional compilation to change where the column is sourced from.

If you want to use the function in SQL (rather than PL/SQL), you can return a nested table collection type rather than a cursor.