VIEW: SYS.ALL_SCHEDULER_JOB_ARGS

Source

View Text - Preformatted

SELECT u.name, o.name, b.name, t.position,
  CASE WHEN (b.user_type_num IS NULL) THEN
    DECODE(b.type_number,
0, null,
1, decode(b.flags, 512, 'NVARCHAR2', 'VARCHAR2'),
2, decode(b.flags, 512, 'FLOAT', 'NUMBER'),
3, 'NATIVE INTEGER',
8, 'LONG',
9, decode(b.flags, 512, 'NCHAR VARYING', 'VARCHAR'),
11, 'ROWID',
12, 'DATE',
23, 'RAW',
24, 'LONG RAW',
29, 'BINARY_INTEGER',
69, 'ROWID',
96, decode(b.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(b.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,
  dbms_scheduler.get_varchar2_value(t.value), t.value,
  DECODE(BITAND(b.flags,1),0,'FALSE',1,'TRUE')
  FROM obj$ t_o, user$ t_u,
    sys.scheduler$_program_argument b, obj$ o, user$ u, (
  SELECT a.oid job_oid, a.position position,
      po.obj# program_oid, a.value value
   FROM  sys.scheduler$_job_argument a
      JOIN sys.scheduler$_job j ON a.oid = j.obj#
      LEFT OUTER JOIN sys.user$ pu ON
       pu.name =  DECODE(bitand(j.flags,4194304),4194304,
          substr(j.program_action,1,instr(j.program_action,'"')-1),'1')
      LEFT OUTER JOIN sys.obj$ po ON
          pu.user#=po.owner# and
          po.name =
    DECODE(bitand(j.flags,4194304),4194304,
      substr(j.program_action,instr(j.program_action,'"')+1,
        length(j.program_action)-instr(j.program_action,'"')) ,'1')
    ) t
  WHERE t.job_oid = o.obj# AND u.user# = o.owner#
    AND b.user_type_num = t_o.obj#(+) AND t_o.owner# = t_u.user#(+)
    AND t.program_oid=b.oid(+) AND t.position=b.position(+) 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 */ )
                 )
          and o.owner#!=0)
      )
