Re: Where to get package procedure default value from data dictionary (8.1.7 and 9.2.0.4)

From: Kresimir Fabijanic <kfabijanic_at_optusnet.com.au>
Date: Tue, 03 Feb 2004 22:31:06 +1100
Message-ID: <401F867A.4060803@optusnet.com.au>

Hi Tim

The catalog view you are looking for is ALL_ARGUMENTS:

Name Type Null =20

OWNER VARCHAR2(30) No =20
OBJECT_NAME VARCHAR2(30) Yes =20
PACKAGE_NAME VARCHAR2(30) Yes =20 OBJECT_ID NUMBER No =20
OVERLOAD VARCHAR2(40) Yes =20
ARGUMENT_NAME VARCHAR2(30) Yes =20 POSITION NUMBER No =20
SEQUENCE NUMBER No =20
DATA_LEVEL NUMBER No =20
DATA_TYPE VARCHAR2(30) Yes =20
DEFAULT_VALUE LONG Yes =20
DEFAULT_LENGTH NUMBER Yes =20
IN_OUT VARCHAR2(9) Yes =20

DATA_LENGTH    NUMBER    Yes  =20
DATA_PRECISION    NUMBER    Yes  =20
DATA_SCALE    NUMBER    Yes  =20

RADIX NUMBER Yes =20
CHARACTER_SET_NAME VARCHAR2(44) Yes =20

TYPE_OWNER    VARCHAR2(30)    Yes  =20
TYPE_NAME    VARCHAR2(30)    Yes  =20
TYPE_SUBNAME    VARCHAR2(30)    Yes  =20
TYPE_LINK    VARCHAR2(128)    Yes  =20

PLS_TYPE VARCHAR2(30) Yes =20

for e.g dbms_lock.sleep:

select * from all_arguments
where owner =3D 'SYS'
and package_name =3D 'DBMS_JOB'
and object_name =3D 'SUBMIT'

That view in turn uses the obj$ and argument$ Oracle internal structures:=

select *
from sys.argument$ a, sys.obj$ o
where a.procedure$ =3D 'SUBMIT'
and a.obj# =3D o.obj#
and o.name =3D 'DBMS_JOB'

HTH Kind Regards

Kresimir Fabijanic

Tim Onions wrote:

>I'm writing SQL to summarise packages/procedures/functions right down to=

>parameters used, datatypes and defaults values. However, although I can =
find
>a flag that shows whether or not a parameter has a default value or not =
I
>cannot for the life of me see any view or underlying table that tells yo=
u a
>parameter's default value (argument$ has a column called default$ but th=
is
>is always NULL). So, to rephrase, given this mythical function where wou=
ld I
>go in the data dictionary to find the default value "FRED" of parameter
>P_Addressee.
>
>FUNCTION fn_Format_Address
> ( P_Addressee VARCHAR2 :=3D "FRED"
> , P_Address_Line1 VARCHAR2 :=3D NULL
> , P_Address_Line2 VARCHAR2 :=3D NULL
> , P_Address_Line3 VARCHAR2 :=3D NULL
> , P_Address_Line4 VARCHAR2 :=3D NULL
> , P_Postcode VARCHAR2 :=3D NULL
> , P_Country VARCHAR2 :=3D NULL
> , P_Telephone VARCHAR2 :=3D NULL
> , P_Fax VARCHAR2 :=3D NULL
> , P_Email VARCHAR2 :=3D NULL
> ) RETURN VARCHAR2
> ; =20
>
>Many thanks in advance
>
>T=AC
>----------------------------------------------------------------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>----------------------------------------------------------------
>To unsubscribe send email to: oracle-l-request_at_freelists.org
>put 'unsubscribe' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------
>
> =20
>

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue Feb 03 2004 - 05:31:06 CST