VIEW: SYS.ALL_SCHEDULER_PROGRAM_ARGS

Source

View Text - Preformatted

SELECT u.name, o.name, a.name, a.position,
  CASE WHEN (a.user_type_num IS NULL) THEN
    DECODE(a.type_number,
0, null,
1, decode(a.flags, 512, 'NVARCHAR2', 'VARCHAR2'),
2, decode(a.flags, 512, 'FLOAT', 'NUMBER'),
3, 'NATIVE INTEGER',
8, 'LONG',
9, decode(a.flags, 512, 'NCHAR VARYING', 'VARCHAR'),
11, 'ROWID',
12, 'DATE',
23, 'RAW',
24, 'LONG RAW',
29, 'BINARY_INTEGER',
69, 'ROWID',
96, decode(a.flags, 512, 'NCHAR', 'CHAR'),
100, 'BINARY_FLOAT',
101, 'BINARY_DOUBLE',
102, 'REF CURSOR',
104, 'UROWID',
105, 'MLSLABEL',
106, 'MLSLABEL',
110, 'REF',
111, 'REF',
112, decode(a.flags, 512, 'NCLOB', 'CLOB'),
113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
121, 'OBJECT',
122, 'TABLE',
123, 'VARRAY',
178, 'TIME',
179, 'TIME WITH TIME ZONE',
180, 'TIMESTAMP',
181, 'TIMESTAMP WITH TIME ZONE',
231, 'TIMESTAMP WITH LOCAL TIME ZONE',
182, 'INTERVAL YEAR TO MONTH',
183, 'INTERVAL DAY TO SECOND',
250, 'PL/SQL RECORD',
251, 'PL/SQL TABLE',
252, 'PL/SQL BOOLEAN',
'UNDEFINED')
    ELSE t_u.name ||'.'|| t_o.name END,
  DECODE(bitand(a.flags, 2+4+64+128+256+1024+2048+8192+16384+32768
         +65536+131072+262144+524288+1048576),
         2,'JOB_NAME',4,'JOB_OWNER',
         64, 'JOB_START', 128, 'WINDOW_START',
         256, 'WINDOW_END', 1024, 'JOB_SUBNAME',
         2048, 'EVENT_MESSAGE', 8192, 'JOB_SCHEDULED_START',
         16384, 'CHAIN_ID', 32768, 'CREDENTIAL_OWNER',
         65536, 'CREDENTIAL_NAME', 131072, 'DESTINATION_OWNER',
         262144, 'DESTINATION_NAME', 524288, 'JOB_DEST_ID',
         1048576, 'LOG_ID', ''),
  dbms_scheduler.get_varchar2_value(a.value), a.value,
  DECODE(BITAND(a.flags,1),0,'FALSE',1,'TRUE')
  FROM obj$ o, user$ u, sys.scheduler$_program_argument a, obj$ t_o, user$ t_u
  WHERE a.oid = o.obj# AND u.user# = o.owner# AND
    a.user_type_num = t_o.obj#(+) AND t_o.owner# = t_u.user#(+) AND
    (o.owner# = userenv('SCHEMAID')
       or o.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               )
            )
       or /* user has system privileges */
         (exists (select null from v$enabledprivs
                 where priv_number in (-265 /* CREATE ANY JOB */,
                                       -255 /* EXPORT FULL DATABASE */,
                                       -266 /* EXECUTE ANY PROGRAM */ )
                 )
          and o.owner#!=0)
      )

View Text - HTML Formatted

