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
Utilities supporting the DBMS_METADATA package
AUTHID
DEFINER
Constants
Name
Data Type
Value
marker
NUMBER
42
Data Types
CREATE TYPE sys.ku$_audobj_t AS OBJECT (
name VARCHAR2(31), -- operation to be audited, e.g., ALTER
value CHAR(1), -- 'S' = by session, 'A' = by access, '-' = no auditing
type CHAR(1)) -- 'S' = when successful, 'F' = when not successful
CREATE TYPE sys.ku$_audit_list_t IS TABLE OF sys.ku$_audobj_t
/
CREATE TYPE sys.ku$_auddef_t AS OBJECT
(
name VARCHAR2(31), -- operation to be audited, e.g., ALTER
value CHAR(1), -- 'S' = by session, 'A' = by access, '-' = no auditing
type CHAR(1)); -- 'S' = when successful, 'F' = when not successful
/
CREATE TYPE sys.ku$_audit_default_list_t IS TABLE OF sys.ku$_auddef_t;
/
CREATE TYPE sys.ku$_source_t AS OBJECT (
obj_num NUMBER, -- object number
line NUMBER, -- line number
pre_name NUMBER,
post_name_off NUMBER,
post_keyw NUMBER, -- the offset of post keyword
pre_name_len NUMBER, -- length between keyword and name
source VARCHAR2(4000)) -- source line
/
CREATE TYPE ku$_source_list_t AS TABLE OF sys.ku$_source_t;
/
Dependencies
SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_METADATA_UTIL'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_METADATA_UTIL';
-- 269 objects returned
Documented
No
Exceptions
Error Code
Reason
31600
invalid_argval: Invalid Argument
31601
invalid_operation: Invalid Operation
31602
inconsistent_args: Inconsistent Arguments
31603
object_not_found: Object Not Found
31604
invalid_objeject_param: Invalid Object Parameter
31607
inconsistent_operation: Inconsistent Operation
31608
object_not_found2: Object Not Found
31609
stylesheet_load_error: installation script initmeta.sql failed to load the named file
39127
procobj_error: DataPump Internal Error
39132
bad_hashcode: Object exists with different hash code on the target system
39133
type_in_use: Object type already exists with different typeid
First Available
9.0
Security Model
Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role
Source
{ORACLE_HOME}/rdbms/admin/dbmsmetu.sql
Subprograms
ARE_STYLESHEETS_LOADED
Are the XSL stylesheets loaded?
dbms_metadata_util.are_stylesheets_loaded RETURN BOOLEAN;
set serveroutput on
BEGIN
IF dbms_metadata_util.are_stylesheets_loaded THEN
dbms_output.put_line('TRUE');
ELSE
dbms_output.put_line('FALSE');
END IF;
END;
/
BLOB2CLOB
Converts a column default replace NULL BLOB into a CLOB
dbms_metadata_util.blob2clob(
tabobj IN NUMBER,
incolnum IN NUMBER)
RETURN CLOB;
conn pm/pm@pdbdev
SELECT object_id
FROM user_objects
WHERE object_name = 'PRINT_MEDIA';
SELECT column_id, data_type
FROM user_tab_cols
WHERE table_name = 'PRINT_MEDIA';
conn sys@pdbdev as sysdba
SELECT dbms_metadata_util .blob2clob(76328, 3)
FROM dual;
DECLARE
vClob CLOB;
BEGIN
vClob := dbms_metadata_util.blob2clob (76328, 3);
dbms_output.put_line(vClob);
END;
/
BLOCK_ESTIMATE
Calculates bytes allocated using the BLOCKS method
dbms_metadata_util.block_estimate(
o_num IN NUMBER,
view_num IN NUMBER)
RETURN NUMBER;
View#
View Name
1
ku$_htable_bytes_alloc_view
2
ku$_htpart_bytes_alloc_view
3
ku$_htspart_bytes_alloc_view
4
ku$_iotable_bytes_alloc_view
5
ku$_iotpart_bytes_alloc_view
6
ku$_ntable_bytes_alloc_view
7
ku$_eqntable_bytes_alloc_view
SELECT object_id
FROM dba_objects
WHERE object_name = 'SOURCE$';
SELECT dbms_metadata_util.block_estimate (284, 1)
FROM dual;
BYTES_ALLOC
Calculate bytes allocated from x$ktfbue using the temporary table if it has been initialized
dbms_metadata_util.bytes_alloc(
ts_num IN NUMBER,
file_num IN NUMBER,
block_num IN NUMBER,
block_size IN NUMBER)
RETURN NUMBER;
SELECT ts#, name, blocksize
FROM ts$;
SELECT file_id
FROM dba_data_files
WHERE tablespace_name = 'SYSTEM';
SELECT block_id
FROM dba_extents
WHERE segment_name = 'SOURCE$'
ORDER BY 1;
-- choose one of the first block_id values from the query of dba_extents
SELECT dbms_metadata_util.bytes_alloc (0, 1, 2216, 8)
FROM dual;
CHECK_CONSTRAINT
According to Oracle checks to see if a constraint exists ... but that's total nonsense. See our comments for the function of the same name in DBMS_METADATA
dbms_metadata_util.check_constraint(OBJ_NUM IN NUMBER)
RETURN NUMBER;
SELECT dbms_metadata_util.check_constraint (780)
FROM dual;
CHECK_TYPE
For transportable import, check a type's definition and typeid
dbms_metadata_util.check_type(
schema IN VARCHAR2,
type_name IN VARCHAR2,
version IN VARCHAR2,
hashcode IN VARCHAR2,
typeid IN VARCHAR2);
TBD
CONVERT_TO_CANONICAL
Convert version string to canonical form
dbms_metadata_util.convert_to_canonical(version IN VARCHAR2)
RETURN VARCHAR2;
SELECT dbms_metadata_util.convert_to_canonical ('21.1.0.1')
FROM dual;
DBMS_METADATA_UTIL.CONVERT_TO_CANONICAL('21.1.0.1')
----------------------------------------------------
21.01.00.01.00
DELETE_XMLSCHEMA
Deletes the named schema
dbms_metadata_util.delete_xmlschema(name IN VARCHAR2);
TBD
FETCH_STAT
In theory returns the XML from the metadata api for the SYS.IMPDP_STATS table
dbms_metadata_util.fetch_stat(stat_clob IN OUT CLOB);
conn sys@pdbdev as sysdba
desc impdp_stats
SELECT COUNT(*) FROM impdp_stats;
-- don't expect a lot if the count is zero
DECLARE
cVal CLOB := 'dflt';
BEGIN
dbms_metadata_util.fetch_stat (cVal);
dbms_output.put_line(cVal);
END;
/
FUNC_INDEX_DEFAULT
Get default$ from col$ for a func index converting any null bytes to 'CHR(0)'
dbms_metadata_util.func_index_default(
length IN NUMBER,
row IN ROWID)
RETURN VARCHAR2;
TBD
FUNC_INDEX_DEFAULTC
Get default$ from col$ for a func index converting any null bytes to 'CHR(0)'
dbms_metadata_util.func_index_defaultc(
length IN NUMBER,
row IN ROWID)
RETURN CLOB;
TBD
FUNC_VIEW_DEFAULT
Returns TEXT from view$ for a func view removing null bytes from view$.text
dbms_metadata_util.func_view_default(
length IN NUMBER,
row IN ROWID)
RETURN VARCHAR2;
TBD
FUNC_VIEW_DEFAULTC
Returns TEXT from view$ for a func view removing null bytes to from view$.text
dbms_metadata_util.func_view_defaultc(
length IN NUMBER,
row IN ROWID)
RETURN CLOB;
TBD
GET_ANC
Get the object number of the base table to which a nested table belongs
dbms_metadata_util.get_anc(
nt IN NUMBER,
exclude_xml IN NUMBER := 1)
RETURN NUMBER;
TBD
GET_ANYDATA_COLSET
Return nested table of type names for unpacked ADT columns contained in an opaque column. Each element in the list contains one or more type names
dbms_metadata_util.get_anydata_colset(
objnum IN NUMBER,
colnum IN NUMBER,
colcnt IN NUMBER,
col_list IN RAW)
RETURN ku$_Unpacked_AnyData_t;
TBD
GET_ATTRNAME
Return attribute name for a table-column
dbms_metadata_util.get_attrname(
obj IN NUMBER,
intcol IN NUMBER)
RETURN VARCHAR2;
conn sys@pdbdev as sysdba
SELECT object_id
FROM dba_objects_ae
WHERE owner = 'UWCLASS'
AND object_name = 'SERVERS';
SELECT dbms_metadata_util.get_attrname (76292, 1)
FROM dual;
GET_ATTRNAME2
For ANYDATA column sets removes the system generated part of the name and replaces it with the type name and its owner
dbms_metadata_util.get_attrname2(
objnum IN NUMBER,
intcolnum IN NUMBER,
colnum IN NUMBER)
RETURN VARCHAR2;
TBD
GET_AUDIT
Return audit information for a schema object
dbms_metadata_util.get_audit(
obj_num IN NUMBER,
type_num IN NUMBER )
RETURN sys.ku$_audit_list_t;
TBD
GET_AUDIT_DEFAULT
Return default object audit information setting
dbms_metadata_util.get_audit(obj_num IN NUMBER)
RETURN sys.ku$_audit_default_list_t;
TBD
GET_BASE_COL_NAME
Returns the name of a base XMLType column
dbms_metadata_util.get_base_col_name(
objnum IN NUMBER,
colnum IN NUMBER,
intcol IN NUMBER,
typenum IN NUMBER)
RETURN VARCHAR2;
TBD
GET_BASE_COL_TYPE
Return 1 if base column is udt, 2 if base column is XMLType stored OR or CSX 3 if base column is XMLType stored as CLOB 0 if (a) intcol = base column or (b) base column not udt or XMLType
dbms_metadata_util.get_base_col_type(
objnum IN NUMBER,
colnum IN NUMBER,
intcol IN NUMBER,
typenum IN NUMBER)
RETURN NUMBER;
TBD
GET_BASE_INTCOL_NUM
Return intcol# of base column, i.e., the intcol# of the first column with this col#
dbms_metadata_util.get_base_intcol_num(
objnum IN NUMBER,
colnum IN NUMBER,
intcol IN NUMBER,
typenum IN NUMBER)
RETURN NUMBER;
TBD
GET_CANONICAL_VSN
Convert user's VERSION param to canonical form
dbms_metadata_util.get_canonical_vsn(version IN VARCHAR2) RETURN VARCHAR2;
SELECT dbms_metadata_util.get_canonical_vsn ('21.1.0.1.0')
FROM dual;
DBMS_METADATA_UTIL.GET_CANONICAL_VSN('21.1.0.1.0')
---------------------------------------------------
21.01.00.01.00
GET_COL_PROPERTY
Return col$.property (but clear encryption bits if force_no_encrypt flag is set
dbms_metadata_util.get_col_property(
objnum IN NUMBER,
intcol_num IN NUMBER)
RETURN NUMBER;
TBD
GET_COMPAT_VSN
Return the compatibility version number as a number
dbms_metadata_util.get_compat_vsn RETURN NUMBER;
SELECT dbms_metadata_util.get_compat_vsn
FROM dual;
GET_DB_VSN
Return the database version number as a string
dbms_metadata_util.get_db_vsn RETURN NUMBER;
SELECT dbms_metadata_util.get_db_vsn
FROM dual;
GET_EDITIONID
Return ID for specified edition
dbms_metadata_util.get_editionid(edition IN VARCHAR2) RETURN NUMBER;
SELECT * FROM dba_editions;
SELECT sys_context ('USERENV', 'CURRENT_EDITION_ID')
FROM dual;
SELECT dbms_metadata_util.get_editionid ('ORA$BASE')
FROM dual;
GET_ENDIANNESS
Returns platform endianness
big = 1, little = 2
dbms_metadata_util.get_endianness RETURN NUMBER;
SELECT dbms_metadata_util.get_endianness
FROM dual;
GET_FULLATTRNAME
Return fully qualified attrname, when attrname is a system generated name
dbms_metadata_util.get_fullattrname(
obj IN NUMBER,
col IN NUMBER,
intcol IN NUMBER,
type IN NUMBER)
RETURN VARCHAR2;
TBD
GET_INDEX_INTCOL
Get intcol# in table of column on which an index is defined that needs special handling for xmltype cols
dbms_metadata_util.get_index_intcol(
obj_num IN NUMBER, -- base table object number
intcol_num IN NUMBER) -- intcol# from icol$
RETURN NUMBER;
TBD
GET_INDEX_INTCOL_PARSE
Parse default_val_clob and get intcol# in table of column on which index is defined
dbms_metadata_util.get_index_intcol_parse(
obj_num IN NUMBER, -- base table object number
intcol_num IN NUMBER, -- intcol# from icol$
default_val_clob IN CLOB) -- default_val from col$
RETURN NUMBER;
SELECT do.object_id, di.index_name
FROM dba_objects do, dba_indexes di
WHERE do.object_name = di.index_name
AND di.table_name = 'OBJ$';
SELECT dbms_metadata_util.get_index_intcol_parse (36, 7, '')
FROM dual;
GET_INDPART_TS
Get a ts# for an index (sub)partition used by ku$_index_view
dbms_metadata_util.get_indpart_ts(obj_num IN NUMBER) RETURN NUMBER;
See the GET_INDPART_TS demo under DBMS_METADATA linked below
GET_LATEST_VSN
Return a number for the latest version number
dbms_metadata_util.get_latest_vsn RETURN NUMBER;
SELECT dbms_metadata_util.get_latest_vsn
FROM dual;
GET_LOB_PROPERTY
Return lob$.property (but clear bit 0x0200 if force_lob_be is set; 0x0200 = LOB data in little endian format)
dbms_metadata_util.get_lob_property(
objnum IN NUMBER,
intcol_num IN NUMBER)
RETURN NUMBER;
TBD
GET_LOST_WRITE_PROTECTION
Returns TRUE when LOST WRITE PROTECTION is enabled, else FALSE
dbms_metadata_util.get_lost_write_protection RETURN BOOLEAN;
show parameter lost
NAME TYPE VALUE
---------------------- ------- ------
db_lost_write_protect string NONE
BEGIN
IF dbms_metadata_util.get_lost_write_protection THEN
dbms_output.put_line('Lost Write Protection Enabled');
ELSE
dbms_output.put_line('Lost Write Protection Disabled');
END IF;
END;
/
GET_MARKER
Returns the current marker number
dbms_metadata_util.get_marker RETURN NUMBER;
SELECT dbms_metadata_util.get_marker
FROM dual;
GET_MARKER
----------
42
GET_OPEN_MODE
Return database open mode (read only, read write). Returns:
0 = MOUNTED
1 = READ WRITE
2 = READ ONLY
dbms_metadata_util.get_open_mode RETURN NUMBER;
SELECT dbms_metadata_util.get_open_mode
FROM dual;
GET_OPEN_MODE
-------------
1
GET_PART_LIST
Get ordered list of partition numbers or partition base object numbers
dbms_metadata_util.get_part_list(
PARTYPE IN NUMBER,
BOBJ_NUM IN NUMBER,
CNT OUT NUMBER)
RETURN dbms_metadata_int.t_num_coll;
Par Types
Explanation
1
tabpart
2
tabcompart
3
tabsubpart
4
indpart
5
indcompart
6
indsubpart
7
lobfrag
8
lobcomppart
conn / as sysdba
SELECT object_id, object_type
FROM dba_objects
WHERE object_name = 'WRH$_SQLSTAT'
ORDER BY 1;
OBJECT_ID OBJECT_TYPE
---------- -------------------------
11130 TABLE
11131 TABLE PARTITION
132212 TABLE PARTITION
132507 TABLE PARTITION
132823 TABLE PARTITION
133109 TABLE PARTITION
133372 TABLE PARTITION
133704 TABLE PARTITION
133974 TABLE PARTITION
134283 TABLE PARTITION
134557 TABLE PARTITION
134910 TABLE PARTITION
DECLARE
lRetVal dbms_metadata_int.t_num_coll;
lCnt PLS_INTEGER;
BEGIN
lRetVal := dbms_metadata_util.get_part_list (1,
11130 , lCnt);
FOR i IN 1..lCnt LOOP
dbms_output.put_line(lRetVal(i));
END LOOP;
END;
/
505
506
516
525
526
527
528
529
530
532
536
SELECT part#
FROM tabpart$
WHERE bo# = 11130 ;
PART#
------
505
506
516
525
526
527
528
529
530
532
536
GET_PROCOBJ_ERRORS
Get any errors raised by procedural object code
dbms_metadata_util.get_procobj_errors(err_list OUT sys.ku$_vcnt);
TBD
GET_QA_LRG_TYPE
Return the value of qa_lrg_type, which can be used to determine that the database is being used for testing
dbms_metadata_util.get_qa_lrg_type RETURN NUMBER
SELECT dbms_metadata_util.get_qa_lrg_type
FROM dual;
GET_QA_LRG_TYPE
---------------
0
GET_REFRESH_ADD_DBA
Return refresh group dbms_irefresh.add execute string
dbms_metadata_util.get_get_refresh_add_dba(
owner IN VARCHAR2,
child IN VARCHAR2,
type IN VARCHAR2,
instsite IN VARCHAR2)
RETURN VARCHAR2;
TBD
GET_REFRESH_ADD_USER
Return refresh group dbms_refresh.add execute string
dbms_metadata_util.get_get_refresh_add_user(
owner IN VARCHAR2,
child IN VARCHAR2,
type IN VARCHAR2,
instsite IN VARCHAR2)
RETURN VARCHAR2;
TBD
GET_REFRESH_MAKE_DBA
Return refresh group dbms_irefresh.make execute string
dbms_metadata_util.get_get_refresh_make_dba(group_id IN NUMBER)
RETURN VARCHAR2;
TBD
GET_REFRESH_MAKE_USER
Return refresh group dbms_refresh.make execute string
dbms_metadata_util.get_get_refresh_make_user(group_id IN NUMBER)
RETURN VARCHAR2;
TBD
GET_SOURCE_LINES
Fetch/annotate lines from source$
dbms_metadata_util.get_source_lines(
obj_name IN VARCHAR2,
obj_num IN NUMBER,
type_num IN NUMBER)
RETURN sys.ku$_source_list_t;
SELECT object_id, object_type
FROM dba_objects
WHERE object_name = 'UTL_COLL';
OBJECT_ID OBJECT_TYPE
---------- -------------
14036 PACKAGE
18767 PACKAGE BODY
14037 SYNONYM
SELECT obj#, dataobj#, name, type#
FROM obj$
WHERE obj# = 14036
OBJ# DATAOBJ#
NAME
TYPE#
---------- ---------- ------------------ ------
14036
UTL_COLL
9
DECLARE
retVal sys.ku$_source_list_t;
BEGIN
retVal := dbms_metadata_util.get_source_lines ('UTL_COLL',
14036, 9);
FOR i IN 1 .. retVal.COUNT LOOP
dbms_output.put_line(retVal(i).source);
END LOOP;
END;
/
PACKAGE utl_coll wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
9
b9
d6
1E6p1w9C4063bW1HqiGKlH0mplYwg3nwLZ4VZ3RAkPiOa16Z0f+UqO3oYwTHbJxqCEDiW9GS
+dShW6jPpQi1od906nHnh3KjM4A7S+g5j3Lkl25stKuV2QILZ22DAoW54c0xuJHFsAwF1vEB
lAQLkeaIzzJecUh9NWVM1pRjWIkUafy8
xkq4nD4QU609MSguhGU139H59Adq/WBJVt8=
GET_STRM_MINVER
Retrieve stream minor version based on job version
dbms_metadata_util.get_strm_minver RETURN CHAR;
SELECT dbms_metadata_util.get_strm_minver
FROM dual;
GET_STRM_MINVER
----------------
2
GET_TABPART_TS
Retrieve the TS# for a table (sub)partition (used by dbms_metadata.in_tsnum_2
dbms_metadata_util.get_tabpart_ts(OBJ_NUM IN NUMBER)
RETURN NUMBER;
conn / as sysdba
SELECT object_id, object_type
FROM dba_objects
WHERE object_name = 'OBJ$'
ORDER BY 1;
OBJECT_ID OBJECT_TYPE
---------- -------------------------
18 TABLE
SELECT obj#, ts#
FROM tab$
WHERE obj# = 18;
OBJ# TS#
---------- ----------
18 0
SELECT dbms_metadata_util.get_tabpart_ts (18)
FROM dual;
DBMS_METADATA_UTIL.GET_TABPART_TS(18)
-------------------------------------
0
GET_VERS_DPAPI
Retrieve DPAPI version
dbms_metadata_util.get_vers_dpapi RETURN NUMBER;
SELECT dbms_metadata_util.get_vers_dpapi
FROM dual;
GET_VERS_DPAPI
--------------
0
GET_XMLCOLSET
Return nested table of intcol numbers for Object Relational storage columns for xmltypes in table
dbms_metadata_util.get_xmlcolset(obj_num IN NUMBER)
RETURN ku$_XmlColSet_t;
TBD
GET_XMLHIERARCHY
Returns 'Y' if the table is hierachy enabled, otherwise NULL
dbms_metadata_util.get_xmlcolset(
schema IN VARCHAR2,
name IN VARCHAR2)
RETURN CHAR;
TBD
GET_XMLTYPE_FMTS
Return formats of XMLType columns in a table
dbms_metadata_util.get_xmltype_fmts(objnum IN NUMBER) RETURN NUMBER;
TBD
GLO
Undocumented fix for bug 12866600 which, at least on my system, is an unreproducible bug
dbms_metadata_util.glo(inval IN RAW) RETURN RAW;
SELECT utl_raw.cast_to_raw('Morgan')
FROM dual;
UTL_RAW.CAST_TO_RAW('MORGAN')
------------------------------
4D6F7267616E
SELECT dbms_metadata_util.glo (utl_raw.cast_to_raw('Morgan'))
FROM dual;
DBMS_METADATA_UTIL.GLO(UTL_RAW.CAST_TO_RAW('MORGAN'))
------------------------------------------------------
4D6F7267616E
HAS_TSTZ_COLS
Returns "Y" if a table contains a type DTYSTZ (type# = 181): "TIMESTAMP WITH TIME ZONE" column
dbms_metadata_util.has_tstz_cols(obj_num IN NUMBER) RETURN CHAR;
SELECT column_name, data_type
FROM dba_tab_cols
WHERE table_name = 'TSM_SRC$';
SELECT object_id
FROM dba_objects
WHERE object_name = 'TSM_SRC$';
SELECT dbms_metadata_util.has_tstz_cols (366)
FROM dual;
HAS_TSTZ_ELEMENTS
Determines whether a varray type has TSTZ elements. This is a wrapper around utl_xml.haststz.
dbms_metadata_util.has_tstz_elements(
type_schema IN VARCHAR2,
type_name IN VARCHAR2)
RETURN CHAR;
TBD
ISXML
Returns 1 if an XMLType column: Otherwise 0
Overload 1
dbms_metadata_util.isXML(
obj_num IN NUMBER,
intcol IN NUMBER)
RETURN NUMBER;
TBD
Overload 2
dbms_metadata_util.isXML(nt_num IN NUMBER)
RETURN NUMBER;
TBD
IS_OMF
Determine if a name is a Oracle Managed File (OMF)
dbms_metadata_util.is_omf(name IN VARCHAR2) RETURN NUMBER;
set serveroutput on
DECLARE
dfn dba_data_files.file_name%TYPE;
BEGIN
SELECT file_name
INTO dfn
FROM dba_data_files
WHERE rownum = 1;
dbms_output.put_line(dbms_metadata_util.is_omf (dfn));
END;
/
IS_SCHEMANAME_EXISTS
Return 1 if schema name exists in trigger definition 0 other wise
dbms_metadata_util.is_schemaname_exists(tdefinition IN VARCHAR2)
RETURN NUMBER;
TBD
IS_SHARD_TBL_IN_SHARD_CATALOG (new 21c)
Return 1 if table whose obj# passed in is a shard table in a shard catalog, else 0
dbms_metadata_util.is_shard_tbl_in_shard_catalog(obj_num IN NUMBER) RETURN NUMBER;
SELECT dbms_metadata_util.is_shard_tbl_in_shard_catalog (127367)
FROM dual;
DBMS_METADATA_UTIL.IS_SHARD_TBL_IN_SHARD_CATALOG(127367)
--------------------------------------------------------
0
LOAD_STYLESHEETS
Load the XSL stylesheets into the database
dbms_metadata_util.load_stylesheets;
exec dbms_metadata_util.load_stylesheets ;
-- the following is from $ORACLE_HOME/rdbms/admin/initmeta.sql and clearly it should fail
EXECUTE SYS.DBMS_METADATA_UTIL.LOAD_STYLESHEETS('C:\app2\oracle\product\12.2.0\dbhome_1\rdbms\xml\xsl');
SQL> EXECUTE SYS.DBMS_METADATA_UTIL.LOAD_STYLESHEETS('C:\app2\oracle\product\12.2.0\dbhome_1\rdbms\xml\xsl');
BEGIN SYS.DBMS_METADATA_UTIL.LOAD_STYLESHEETS('C:\app2\oracle\product\12.2.0\dbhome_1\rdbms\xml\xsl'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'LOAD_STYLESHEETS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
-- and it does as you see ... whoever at Oracle wrote the code that generates this ... didn't test it.
LOAD_TEMP_TABLE
Copy sys.x$ktfbue into a temporary table for subsequent use in computing bytes allocated
dbms_metadata_util.load_temp_table;
SELECT sql_id, last_load_time
FROM v$sqlarea
WHERE last_load_time > sysdate-1/24
AND LOWER(sql_fulltext) LIKE '%insert%'
ORDER BY last_load_time;
exec dbms_metadata_util.load_temp_table ;
-- the statement being executed:
INSERT INTO SYS.KU$XKTFBUE
SELECT KTFBUESEGTSN, KTFBUESEGFNO, KTFBUESEGBNO, KTFBUEBLKS
FROM SYS.X$KTFBUE;
LOAD_XSD
Calls dbms_xmlschema.registerSchema to register the named schema
dbms_metadata_util.load_xsd(filename IN VARCHAR2);
TBD
LONG2CLOB
Convert a table LONG value to a CLOB
dbms_metadata_util.long2clob(
length IN NUMBER,
tab IN VARCHAR2,
col IN VARCHAR2,
row IN ROWID)
RETURN CLOB;
-- this demo creates the exception shown when run as shown but, as can be seen, it is just an API to a UTL_XML function_
set serveroutput on
DECLARE
retVal VARCHAR2(4000);
rid rowid;
BEGIN
SELECT rowid
INTO rid
FROM view$
WHERE obj# IN (SELECT obj# FROM obj$ WHERE name = 'DBA_VIEWS' AND type# = 4);
retVal := dbms_metadata_util.long2clob (120, 'DBA_VIEWS', 'TEXT', rid);
dbms_output.put_line(retVal);
END;
/
META:20:47:23.640: exception from 'SELECT TEXT FROM DBA_VIEWS WHERE ROWID = :1' for rowid value AAAA
ORA-31600: invalid input value DBA_VIEWS for parameter TAB in function UTL_XML.LONG2CLOB
LONG2VARCHAR
Convert a table LONG value to a VARCHAR2
dbms_metadata_util.long2varchar(
length IN NUMBER,
tab IN VARCHAR2,
col IN VARCHAR2,
row IN UROWID)
RETURN VARCHAR2;
-- I can not get the demo to return a string but it does compile and run
set serveroutput on
DECLARE
retVal VARCHAR2(4000);
urid urowid;
BEGIN
SELECT rowid
INTO urid
FROM view$
WHERE obj# IN (SELECT obj# FROM obj$ WHERE name = 'DBA_VIEWS' AND type# = 4);
retVal := dbms_metadata_util.long2varchar (120, 'DBA_VIEWS', 'TEXT', urid);
dbms_output.put_line(retVal);
END;
/
LONG2VCMAX
Convert a table LONG value to a VARCHAR2 table and each line max length is 2000
dbms_metadata_util.long2vcmax(
length IN NUMBER,
tab IN VARCHAR2,
col IN VARCHAR2,
row IN UROWID)
RETURN sys.ku$_vcnt;
TBD
LONG2VCNT
Convert a table LONG value to a nested table of VARCHAR2
dbms_metadata_util.long2vcnt(
length IN NUMBER,
tab IN VARCHAR2,
col IN VARCHAR2,
row IN UROWID)
RETURN sys.ku$_vcnt;
TBD
NULLTOCHR0
Replace \0 with CHR(0) in a VARCHAR2
dbms_metadata_util.nulltochr0(
value IN VARCHAR2,
replace_quote IN BOOLEAN DEFAULT TRUE)
RETURN VARCHAR2;
DECLARE
testVal VARCHAR2(30) := 'Daniel A\0''Morgan';
BEGIN
dbms_output.put_line(testVal);
testVal := dbms_metadata_util.nulltochr0 (testVal, TRUE);
dbms_output.put_line(testVal);
END;
/
Daniel A\0'Morgan
Daniel A\0''Morgan
PARSE_TRIGGER_DEFINITION
Return annotated trigger definition
dbms_metadata_util.parse_trigger_definition(
obj_name IN VARCHAR2,
definition IN VARCHAR2)
RETURN sys.ku$_source_t;
TBD
PATCH_TYPEID
For transportable import, modify a type's typeid
dbms_metadata_util.patch_typeid(
schema IN VARCHAR2,
name IN VARCHAR2,
typeid IN VARCHAR2,
hashcode IN VARCHAR2);
TBD
PUT_BOOL
Write debugging output
dbms_metadata_util.put_bool(
stmt IN VARCHAR2,
value IN BOOLEAN);
BEGIN
dbms_metadata_util.put_bool ('This is ', TRUE);
END;
/
PUT_LINE
Does a DBMS_OUTPUT.PUT_LINE regardless of string length; i.e, works with strings > 255
dbms_metadata_util.put_line(stmt IN VARCHAR2);
set serveroutput on
exec dbms_metadata_util.put_line ('This works');
REF_PAR_LEVEL
Return level of ref partitioned child table
Overload 1
dbms_metadata_util.ref_par_level(objnum IN NUMBER)
RETURN NUMBER;
TBD
Overload 2
dbms_metadata_util.ref_par_level(
objnum IN NUMBER,
properties IN NUMBER)
RETURN NUMBER;
TBD
REF_PAR_PARENT
Return object number of ref partitioned parent table
dbms_metadata_util.ref_par_parent(objnum IN NUMBER)
RETURN NUMBER;
TBD
SAVE_PROCOBJ_ERRORS
Save errors raised by procedural object code
dbms_metadata_util.save_procobj_errors(sql_stmt IN VARCHAR2);
TBD
SET_BLOCK_ESTIMATE
Sets the state for the estimate phase
dbms_metadata_util.set_block_estimate(value IN BOOLEAN);
exec dbms_metadata_util.set_block_estimate (TRUE);
SET_DEBUG
Set the internal debug switch
dbms_metadata_util.set_debug(
on_off IN BOOLEAN,
force_trace IN BOOLEAN DEFAULT FALSE);
exec dbms_metadata_util.set_debug (FALSE, FALSE);
SET_FORCE_LOB_BE
Save the 'force_lob_be' switch
dbms_metadata_util.set_force_lob_be(value IN BOOLEAN);
exec dbms_metadata_util.set_force_lob_be (FALSE);
SET_FORCE_NO_ENCRYPT
Save the 'force_no_encrypt' switch
dbms_metadata_util.set_force_no_encrypt(value IN BOOLEAN);
exec dbms_metadata_util.set_force_no_encrypt (TRUE);
SET_MARKER
Sets the current marker number
dbms_metadata_util.set_marker(new_marker IN NUMBER)
ACCESSIBLE BY (PACKAGE sys.dbms_metadata_int);
No direct demo possible due to ACCESSIBLE BY clause
SET_VERS_DPAPI
Save DPAPI version
dbms_metadata_util.set_vers_dpapi(version IN NUMBER);
SELECT dbms_metadata_util.get_vers_dpapi
FROM dual;
exec dbms_metadata_util.set_vers_dpapi (0.99);
SELECT dbms_metadata_util.get_vers_dpapi
FROM dual;
exec dbms_metadata_util.set_vers_dpapi (0);
SELECT dbms_metadata_util.get_vers_dpapi
FROM dual;
TABLE_TSNUM
Returns a table's canonical tablespace number
dbms_metadata_util.table_tsnum(objnum IN NUMBER)
RETURN NUMBER;
conn / as sysdba
SELECT object_id
FROM dba_objects
WHERE object_name = 'TAB$';
SELECT dbms_metadata_util.table_tsnum (4)
FROM dual;
VSN2NUM
Convert a dot-separated version string
dbms_metadata_util.vsn2num(vsn IN VARCHAR2)
RETURN NUMBER;
SELECT dbms_metadata_util.vsn2num ('21.1.0.1.0')
FROM dual;
DBMS_METADATA_UTIL.VSN2NUM('21.1.0.1.0')
----------------------------------------
2101000100
WRITE_CLOB
Write a CLOB to the trace file
dbms_metadata_util.write_clob(xml IN CLOB);
DECLARE
clobvar CLOB := 'This is a test';
BEGIN
dbms_metadata_util.write_clob (clobvar);
END;
/
-- runs successfully but I can not find any indication of where it was written,
-- definitely not to the alert log.