VIEW: SYS.USER_SCHEDULER_JOB_ARGS

Source

View Text - Preformatted

SELECT 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  sys.scheduler$_program_argument b, obj$ t_o, user$ t_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,
   obj$ o
  WHERE t.job_oid = o.obj# AND o.owner# = USERENV('SCHEMAID')
    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(+)
UNION ALL
SELECT 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  sys.scheduler$_program_argument lb, obj$ lt_o, user$ lt_u,
    (SELECT la.oid job_oid, la.position position,
      decode(bitand(lj.flags, 8589934592), 0, lj.program_oid,
             ljp.program_oid) program_oid,
      la.value value
    FROM sys.scheduler$_job_argument la,  sys.scheduler$_comb_lw_job lj,
         sys.scheduler$_job ljp
    WHERE lj.program_oid = ljp.obj#(+) and
       bitand(lj.flags, 137438953472) = 0 and
       la.oid = lj.obj#) lt,
   scheduler$_lwjob_obj lo
  WHERE lt.job_oid = lo.obj# AND lo.userid = USERENV('SCHEMAID')
    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(+)

View Text - HTML Formatted

SELECT 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 SYS.SCHEDULER$_PROGRAM_ARGUMENT B
, OBJ$ T_O
, USER$ T_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
, OBJ$ O
WHERE T.JOB_OID = O.OBJ#
AND O.OWNER# = USERENV('SCHEMAID')
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(+) UNION ALL SELECT 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 SYS.SCHEDULER$_PROGRAM_ARGUMENT LB
, OBJ$ LT_O
, USER$ LT_U
, (SELECT LA.OID JOB_OID
, LA.POSITION POSITION
, DECODE(BITAND(LJ.FLAGS
, 8589934592)
, 0
, LJ.PROGRAM_OID
, LJP.PROGRAM_OID) PROGRAM_OID
, LA.VALUE VALUE
FROM SYS.SCHEDULER$_JOB_ARGUMENT LA
, SYS.SCHEDULER$_COMB_LW_JOB LJ
, SYS.SCHEDULER$_JOB LJP
WHERE LJ.PROGRAM_OID = LJP.OBJ#(+) AND BITAND(LJ.FLAGS
, 137438953472) = 0 AND LA.OID = LJ.OBJ#) LT
, SCHEDULER$_LWJOB_OBJ LO
WHERE LT.JOB_OID = LO.OBJ#
AND LO.USERID = USERENV('SCHEMAID')
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(+)