SELECT U.NAME
, O.NAME
, A.NAME
, A.POSITION
, CASE WHEN (A.USER_TYPE_NUM IS NULL) THEN DECODE(A.TYPE_NUMBER
, 0
, NULL
, 1
, DECODE(A.FLAGS
, 512
, 'NVARCHAR2'
, 'VARCHAR2')
, 2
, DECODE(A.FLAGS
, 512
, 'FLOAT'
, 'NUMBER')
, 3
, 'NATIVE INTEGER'
, 8
, 'LONG'
, 9
, DECODE(A.FLAGS
, 512
, 'NCHAR VARYING'
, 'VARCHAR')
, 11
, 'ROWID'
, 12
, 'DATE'
, 23
, 'RAW'
, 24
, 'LONG RAW'
, 29
, 'BINARY_INTEGER'
, 69
, 'ROWID'
, 96
, DECODE(A.FLAGS
, 512
, 'NCHAR'
, 'CHAR')
, 100
, 'BINARY_FLOAT'
, 101
, 'BINARY_DOUBLE'
, 102
, 'REF CURSOR'
, 104
, 'UROWID'
, 105
, 'MLSLABEL'
, 106
, 'MLSLABEL'
, 110
, 'REF'
, 111
, 'REF'
, 112
, DECODE(A.FLAGS
, 512
, 'NCLOB'
, 'CLOB')
, 113
, 'BLOB'
, 114
, 'BFILE'
, 115
, 'CFILE'
, 121
, 'OBJECT'
, 122
, 'TABLE'
, 123
, 'VARRAY'
, 178
, 'TIME'
, 179
, 'TIME WITH TIME ZONE'
, 180
, 'TIMESTAMP'
, 181
, 'TIMESTAMP WITH TIME ZONE'
, 231
, 'TIMESTAMP WITH LOCAL TIME ZONE'
, 182
, 'INTERVAL YEAR TO MONTH'
, 183
, 'INTERVAL DAY TO SECOND'
, 250
, 'PL/SQL RECORD'
, 251
, 'PL/SQL TABLE'
, 252
, 'PL/SQL BOOLEAN'
, 'UNDEFINED') ELSE T_U.NAME ||'.'|| T_O.NAME END
, DECODE(BITAND(A.FLAGS
, 2+4+64+128+256+1024+2048+8192+16384+32768 +65536+131072+262144+524288+1048576)
, 2
, 'JOB_NAME'
, 4
, 'JOB_OWNER'
, 64
, 'JOB_START'
, 128
, 'WINDOW_START'
, 256
, 'WINDOW_END'
, 1024
, 'JOB_SUBNAME'
, 2048
, 'EVENT_MESSAGE'
, 8192
, 'JOB_SCHEDULED_START'
, 16384
, 'CHAIN_ID'
, 32768
, 'CREDENTIAL_OWNER'
, 65536
, 'CREDENTIAL_NAME'
, 131072
, 'DESTINATION_OWNER'
, 262144
, 'DESTINATION_NAME'
, 524288
, 'JOB_DEST_ID'
, 1048576
, 'LOG_ID'
, '')
, DBMS_SCHEDULER.GET_VARCHAR2_VALUE(A.VALUE)
, A.VALUE
, DECODE(BITAND(A.FLAGS
, 1)
, 0
, 'FALSE'
, 1
, 'TRUE')
FROM OBJ$ O
, USER$ U
, SYS.SCHEDULER$_PROGRAM_ARGUMENT A
, OBJ$ T_O
, USER$ T_U
WHERE A.OID = O.OBJ#
AND U.USER# = O.OWNER# AND A.USER_TYPE_NUM = T_O.OBJ#(+)
AND T_O.OWNER# = T_U.USER#(+) AND (O.OWNER# = USERENV('SCHEMAID') OR O.OBJ# IN (SELECT OA.OBJ#
FROM SYS.OBJAUTH$ OA
WHERE GRANTEE# IN ( SELECT KZSROROL
FROM X$KZSRO ) ) OR /* USER HAS SYSTEM PRIVILEGES */ (EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-265 /* CREATE ANY JOB */
, -255 /* EXPORT FULL DATABASE */
, -266 /* EXECUTE ANY PROGRAM */ ) )
AND O.OWNER#!=0) )