CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
Art
Art

Reputation: 5792

How to select default values for stored proc arguments?

Facing a problem getting the default values of the procedure parameters:

CREATE PROCEDURE foo(p_arg VARCHAR2:= 'FOO')
IS
BEGIN
 NULL;
END;
/

SELECT argument_name, default_value
  FROM sys.all_arguments
 WHERE object_name = 'FOO';

 ARGUMENT_NAME | DEFAULT_VALUE
 ------------------------------
 P_ARG         |  (Memo)

The default values I set for the procedure parameters are not displayed in the output. All I see is (Memo) in the default_value column. The Oracle documentation does not help as this feature is "Reserved for future use": http://docs.oracle.com/cd/E16655_01/server.121/e17615/refrn20015.htm#i1573843

Is there another way to read default values of the procedure parameters please?

Upvotes: 2

Views: 545

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27251

Unfortunately the default_value column of *_arguments data dictionary views never gets populated. Oracle, for some reason, reserves it for future use for a quite sometime. You can see that an argument has or has not a default value assigned to it(column defaulted), but crucial information about actual default value is missing. To some extent it considered to be a documentation bug. Worth to note that the dbms_describe package does not provide information on actual default values of arguments either. So it leaves you with one option to retrieve default values of arguments - stored procedure source code parsing, that can be obtained by querying *_source views.

Upvotes: 2

Related Questions