Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Posts
    2

    Question Unanswered: Default values for stored procs arguments

    Hi there

    I am trying to work out default values for stored procedure parameters.
    There is a view USER_ARGUMENTS that has a column DEFAULT_VALUE and I assume that if I have a stored proc like

    PROCEDURE REMOVE_RECORDS
    (
    P_PARAMETER VARCHAR2 DEFAULT 'HELLO'
    )

    there should be a record in this view for parameter P_PARAMETER with the default value of 'HELLO'.

    However none of the rows in USER_ARGUMENTS or ALL_ARGUMENTS to that effect have anything rather than NULL in DEFAULT_VALUE column.

    My question is how to determine a default value for a stored proc argument.

    Thanks a lot

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Default values for stored procs arguments

    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.

  3. #3
    Join Date
    Dec 2002
    Posts
    2

    Thumbs down Re: Default values for stored procs arguments

    Thanks Andrew

    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?
    regards
    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
    10i).

    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.

  4. #4
    Join Date
    Apr 2004
    Posts
    15
    wow, 5 years later and it would appear that there is still no workaround for this!

    cybercraft: Did you ever find a suitable solution?

    Thanks
    Kevin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •