Oracle OWM_DYNSQL_ACCESS
Version 21c

General Information
Library Note Morgan's Library Page Header
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
AUTHID DEFINER
Data Types CREATE OR REPLACE NONEDITIONABLE TYPE wmsys.wm$exp_map_type AUTHID DEFINER
AS OBJECT(
code    INTEGER,
nfield1 NUMBER,
nfield2 NUMBER,
nfield3 NUMBER,
vfield1 VARCHAR2(128),
vfield2 VARCHAR2(128),
vfield3 CLOB);
/

CREATE OR REPLACE NONEDITIONABLE TYPE wmsys.wm$lock_info_type AUTHID DEFINER
AS OBJECT(
table_owner VARCHAR2(128),
table_name  VARCHAR2(128),
info        VARCHAR(100));
/

CREATE OR REPLACE NONEDITIONABLE TYPE wmsys.wm$table_type AS TABLE OF wmsys.wm$lock_info_type;
/
Dependencies
ALL_WM_CONSTRAINT_VIOLATIONS NO_VM_DROP_A WM$ALL_LOCKS_VIEW
ALL_WM_LOCKED_TABLES ORA_DICT_OBJ_TYPE WM$EXP_MAP_TAB
ALL_WM_VERSIONED_TABLES OWM_ASSERT_PKG WM$EXP_MAP_TYPE
DBA_WM_VERSIONED_TABLES OWM_BULK_LOAD_PKG WM$LOCK_INFO_TYPE
DBA_WORKSPACE_SESSIONS OWM_CPKG_PKG WM$LOCK_TABLE_TYPE
LT OWM_DDL_PKG WM$OPER_LOCKVALUES_ARRAY_TYPE
LTADM OWM_IEXP_PKG WM$OPER_LOCKVALUES_TYPE
LTDDL OWM_MIG_PKG WM$RIC_TABLE$
LTDTRG OWM_MP_PKG WM$VERSIONED_TABLES
LTRIC PLITBLM WM$WORKSPACE_SESSIONS_VIEW
LTUTIL UD_TRIGS WM_COMPRESS_BATCH_SIZES
LT_CTX_PKG USER_WM_LOCKED_TABLES WM_DDL_UTIL
NO_VM_DDL USER_WM_VERSIONED_TABLES WM_ERROR
Documented No
Exceptions
Error Code Reason
ORA-20122 workspace <workspace_name> does not exist
ORA-20235 invalid system parameter name or value
First Available 19c
Security Model Owned by WMSYS with no privileges granted.

Direct access to some objects in this package is prevented by means of an Accessible By clause.
Source {ORACLE_HOME}/rdbms/admin/owmdyns.plb
Subprograms
ALLOWDDLOPERATION CREATEUNIONBASEVIEW NO_VM_CREATE_PROC
ARETHERECONFLICTS CREATEVTTABLE NO_VM_DROP_PROC
ARETHEREDIFFS DROPRICLOCKINGTABLES NULL_TABLE_FUNC
CHECKWHERECLAUSE EXECSQLFROMCLOB OPERCOUNT
CLEANUPMETADATABYUSER EXISTCONFLICTS PREFIXSTR
CLEANUPSTALEMETADATA FIXOWMCOLSWHIST RECREATEINSTOFTRIGS
COMPAREINDEXPROPERTIES GETBATCHWHERECLAUSES RECREATEPTAFTERTRIGS
COMPUTERICWEIGHT GETDISPATCHERINFO RECREATEVIEWS
CREATEBASEVIEW GETNCA REFRESHVERSINWSPCLIST
CREATECONFLICTVIEW GETRICINSTEADOFTRIGSTRS REGENUDTRIGDISPATCHER
CREATECONSVIEW GETRICSESSLOCKSFORTABLE REGENUDTRIGPROCS
CREATEDIFFVIEW GETSPVERSION RELEASELOCK
CREATEHISTORYVIEW GETSTATELOCKID RELRICSESSLOCKSFORTABLE
CREATEINLISTFROMQUERY GETSYSTEMPARAMETER REMOVEDOUBLEQUOTESINUSERNAME
CREATELCKTABLE GETUCDETAILS RETURNCONSTRAINTVIOLATIONS
CREATELOCKVIEW GETUDHINT RIC_ENABLE_VERSIONING_INTERNAL
CREATEMWVIEW GETVARIABLE_B RIC_TOPO_TABLE_REFS
CREATENESTEDTABLEBASEVIEW GETVARIABLE_N SETEVCHECKPOINT
CREATENESTEDTABLECONSVIEW GET_LOCK_TABLE SETIMPORTVARS
CREATEPACKAGE HAVEIDENTICALCOLUMNS SETLTLOCKINFO
CREATERICLOCKINGTABLES IMPORT TRF_UDTRGS_ON_VER_ENABLE
CREATERICVIEWS ISMODIFIEDINSUBTREE UNSETIMPORTVARS
CREATETOPVIEW LOCKRICROWS WMFLAGENCODE
CREATETRIGGERS NO_VM_ALTER_PROC  
 
