ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
Undocumented support utility supporting SQL patching.
CURSOR all_patches_cursor IS
SELECT *
FROM (SELECT dba_registry_sqlpatch.*, rowid registry_rowid,
RANK() OVER (PARTITION BY patch_id, patch_uid
ORDER BY install_id DESC, action_time DESC) r
FROM dba_registry_sqlpatch)
WHERE r = 1
ORDER BY patch_id, patch_uid;
-- Returns the most recent entry for all interim patches
CURSOR all_interims_cursor IS
SELECT *
FROM (SELECT dba_registry_sqlpatch.*, rowid registry_rowid,
RANK() OVER (PARTITION BY patch_id, patch_uid
ORDER BY install_id DESC, action_time DESC) r
FROM dba_registry_sqlpatch
WHERE patch_type = 'INTERIM')
WHERE r = 1
ORDER BY patch_id, patch_uid;
-- Returns the most recent release update entry, which is the
-- current RU installed. This could be any flavor of release update patch,
-- i.e. an RU or RUR or CU. Note that the most recent entry may not have
-- been successful.
CURSOR current_ru_cursor IS
SELECT *
FROM (SELECT dba_registry_sqlpatch.*, rowid registry_rowid,
RANK() OVER (ORDER BY install_id DESC, action_time DESC) r
FROM dba_registry_sqlpatch
WHERE patch_type != 'INTERIM')
WHERE r = 1;
-- Returns the most recent successful release update entry. There may
-- have been subsequent unsuccessful release update install attempts.
CURSOR last_successful_ru_cursor IS
SELECT *
FROM (SELECT dba_registry_sqlpatch.*, rowid registry_rowid,
RANK() OVER (ORDER BY install_id DESC, action_time DESC) r
FROM dba_registry_sqlpatch
WHERE patch_type != 'INTERIM'
AND status = 'SUCCESS')
WHERE r = 1;
Data Types
TYPE registry_record IS RECORD (
install_id NUMBER,
patch_id NUMBER,
patch_uid NUMBER,
patch_type VARCHAR2(10),
flags VARCHAR2(10),
action VARCHAR2(15),
status VARCHAR2(25),
action_time TIMESTAMP,
description VARCHAR2(100),
source_version VARCHAR2(15),
source_build_description VARCHAR2(80),
source_build_timestamp TIMESTAMP,
target_version VARCHAR2(15),
target_build_description VARCHAR2(80),
target_build_timestamp TIMESTAMP,
registry_rowid VARCHAR2(25));
TYPE registry_table IS TABLE of registry_record;
Dependencies
DBA_REGISTRY_SQLPATCH
DBMS_SQLPATCH_FILES
DUAL
DBMS_OUTPUT
DBMS_SQLPATCH_STATE
PROPS$
DBMS_QOPATCH
DBMS_STANDARD
XMLTYPE
DBMS_REGISTRY
DBMS_SYSTEM
Documented
No
First Available
12.1
Security Model
Owned by SYS with EXECUTED granted to DATAPATCH_ROLE and EXECUTE_CATALOG_ROLE roles.
Pragma
PRAGMA SUPPLEMENTAL_LOG_DATA(default, NONE);
Source
{ORACLE_HOME}/rdbms/admin/dbmssqlpatch.sql
see also: catdwgrd.sql
For the current patch and mode, determines if the file should be run.
Returns the name of the file to be run, which will either be the supplied input file (prefixed with top_directory supplied during initialize) or dbms_registry.nothing_script if the file does not need to be run.
dbms_sqlpatch.install_file(sql_file IN VARCHAR2) RETURN VARCHAR2;
Wrapper around queryable inventory's get_pending_activity function returning an XML string representing the state of SQL patches installed in the opatch inventory.
Performs session initialization before patch_initialize can begin
dbms_sqlpatch.session_initialize(
p_force IN BOOLEAN := FALSE,
p_debug IN BOOLEAN := FALSE,
p_app_mode IN BOOLEAN := FALSE,
p_nothing_sql IN VARCHAR2 := NULL,
p_attempt IN NUMBER := NULL);