UNION ALL
SELECT lu.name, lo.name, lb.name, lt.position,
  CASE WHEN (lb.user_type_num IS NULL) THEN
    DECODE(lb.type_number,
0, null,
1, decode(lb.flags, 512, 'NVARCHAR2', 'VARCHAR2'),
2, decode(lb.flags, 512, 'FLOAT', 'NUMBER'),
3, 'NATIVE INTEGER',
8, 'LONG',
9, decode(lb.flags, 512, 'NCHAR VARYING', 'VARCHAR'),
11, 'ROWID',
12, 'DATE',
23, 'RAW',
24, 'LONG RAW',
29, 'BINARY_INTEGER',
69, 'ROWID',
96, decode(lb.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(lb.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 lt_u.name ||'.'|| lt_o.name END,
  dbms_scheduler.get_varchar2_value(lt.value), lt.value,
  DECODE(BITAND(lb.flags,1),0,'FALSE',1,'TRUE')
  FROM obj$ lt_o, user$ lt_u, sys.obj$ lpo,
    sys.scheduler$_program_argument lb,
    sys.scheduler$_lwjob_obj lo, user$ lu,
    (SELECT la.oid job_oid, la.position position,
      decode(bitand(lj.flags, 8589934592), 0, lj.program_oid,
             ljp.program_oid) program_oid, lj.program_oid job_prog,
      lj.flags flags, la.value value
    FROM sys.scheduler$_comb_lw_job lj, sys.scheduler$_job_argument la,
         sys.scheduler$_job ljp
    WHERE lj.program_oid = ljp.obj#(+) and
       bitand(lj.flags, 137438953472) = 0 and
       la.oid = lj.obj#) lt
  WHERE lt.job_oid = lo.obj# AND lu.user# = lo.userid
    AND lb.user_type_num = lt_o.obj#(+) AND lt_o.owner# = lt_u.user#(+)
    AND lt.program_oid=lb.oid(+) AND lt.position=lb.position(+) AND
        lt.job_prog = lpo.obj# AND
        ((bitand(lt.flags, 8589934592) = 0 and lpo.type# = 67) or
         (bitand(lt.flags, 8589934592) <> 0 and lpo.type# = 66)) AND
    (lo.userid = userenv('SCHEMAID')
       or lt.job_prog in
            (select loa.obj#
             from sys.objauth$ loa
             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 */ )
                 )
          and lo.userid!=0)
      )

View Text - HTML Formatted

SELECT U.NAME
, O.NAME
, B.NAME
, T.POSITION
, CASE WHEN (B.USER_TYPE_NUM IS NULL) THEN DECODE(B.TYPE_NUMBER
, 0
, NULL
, 1
, DECODE(B.FLAGS
, 512
, 'NVARCHAR2'
, 'VARCHAR2')
, 2
, DECODE(B.FLAGS
, 512
, 'FLOAT'
, 'NUMBER')
, 3
, 'NATIVE INTEGER'
, 8
, 'LONG'
, 9
, DECODE(B.FLAGS
, 512
, 'NCHAR VARYING'
, 'VARCHAR')
, 11
, 'ROWID'
, 12
, 'DATE'
, 23
, 'RAW'
, 24
, 'LONG RAW'
, 29
, 'BINARY_INTEGER'
, 69
, 'ROWID'
, 96
, DECODE(B.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(B.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
, DBMS_SCHEDULER.GET_VARCHAR2_VALUE(T.VALUE)
, T.VALUE
, DECODE(BITAND(B.FLAGS
, 1)
, 0
, 'FALSE'
, 1
, 'TRUE')
FROM OBJ$ T_O
, USER$ T_U
, SYS.SCHEDULER$_PROGRAM_ARGUMENT B
, OBJ$ O
, USER$ U
, ( SELECT A.OID JOB_OID
, A.POSITION POSITION
, PO.OBJ# PROGRAM_OID
, A.VALUE VALUE
FROM SYS.SCHEDULER$_JOB_ARGUMENT A JOIN SYS.SCHEDULER$_JOB J ON A.OID = J.OBJ# LEFT OUTER JOIN SYS.USER$ PU ON PU.NAME = DECODE(BITAND(J.FLAGS
, 4194304)
, 4194304
, SUBSTR(J.PROGRAM_ACTION
, 1
, INSTR(J.PROGRAM_ACTION
, '"')-1)
, '1') LEFT OUTER JOIN SYS.OBJ$ PO ON PU.USER#=PO.OWNER# AND PO.NAME = DECODE(BITAND(J.FLAGS
, 4194304)
, 4194304
, SUBSTR(J.PROGRAM_ACTION
, INSTR(J.PROGRAM_ACTION
, '"')+1
, LENGTH(J.PROGRAM_ACTION)-INSTR(J.PROGRAM_ACTION
, '"'))
, '1') ) T
WHERE T.JOB_OID = O.OBJ#
AND U.USER# = O.OWNER#
AND B.USER_TYPE_NUM = T_O.OBJ#(+)
AND T_O.OWNER# = T_U.USER#(+)
AND T.PROGRAM_OID=B.OID(+)
AND T.POSITION=B.POSITION(+) 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 */ ) )
AND O.OWNER#!=0) ) UNION ALL SELECT LU.NAME
, LO.NAME
, LB.NAME
, LT.POSITION
, CASE WHEN (LB.USER_TYPE_NUM IS NULL) THEN DECODE(LB.TYPE_NUMBER
, 0
, NULL
, 1
, DECODE(LB.FLAGS
, 512
, 'NVARCHAR2'
, 'VARCHAR2')
, 2
, DECODE(LB.FLAGS
, 512
, 'FLOAT'
, 'NUMBER')
, 3
, 'NATIVE INTEGER'
, 8
, 'LONG'
, 9
, DECODE(LB.FLAGS
, 512
, 'NCHAR VARYING'
, 'VARCHAR')
, 11
, 'ROWID'
, 12
, 'DATE'
, 23
, 'RAW'
, 24
, 'LONG RAW'
, 29
, 'BINARY_INTEGER'
, 69
, 'ROWID'
, 96
, DECODE(LB.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(LB.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 LT_U.NAME ||'.'|| LT_O.NAME END
, DBMS_SCHEDULER.GET_VARCHAR2_VALUE(LT.VALUE)
, LT.VALUE
, DECODE(BITAND(LB.FLAGS
, 1)
, 0
, 'FALSE'
, 1
, 'TRUE')
FROM OBJ$ LT_O
, USER$ LT_U
, SYS.OBJ$ LPO
, SYS.SCHEDULER$_PROGRAM_ARGUMENT LB
, SYS.SCHEDULER$_LWJOB_OBJ LO
, USER$ LU
, (SELECT LA.OID JOB_OID
, LA.POSITION POSITION
, DECODE(BITAND(LJ.FLAGS
, 8589934592)
, 0
, LJ.PROGRAM_OID
, LJP.PROGRAM_OID) PROGRAM_OID
, LJ.PROGRAM_OID JOB_PROG
, LJ.FLAGS FLAGS
, LA.VALUE VALUE
FROM SYS.SCHEDULER$_COMB_LW_JOB LJ
, SYS.SCHEDULER$_JOB_ARGUMENT LA
, SYS.SCHEDULER$_JOB LJP
WHERE LJ.PROGRAM_OID = LJP.OBJ#(+) AND BITAND(LJ.FLAGS
, 137438953472) = 0 AND LA.OID = LJ.OBJ#) LT
WHERE LT.JOB_OID = LO.OBJ#
AND LU.USER# = LO.USERID
AND LB.USER_TYPE_NUM = LT_O.OBJ#(+)
AND LT_O.OWNER# = LT_U.USER#(+)
AND LT.PROGRAM_OID=LB.OID(+)
AND LT.POSITION=LB.POSITION(+) AND LT.JOB_PROG = LPO.OBJ# AND ((BITAND(LT.FLAGS
, 8589934592) = 0
AND LPO.TYPE# = 67) OR (BITAND(LT.FLAGS
, 8589934592) <> 0
AND LPO.TYPE# = 66)) AND (LO.USERID = USERENV('SCHEMAID') OR LT.JOB_PROG IN (SELECT LOA.OBJ#
FROM SYS.OBJAUTH$ LOA
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 */ ) )
AND LO.USERID!=0) )