I had a bit of e-mail exchange with the Oracle Support. This is an extract fyi
me: I am unable to retrieve default values of stored proc arguments. Bug #393152.Are there any work-arounds? If my only option is to parse USER_SOURCE, are
there any parsers readily available for PL/SQL?
Oracle: This particular issue has been outstanding since 1993. This Bug is considered as an enhancement request.
Development states that it is very difficult for them to fix this bug because of the following:
The default value of arguments can be arbitrary expressions. Furthermore, even
if the default value is a scalar - it can be upto 32K in size for VARCHAR2.
Hence this bug is not yet fixed, There have been requests to provide a workaround to the developers but they mentioned that there is no workaround available presently.
Incase getting the default values is very much necessary for you then you might have to give us strong justification as to why you need this feature, so that I can update the development regarding this.
Me:As far as developers comments regarding a difficulty to implement the fix: Currently Oracle 9i provides an access to default values for the column via view USER_TAB_COLUMNS - DATA_DEFAULT. One would think that all the difficulties that have been mentioned in your developer's response would prevent implementing of this functionality as well. However it is possible to retrieve default value for a column of an arbitrary type and not possible to retrieve default value of the stored proc argument.
Oracle:The bug is closed as a Documentation Bug.
Here is the update extracted from the bug --
"Feature still has not been implemented in 10i (RDBMS_MAIN_SOLARIS_021211).
The default_value column is a long in all current versions (8.1.7 through
The latest 9.2 doc still lists default_value and default_length of
all_arguments as holding default information. Therefore rather than simply marking this as a duplicate bug I'm switching to a doc bug. I'm also cross referencing this bug in bug 183707.
Oracle Reference Manual, ALL_ARGUMENTS section, should be changed so that DEFAULT_VALUE and DEFAULT_LENGTH say something like "Reserved for future use" rather than imply they hold default information. "
Originally posted by andrewst
This is a bug that Oracle apparently can't fix (#393152), so ALL_ARGUMENTS.DEFAULT_VALUE is an unusable column. The only solution is to read ALL_SOURCE and parse the argument list.