0

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.

Improve this question

3 Answers 3

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Hi Alex, i have found this similar request.stackoverflow.com/questions/64154332/… Trying to modify based on the answer.
Yeah, the XMLTable approach might work for you; that's one of the forms of dynamic SQL *8-) For this specific query it seems like overkill, unless you do need to support versions that don't have spare6.
i am unable to model the query with the XMLTable approach,as in this case i have to use 2 views to query.
2

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.

Improve this answer

Comments

1

i am unable to model the query with the XMLTable approach,as in this case i have to use 2 views to query

You can't use ora:view, but can use the same principle with XMLTable and extracting the columns; the trick is to generate the XML view of the data with dbms_xmlgen.getxmltype(), passing that your original query as a string, but manipulating that query string to only include the last_login column if the version is > 11 - which you can get by cross-joining to v$instance.

This is a bit rough and messy but I think does what you want:

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 v$instance i
cross join xmltable('/ROWSET/ROW'
  passing dbms_xmlgen.getxmltype(
    q'^
select a.username, a.profile, a.account_status, a.default_tablespace,
  a.temporary_tablespace, a.external_name, a.authentication_type,
^' || case when to_number(substr(i.version, 1, instr(i.version, '.') - 1)) > 11
        then q'^ to_char(a.last_login,'YYYY-MM-DD"T"HH24:MI:SS') last_login,^'
      end || q'^
  to_char(b.ctime, 'YYYY-MM-DD"T"HH24:MI:SS') ctime,
  to_char(b.ptime,'YYYY-MM-DD"T"HH24:MI:SS') ptime
from dba_users a
left join sys.user$ b on b.name = a.username
^')
columns 
  username, profile, account_status, default_tablespace, temporary_tablespace,
  external_name, authentication_type, ctime timestamp, ptime timestamp, last_login timestamp
) x;

It works in 19c anyway; I don't have an 11g database to try it against, but it should just return null in the last_login column.

The inner bit is probably most confusing, where it builds the dynamic query string:

    q'^
select a.username, a.profile, a.account_status, a.default_tablespace,
  a.temporary_tablespace, a.external_name, a.authentication_type,
^' || case when to_number(substr(i.version, 1, instr(i.version, '.') - 1)) > 11
        then q'^  to_char(a.last_login,'YYYY-MM-DD"T"HH24:MI:SS') last_login,^'
      end || q'^
  to_char(b.ctime, 'YYYY-MM-DD"T"HH24:MI:SS') ctime,
  to_char(b.ptime,'YYYY-MM-DD"T"HH24:MI:SS') ptime
from dba_users a
left join sys.user$ b on b.name = a.username
^'

That's mostly your original, reordered a bit and with modern join syntax and columns prefixed with the alias of the table they come from. Because of the case expression, in 11g that will evaluate to:

select a.username, a.profile, a.account_status, a.default_tablespace,
  a.temporary_tablespace, a.external_name, a.authentication_type,
  to_char(b.ctime, 'YYYY-MM-DD"T"HH24:MI:SS') ctime,
  to_char(b.ptime,'YYYY-MM-DD"T"HH24:MI:SS') ptime
from dba_users a
left join sys.user$ b on b.name = a.username

while in 12c and 19c etc. it will evaluate to:

select a.username, a.profile, a.account_status, a.default_tablespace,
  a.temporary_tablespace, a.external_name, a.authentication_type,
  to_char(a.last_login,'YYYY-MM-DD"T"HH24:MI:SS') last_login,
  to_char(b.ctime, 'YYYY-MM-DD"T"HH24:MI:SS') ctime,
  to_char(b.ptime,'YYYY-MM-DD"T"HH24:MI:SS') ptime
from dba_users a
left join sys.user$ b on b.name = a.username

i.e. the same query but with the last_login reference added.

The three date columns are being formatted as strings in ISO-8601 format. That is so that when they are extracted with the XMLTable columns clause they can be declared as timestamps, and will be converted to that data type automatically.

The main query select list can then format those timestamps however you want.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.