can you help? How do I tell if a procedure parameter is optional?

[Kevin Herring]

Hi
I am an oracle problem which I am hoping you might be able to help with.
In my program at runtime, I get all the procedures in a package which the user can then run. Some of the procedures have parameters which the user enters, but some of these parameters are optional as they have a default value. e.g:

PROCEDURE kevins_test(p_foo in varchar := NULL);

What I want to do is let the user know that the parameter is optional. At first glance it would appear that there is a view for this: USER_ARGUMENTS which has a column called DEFAULT_VALUE, however this is 'reserved for future use'.

According to this post:
http://www.dbforums.com/archive/index.php/t-631096.html
I am going to have to use the ALL_SOURCE table and parse the source code. Sounds like fun.

Then I noticed that in PL/SQL Dev when you expand a package->procedure->parameters, the optional parameters have [] around them!

So my question is, how do your developers determine that a parameter is optional??

Many thanks
Kevin

 

[Marco Kalter]

It uses the Oracle Call Interface, which allows you determine if a parameter has a default value:

OCIAttrGet(ParamHandle,
           OCI_DTYPE_PARAM,
           HasDefault,
           Nil,
           OCI_ATTR_HAS_DEFAULT,
           ErrorHandle);

 

[Kevin Herring]

wow, I forgot all about this!
Thanks Marco, thats brilliant.