VIEW: SYS.DBA_XS_NS_TEMPLATES

Source

View Text - Preformatted

select o1.name, n.hschema, n.hpname, n.hfname,
       decode(n.hfname, null, null,
              decode((select 1
                        from sys.all_arguments a
                       where a.owner = n.hschema                -- match schema
                         and ((a.package_name = n.hpname)       -- in a package
                              or ((n.hpname is null)       -- outside a package
                                  and (a.package_name is null)))
                         and a.object_name = n.hfname    -- match function name
                       group by a.overload
                      having sum(decode(a.data_type,              -- Match type
                                        decode(a.position,    -- Match position
                                               0, 'BINARY_INTEGER',
                                               1, 'RAW',
                                               2, 'VARCHAR2',
                                               3, 'VARCHAR2',
                                               4, 'VARCHAR2',
                                               5, 'VARCHAR2',
                                               6, 'BINARY_INTEGER',
                                                  chr(0)),    1,
                                        -1)) = 7
                         and sum(decode(a.in_out,               -- Match in out
                                        decode(a.position,    -- Match position
                                               0, 'OUT',
                                                  'IN'),      1,
                                        -1)) = 7
                     ),
                     1, 'VALID', 'INVALID')),
       o2.name, n.description
  from sys.xs$obj o1, sys.xs$nstmpl n, sys.xs$obj o2
 where o1.id = n.ns# and o2.id = n.acl#

View Text - HTML Formatted

SELECT O1.NAME
, N.HSCHEMA
, N.HPNAME
, N.HFNAME
, DECODE(N.HFNAME
, NULL
, NULL
, DECODE((SELECT 1
FROM SYS.ALL_ARGUMENTS A
WHERE A.OWNER = N.HSCHEMA -- MATCH SCHEMA
AND ((A.PACKAGE_NAME = N.HPNAME) -- IN A PACKAGE OR ((N.HPNAME IS NULL) -- OUTSIDE A PACKAGE
AND (A.PACKAGE_NAME IS NULL)))
AND A.OBJECT_NAME = N.HFNAME -- MATCH FUNCTION NAME GROUP BY A.OVERLOAD HAVING SUM(DECODE(A.DATA_TYPE
, -- MATCH TYPE DECODE(A.POSITION
, -- MATCH POSITION 0
, 'BINARY_INTEGER'
, 1
, 'RAW'
, 2
, 'VARCHAR2'
, 3
, 'VARCHAR2'
, 4
, 'VARCHAR2'
, 5
, 'VARCHAR2'
, 6
, 'BINARY_INTEGER'
, CHR(0))
, 1
, -1)) = 7
AND SUM(DECODE(A.IN_OUT
, -- MATCH IN OUT DECODE(A.POSITION
, -- MATCH POSITION 0
, 'OUT'
, 'IN')
, 1
, -1)) = 7 )
, 1
, 'VALID'
, 'INVALID'))
, O2.NAME
, N.DESCRIPTION
FROM SYS.XS$OBJ O1
, SYS.XS$NSTMPL N
, SYS.XS$OBJ O2
WHERE O1.ID = N.NS#
AND O2.ID = N.ACL#