Rem  Copyright (c) Oracle Corporation 2006 - 2008. All Rights Reserved.
Rem
Rem    NAME
Rem      create_dba_arguments_view.sql
Rem
Rem    DESCRIPTION
Rem      Local view to take the place of sys.dba_arguments in database versions earlier than 10.2 which do not have sys.dba_arguments
Rem      This must be created in the APEX_xxxxxx schema, e.g., APEX_040000
Rem
Rem    MODIFIED   (MM/DD/YYYY)
Rem    sspadafo    11/04/2008 - Created

set define '^'

prompt creating dba_arguments view

create view dba_arguments
as
select
u.name OWNER,
nvl(a.procedure$,o.name) OBJECT_NAME,
decode(a.procedure$,null,null, o.name) PACKAGE_NAME,
o.obj# OBJECT_ID,
decode(a.overload#,0,null,a.overload#) OVERLOAD,
a.argument ARGUMENT_NAME,
a.position# POSITION,
a.sequence# SEQUENCE,
a.level# DATA_LEVEL,
decode(a.type#,
0, null,
1, decode(a.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
2, decode(a.scale, -127, 'FLOAT', 'NUMBER'),
3, 'NATIVE INTEGER',
8, 'LONG',
9, decode(a.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
11, 'ROWID',
12, 'DATE',
23, 'RAW',
24, 'LONG RAW',
29, 'BINARY_INTEGER',
69, 'ROWID',
96, decode(a.charsetform, 2, 'NCHAR', 'CHAR'),
102, 'REF CURSOR',
104, 'UROWID',
105, 'MLSLABEL',
106, 'MLSLABEL',
110, 'REF',
111, 'REF',
112, decode(a.charsetform, 2, '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') DATA_TYPE,
default$ DEFAULT_VALUE,
deflength DEFAULT_LENGTH,
decode(in_out,null,'IN',1,'OUT',2,'IN/OUT','Undefined') IN_OUT,
length DATA_LENGTH,
precision# DATA_PRECISION,
decode(a.type#, 2, scale, 1, null, 96, null, scale) DATA_SCALE,
radix RADIX,
decode(a.charsetform, 1, 'CHAR_CS',
2, 'NCHAR_CS',
3, NLS_CHARSET_NAME(a.charsetid),
4, 'ARG:'||a.charsetid) CHARACTER_SET_NAME,
a.type_owner TYPE_OWNER,
a.type_name TYPE_NAME,
a.type_subname TYPE_SUBNAME,
a.type_linkname TYPE_LINK,
a.pls_type PLS_TYPE,
decode(a.type#, 1, a.scale, 96, a.scale, 0) CHAR_LENGTH,
decode(a.type#,
1, decode(bitand(a.properties, 128), 128, 'C', 'B'),
96, decode(bitand(a.properties, 128), 128, 'C', 'B'), 0) CHAR_USED
from sys.obj$ o,sys.argument$ a,sys.user$ u
where o.obj# = a.obj#
and o.owner# = u.user#
/