Oracle | OWM_DYNSQL

Oracle OWM_DYNSQL_ACCESS Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

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_DDL	WM$ALL_LOCKS_VIEW
ALL_WM_LOCKED_TABLES	NO_VM_DROP_A	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
DICTIONARY_OBJECT_TYPE	OWM_DDL_PKG	WM$OPER_LOCKVALUES_ARRAY_TYPE
LT	OWM_IEXP_PKG	WM$OPER_LOCKVALUES_TYPE
LTADM	OWM_MIG_PKG	WM$RIC_TABLE$
LTDDL	OWM_MP_PKG	WM$VERSIONED_TABLES_USER
LTDEFINER	PLITBLM	WM$WORKSPACE_SESSIONS_VIEW
LTDTRG	UD_TRIGS	WM_COMPRESS_BATCH_SIZES
LTRIC	USER_WM_LOCKED_TABLES	WM_DDL_UTIL
LTUTIL	USER_WM_VERSIONED_TABLES	WM_ERROR
LT_CTX_PKG

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 EXECUTE granted to PUBLIC.

Access to some subpograms prevented by an Accessible By clause.

Source {ORACLE_HOME}/rdbms/admin/owmdyns.plb

Subprograms

ALLOWDDLOPERATION	CREATEVTTABLE	NO_VM_CREATE_PROC
ARETHERECONFLICTS	DROPRICLOCKINGTABLES	NO_VM_DROP_PROC
ARETHEREDIFFS	EXECSQLFROMCLOB	NULL_TABLE_FUNC
CHECKWHERECLAUSE	EXISTCONFLICTS	OPERCOUNT
CLEANUPMETADATABYUSER	FIXAUXPKCONSTRAINT	PREFIXSTR
CLEANUPSTALEMETADATA	FIXOWMCOLSWHIST	RECOMPILETABLEOBJECTS
COMPAREINDEXPROPERTIES	FIXPKCONSTRAINT	RECREATEINSTOFTRIGS
COMPUTERICWEIGHT	GETBATCHWHERECLAUSES	RECREATEPTAFTERTRIGS
CREATEBASEVIEW	GETDISPATCHERINFO	RECREATEVIEWS
CREATECONFLICTVIEW	GETNCA	REFRESHVERSINWSPCLIST
CREATECONSVIEW	GETRICINSTEADOFTRIGSTRS	REGENUDTRIGDISPATCHER
CREATEDIFFVIEW	GETRICSESSLOCKSFORTABLE	REGENUDTRIGPROCS
CREATEHISTORYVIEW	GETSPVERSION	RELEASELOCK
CREATEINLISTFROMQUERY	GETSTATELOCKID	RELRICSESSLOCKSFORTABLE
CREATELCKTABLE	GETSYSTEMPARAMETER	REMOVEDOUBLEQUOTESINUSERNAME
CREATELOCKVIEW	GETUCDETAILS	RETURNCONSTRAINTVIOLATIONS
CREATEMWVIEW	GETUDHINT	RIC_ENABLE_VERSIONING_INTERNAL
CREATENESTEDTABLEBASEVIEW	GETVARIABLE_B	RIC_TOPO_TABLE_REFS
CREATENESTEDTABLECONSVIEW	GETVARIABLE_N	SETEVCHECKPOINT
CREATEPACKAGE	GET_LOCK_TABLE	SETIMPORTVARS
CREATERICLOCKINGTABLES	HAVEIDENTICALCOLUMNS	SETLTLOCKINFO
CREATERICVIEWS	IMPORT	SETRICWEIGHT
CREATETOPVIEW	ISMODIFIEDINSUBTREE	TRF_UDTRGS_ON_VER_ENABLE
CREATETRIGGERS	LOCKRICROWS	UNSETIMPORTVARS
CREATEUNIONBASEVIEW	NO_VM_ALTER_PROC	WMFLAGENCODE

ALLOW_DDL_OPERATION

Undocumented owm_dynsql_access.allowDDLOperation(status IN VARCHAR2);

conn wmsys

      

      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);

conn wmsys

      

      exec wmsys.owm_dynsql_access.cleanupMetadataByUser('WMSYS');

      

      PL/SQL procedure successfully completed.

CLEANUPSTALEMETADATA

Cleans up all stale metadata owm_dynsql_access.cleanupStaleMetadata;

conn wmsys

      

      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 wmsys

      

      exec wmsys.owm_dynsql_access.createPackage('UWCLASS', 'ODACP');

           *

      ORA-20289: insufficient privileges on WMSYS owned package

CREATERICVIEWS

Undocumented

Likely protected by an ACCESSIBLE BY clause owm_dynsql_access.createRICViews( TABLE_OWNER_V IN VARCHAR2, TABLE_NAME_V IN VARCHAR2);

conn wmsys

      

      exec wmsys.owm_dynsql_access.createRICViews('UWCLASS', 'ODACP');

           *

      ORA-20289: insufficient privileges on WMSYS owned package

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;

      /

      *

      ORA-20289: insufficient privileges on WMSYS owned package

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');

      

      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');

      

      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);

      

      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');

      

      PWMSYS.OWM_DYNSQL_ACCESS.PREFIXSTR('C##','MORGAN')

      ---------------------------------------------------

      C##.MORGAN.

RECOMPILETABLEOBJECTS (new 23ai)

Undocumented owm_dynsql_access.recompileTableObjects( schema IN VARCHAR2, table_name IN VARCHAR2, vt_option IN NUMBER);

TBD

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);

           *

      ORA-20289: insufficient privileges on WMSYS owned package

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');

      

      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.