ALLOW_DDL_OPERATION
Undocumented owm_dynsql_access.allowDDLOperation(status IN VARCHAR2);
exec wmsys.owm_dynsql_access.allowDDLOperation('STOP');

PL/SQL procedure successfully completed.
 
CLEANUPMETADATABYUSER
Cleans up metadata by schema owm_dynsql_access.cleanupMetadataByUser(schema IN VARCHAR2);
exec wmsys.owm_dynsql_access.cleanupMetadataByUser('WMSYS');

PL/SQL procedure successfully completed.
 
CLEANUPSTALEMETADATA
Cleans up all stale metadata owm_dynsql_access.cleanupStaleMetadata;
exec wmsys.owm_dynsql_access.cleanupStaleMetadata;

PL/SQL procedure successfully completed.
 
CREATEPACKAGE
Undocumented

Likely protected by an ACCESSIBLE BY clause		 owm_dynsql_access.createPackage(
OBJ_OWNER_V IN VARCHAR2,
OBJ_NAME_V  IN VARCHAR2,
BUFFERCODE  IN BOOLEAN);
conn sys@pdbdev as sysdba

exec wmsys.owm_dynsql_access.createPackage('UWCLASS', 'ODACP');
BEGIN wmsys.owm_dynsql_access.createPackage('UWCLASS', 'ODACP'); END;
*
ERROR at line 1:
ORA-20289: insufficient privileges on WMSYS owned package
ORA-06512: at "WMSYS.WM_ERROR", line 449
ORA-06512: at "WMSYS.WM_ERROR", line 463
ORA-06512: at line 2
ORA-06512: at "WMSYS.OWM_ASSERT_PKG", line 215
ORA-06512: at "WMSYS.OWM_ASSERT_PKG", line 27
ORA-06512: at "WMSYS.OWM_CPKG_PKG", line 11
ORA-06512: at "WMSYS.OWM_DYNSQL_ACCESS", line 399
ORA-06512: at line 1
 
CREATERICVIEWS
Undocumented

Likely protected by an ACCESSIBLE BY clause		 owm_dynsql_access.createRICViews(
TABLE_OWNER_V IN VARCHAR2,
TABLE_NAME_V  IN VARCHAR2);
conn sys@pdbdev as sysdba

exec wmsys.owm_dynsql_access.createRICViews('UWCLASS', 'ODACP');
BEGIN wmsys.owm_dynsql_access.createRICViews('UWCLASS', 'ODACP'); END;
*
ERROR at line 1:
ORA-20289: insufficient privileges on WMSYS owned package
ORA-06512: at "WMSYS.WM_ERROR", line 449
ORA-06512: at "WMSYS.WM_ERROR", line 463
ORA-06512: at line 2
ORA-06512: at "WMSYS.OWM_ASSERT_PKG", line 215
ORA-06512: at "WMSYS.OWM_ASSERT_PKG", line 27
ORA-06512: at "WMSYS.OWM_DDL_PKG", line 2701
ORA-06512: at "WMSYS.OWM_DYNSQL_ACCESS", line 411
ORA-06512: at line 1
 
