VIEW: SYS.USER_ADVISOR_EXEC_PARAMETERS

Source

View Text - Preformatted

select tp.task_id, task_name, execution_name, execution_type,
             parameter_name,  nvl(ep.value, tp.value) as parameter_value,
             parameter_type, is_default, is_output, is_modifiable_anytime,
             tp.description, parameter_flags, parameter_type#
      from   (select t.owner_name as owner, t.id as task_id,
               t.name as task_name, e.name as execution_name,
               p.name as parameter_name, p.value,
               decode(d.datatype, 1, 'NUMBER',
                                  2, 'STRING',
                                  3, 'STRINGLIST',
                                  4, 'TABLE',
                                  5, 'TABLELIST',
                                  'UNKNOWN') as parameter_type,
               d.datatype as parameter_type#,
               decode(bitand(d.flags, 2), 0, 'Y', 'N') as is_default,
               decode(bitand(d.flags, 4), 0, 'N', 'Y') as is_output,
               decode(bitand(d.flags, 8), 0, 'N', 'Y') as is_modifiable_anytime,
               dbms_advisor.format_message(d.description) as description,
               d.exec_type as execution_type,
               d.flags as parameter_flags
             from wri$_adv_parameters p,
                  wri$_adv_tasks t,
                  wri$_adv_def_parameters d,
                  wri$_adv_executions e
             where p.task_id = t.id
               and bitand(t.property, 4) = 4       /* task property */
               and bitand(d.flags, 1) = 0          /* invisible parameter */
               and (bitand(t.property, 32) = 32 or /* system task only prm */
                    bitand(d.flags, 16) = 0)
               and p.name = d.name
               and (t.advisor_id = d.advisor_id or d.advisor_id = 0)
               and e.task_id = p.task_id
               and t.owner# = userenv('SCHEMAID')) tp,
              wri$_adv_exec_parameters ep
      where tp.task_id = ep.task_id (+)
        and tp.parameter_name = ep.name (+)
        and tp.execution_name = ep.exec_name (+)

View Text - HTML Formatted

SELECT TP.TASK_ID
, TASK_NAME
, EXECUTION_NAME
, EXECUTION_TYPE
, PARAMETER_NAME
, NVL(EP.VALUE
, TP.VALUE) AS PARAMETER_VALUE
, PARAMETER_TYPE
, IS_DEFAULT
, IS_OUTPUT
, IS_MODIFIABLE_ANYTIME
, TP.DESCRIPTION
, PARAMETER_FLAGS
, PARAMETER_TYPE#
FROM (SELECT T.OWNER_NAME AS OWNER
, T.ID AS TASK_ID
, T.NAME AS TASK_NAME
, E.NAME AS EXECUTION_NAME
, P.NAME AS PARAMETER_NAME
, P.VALUE
, DECODE(D.DATATYPE
, 1
, 'NUMBER'
, 2
, 'STRING'
, 3
, 'STRINGLIST'
, 4
, 'TABLE'
, 5
, 'TABLELIST'
, 'UNKNOWN') AS PARAMETER_TYPE
, D.DATATYPE AS PARAMETER_TYPE#
, DECODE(BITAND(D.FLAGS
, 2)
, 0
, 'Y'
, 'N') AS IS_DEFAULT
, DECODE(BITAND(D.FLAGS
, 4)
, 0
, 'N'
, 'Y') AS IS_OUTPUT
, DECODE(BITAND(D.FLAGS
, 8)
, 0
, 'N'
, 'Y') AS IS_MODIFIABLE_ANYTIME
, DBMS_ADVISOR.FORMAT_MESSAGE(D.DESCRIPTION) AS DESCRIPTION
, D.EXEC_TYPE AS EXECUTION_TYPE
, D.FLAGS AS PARAMETER_FLAGS
FROM WRI$_ADV_PARAMETERS P
, WRI$_ADV_TASKS T
, WRI$_ADV_DEF_PARAMETERS D
, WRI$_ADV_EXECUTIONS E
WHERE P.TASK_ID = T.ID
AND BITAND(T.PROPERTY
, 4) = 4 /* TASK PROPERTY */
AND BITAND(D.FLAGS
, 1) = 0 /* INVISIBLE PARAMETER */
AND (BITAND(T.PROPERTY
, 32) = 32 OR /* SYSTEM TASK ONLY PRM */ BITAND(D.FLAGS
, 16) = 0)
AND P.NAME = D.NAME
AND (T.ADVISOR_ID = D.ADVISOR_ID OR D.ADVISOR_ID = 0)
AND E.TASK_ID = P.TASK_ID
AND T.OWNER# = USERENV('SCHEMAID')) TP
, WRI$_ADV_EXEC_PARAMETERS EP
WHERE TP.TASK_ID = EP.TASK_ID (+)
AND TP.PARAMETER_NAME = EP.NAME (+)
AND TP.EXECUTION_NAME = EP.EXEC_NAME (+)