PSU, patches info not filled

Wraith

Hello,

the SQL-Syntax for the PSU, patches info has an error:

SQL> prompt ##
prompt PSU, patches info

select  to_char(action_time,'yyyy-mm-dd') "DATE",
        patch_id,
        version,
        action,
        status,
        description
  from  sys.registry$sqlpatch
  order by action_time;
##
SQL> PSU, patches info
SQL> SQL>   2    3    4    5    6    7    8     version,
        *
ERROR at line 3:
ORA-00904: "VERSION": invalid identifier

SQL> describe sys.registry$sqlpatch;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 INSTALL_ID                                NOT NULL NUMBER
 PATCH_ID                                  NOT NULL NUMBER
 PATCH_UID                                 NOT NULL NUMBER
 PATCH_TYPE                                NOT NULL VARCHAR2(10)
 ACTION                                    NOT NULL VARCHAR2(15)
 STATUS                                    NOT NULL VARCHAR2(25)
 ACTION_TIME                               NOT NULL TIMESTAMP(6)
 DESCRIPTION                                        VARCHAR2(100)
 LOGFILE                                   NOT NULL VARCHAR2(500)
 RU_LOGFILE                                         VARCHAR2(500)
 FLAGS                                              VARCHAR2(10)
 PATCH_DESCRIPTOR                          NOT NULL XMLTYPE
 PATCH_DIRECTORY                                    BLOB
 SOURCE_VERSION                                     VARCHAR2(15)
 SOURCE_BUILD_DESCRIPTION                           VARCHAR2(80)
 SOURCE_BUILD_TIMESTAMP                             TIMESTAMP(6)
 TARGET_VERSION                                     VARCHAR2(15)
 TARGET_BUILD_DESCRIPTION                           VARCHAR2(80)
 TARGET_BUILD_TIMESTAMP                             TIMESTAMP(6)

Maybe SOURCE_VERSION can be used?

Kind regards,

Frank

Wraith

Update: This is obviously a change with Oracle 19c, because for 12c it works just fine.

damerva

Hello Frank,

are you able to get version with this select? (on 19c)

SELECT SOURCE_VERSION FROM sys.REGISTRY$SQLPATCH;

Because I am unable to do so on 18c. Just want to check before we try to find a replacement.

Regards,

Daniel

Wraith

Hi Daniel,

I can query that column:

SQL> SELECT SOURCE_VERSION FROM sys.REGISTRY$SQLPATCH;

SOURCE_VERSION
---------------
19.1.0.0.0

Kind regards,

Frank