EXECSQLFROMCLOB
Undocumented

Likely protected by an ACCESSIBLE BY clause		 owm_dynsql_access.execSQLfromCLOB(
CSTR          IN CLOB,
COMPRESS_FLAG IN BOOLEAN);
DECLARE
 c CLOB := 'CREATE TABLE t (col DATE)';
BEGIN
  wmsys.owm_dynsql_access.execSQLFromClob(c, TRUE);
END;
/
DECLARE
*
ERROR at line 1:
ORA-20289: insufficient privileges on WMSYS owned package
ORA-06512: at "WMSYS.WM_ERROR", line 449
ORA-06512: at "WMSYS.WM_ERROR", line 463
ORA-06512: at line 2
ORA-06512: at "WMSYS.OWM_ASSERT_PKG", line 215
ORA-06512: at "WMSYS.LTADM", line 8242
ORA-06512: at "WMSYS.OWM_DYNSQL_ACCESS", line 479
ORA-06512: at line 5
 
GETNCA
Undocumented owm_dynsql_access.getnca(
workspace1 IN VARCHAR2,
workspace2 IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
GETSPVERSION
Undocumented owm_dynsql_access.getSPVersion(
state  IN VARCHAR2,
spname IN VARCHAR2)
RETURN NUMBER;
TBD
 
GETSTATELOCKID
Undocumented

The "state" parameter appears to be the name of a workspace		 owm_dynsql_access.getStateLockID(state IN VARCHAR2) RETURN NUMBER;
SELECT workspace
FROM dba_workspaces;

WORKSPACE
---------
LIVE

SELECT wmsys.owm_dynsql_access.getStateLockID('LIVE')
FROM dual;

WMSYS.OWM_DYNSQL_ACCESS.GETSTATELOCKID('LIVE')
----------------------------------------------
                                             0
 
GETSYSTEMPARAMETER
Returns the value of a Workspace Manager parameter

Workspace Manager parameters are defined in the LT (dbms_wm) package		 owm_dynsql_access.getSystemParameter(p_name IN VARCHAR2) RETURN VARACHAR2;
SELECT wmsys.owm_dynsql_access.getSystemParameter('ALLOW_CAPTURE_EVENTS')
FROM dual;

WMSYS.OWM_DYNSQL_ACCESS.GETSYSTEMPARAMETER('ALLOW_CAPTURE_EVENTS')
------------------------------------------------------------------
OFF
 
GETUCDETAILS
Undocumented owm_dynsql_access.getUCDetails(
index_owner_var IN VARCHAR2,
index_name_var  IN VARCHAR2,
table_owner_var IN VARCHAR2,
table_name_var  IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
GETVARIABLE_B
Undocumented owm_dynsql_access.getVariable_b(varname IN VARCHAR2) RETURN BOOLEAN;
BEGIN
  IF wmsys.owm_dynsql_access.getVariable_b('Morgan') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
F

PL/SQL procedure successfully completed.
 
GETVARIABLE_N
Undocumented owm_dynsql_access.getVariable_n(varname IN VARCHAR2) RETURN NUMBER;
DECLARE
 inVal  VARCHAR2(20) := 'MORGAN';
 retVal NUMBER;
BEGIN
  SELECT wmsys.owm_dynsql_access.getVariable_n(inVal)
  INTO retVal
  FROM dual;

  dbms_output.put_line(retVal);
END;
/

PL/SQL procedure successfully completed.
 
NULL_TABLE_FUNC
Undocumented owm_dynsql_access.null_table_func RETURN wmsys.wm$exp.map_tab;
DECLARE
 emt wmsys.wm$exp_map_tab;
BEGIN
  SELECT wmsys.owm_dynsql_access.null_table_func
  INTO emt
  FROM dual;
END;
/

PL/SQL procedure successfully completed.
 
OPERCOUNT
Undocumented but suspected to be the count of operations owm_dynsql_access.oper_count(flag IN NUMBER) RETURN NUMBER;
SELECT wmsys.owm_dynsql_access.operCount(1)
FROM dual;

WMSYS.OWM_DYNSQL_ACCESS.OPERCOUNT(1)
------------------------------------
                                  39
 
PREFIXSTR
Concatenates 2 strings with a period between them owm_dynsql_access.prefixStr(
prfx IN VARCHAR2,
str  IN VARCHAR2)
RETURN VARCHAR2;
SELECT wmsys.owm_dynsql_access.prefixStr('C##','MORGAN')
FROM dual;

PWMSYS.OWM_DYNSQL_ACCESS.PREFIXSTR('C##','MORGAN')
---------------------------------------------------
C##.MORGAN.
 
RECREATEVIEWS
Undocumented

This procedure is secured by an ACCESSIBLE BY clause		 owm_dynsql_access.recreateViews(
table_owner_var IN VARCHAR2,
table_name_var  IN VARCHAR2,
buffercode      IN BOOLEAN);
SQL> sho user
USER is "WMSYS"

exec wmsys.owm_dynsql_access.recreateViews('C##UWCLASS', 'MORGAN', TRUE);
BEGIN wmsys.owm_dynsql_access.recreateViews('C##UWCLASS', 'MORGAN', TRUE); END;
*
ERROR at line 1:
ORA-20289: insufficient privileges on WMSYS owned package
ORA-06512: at "WMSYS.WM_ERROR", line 449
ORA-06512: at "WMSYS.WM_ERROR", line 463
ORA-06512: at line 2
ORA-06512: at "WMSYS.OWM_ASSERT_PKG", line 215
ORA-06512: at "WMSYS.OWM_ASSERT_PKG", line 27
ORA-06512: at "WMSYS.OWM_DDL_PKG", line 4433
ORA-06512: at "WMSYS.OWM_DYNSQL_ACCESS", line 571
ORA-06512: at line 1
 
RELEASELOCK
Releases a lock but which specific lock on what is not documented owm_dynsql_access.releaseLock(lock_id IN NUMBER);
exec wmsys.owm_dynsql_access.releaseLock(1);

PL/SQL procedure successfully completed.
 
RETURNCONSTRAINTVIOLATIONS
Completes successfully if no constraint violations exist owm_dynsql_access.returnConstraintViolations RETURN wmsys.wm$exp_map_tab;
DECLARE
 rcv wmsys.wm$exp_map_tab;
BEGIN
  SELECT wmsys.owm_dynsql_access.returnConstraintViolations
  INTO rcv
  FROM dual;
END;
/

PL/SQL procedure successfully completed.
 
SETLTLOCKINFO
Undocumented

Locking modes are defined in the LT (dbms_wm) package		 owm_dynsql_access.setLTLockInfo(wm_ltlock IN VARCHAR2) RETURN VARCHAR2;
SELECT wmsys.owm_dynsql_access.setLTLockInfo('E')
FROM dual;

WMSYS.OWM_DYNSQL_ACCESS.SETLTLOCKINFO('E')
------------------------------------------
E*-1,-9*
 
UNSETIMPORTVARS
Unsets the value of import variables owm_dynsql_access.unsetImportVars;
exec wmsys.owm_dynsql_access.unsetImportVars;

PL/SQL procedure successfully completed.

Related Topics
Built-in Functions
Built-in Packages
Database Security
OWM_ASSERT_PKG
OWM_BULK_LOAD_PKG
OWM_CPKG_PKG
OWM_DDL_PKG
OWM_DML_PKG
OWM_IEXP_PKG
OWM_MIG_PKG
OWM_MP_PKG
OWM_VSCRIPT_PKG
OWM_VT_PKG
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx