DBA_METHOD_PARAMS view tips

Oracle Tips by Burleson Consulting

DBA_METHOD_PARAMS

<< Return to the index

Oracle 11g makes the following comments about the DBA_METHOD_PARAMS table: "Description of method parameters of all types in the database"

Related notes on DBA_METHOD_PARAMS:

Column description of the DBA_METHOD_PARAMS view:

OWNER

Description of DBA_METHOD_PARAMS.OWNER: "Onwer of the type"

TYPE_NAME

Description of DBA_METHOD_PARAMS.TYPE_NAME: "Name of the type"

METHOD_NAME

Description of DBA_METHOD_PARAMS.METHOD_NAME: "Name of the method"

METHOD_NO

Description of DBA_METHOD_PARAMS.METHOD_NO: "Method number for distinguishing overloaded method (not to be used as ID number)"

PARAM_NAME

Description of DBA_METHOD_PARAMS.PARAM_NAME: "Name of the parameter"

PARAM_NO

Description of DBA_METHOD_PARAMS.PARAM_NO: "Parameter number or position"

PARAM_MODE

Description of DBA_METHOD_PARAMS.PARAM_MODE: "Mode of the parameter"

PARAM_TYPE_MOD

Description of DBA_METHOD_PARAMS.PARAM_TYPE_MOD: "Type modifier of the parameter"

PARAM_TYPE_OWNER

Description of DBA_METHOD_PARAMS.PARAM_TYPE_OWNER: "Owner of the type of the parameter"

PARAM_TYPE_NAME

Description of DBA_METHOD_PARAMS.PARAM_TYPE_NAME: "Name of the type of the parameter"

CHARACTER_SET_NAME

Description of DBA_METHOD_PARAMS.CHARACTER_SET_NAME: "Character set name of the parameter"

DBA_METHOD_PARAMS View Source

Oracle 11g's data dictionary defines the DBA_METHOD_PARAMS view using the following source query:

select u.name, o.name, m.name, m.method#,
p.name, p.parameter#,
decode(bitand(p.properties, 768), 768, 'IN OUT',
decode(bitand(p.properties, 256), 256, 'IN',
decode(bitand(p.properties, 512), 512, 'OUT'))),
decode(bitand(p.properties, 32768), 32768, 'REF',
decode(bitand(p.properties, 16384), 16384, 'POINTER')),
decode(bitand(pt.properties, 64), 64, null, pu.name),
decode(pt.typecode,
9, decode(p.charsetform, 2, 'NVARCHAR2', po.name),
96, decode(p.charsetform, 2, 'NCHAR', po.name),
112, decode(p.charsetform, 2, 'NCLOB', po.name),
po.name),
decode(p.charsetform, 1, 'CHAR_CS',
2, 'NCHAR_CS',
3, NLS_CHARSET_NAME(p.charsetid),
4, 'ARG:'||p.charsetid)
from sys.user$ u, sys."_CURRENT_EDITION_OBJ" o, sys.method$ m, sys.parameter$ p,
sys."_CURRENT_EDITION_OBJ" po, sys.user$ pu, sys.type$ pt
where o.owner# = u.user#
and o.type# <> 10 -- must not be invalid
and o.oid$ = m.toid
and o.subname IS NULL -- get the latest version only
and m.toid = p.toid
and m.version# = p.version#
and m.method# = p.method#
and p.param_toid = po.oid$
and po.owner# = pu.user#
and p.param_toid = pt.toid
and p.param_version# = pt.version#

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.
You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books