default values for parameters?

default values for parameters?

Postby kevinherring » Wed 28 Feb 2007 17:13

Hi
I am using the OraclePackage.DescribeProcedure to return a command object. What I would like to know is whether any of the parameters in the command are optional. i.e. when you declare a procedure:
Code: Select all
procedure test(p_param IN STRING := null);


I would like some why to tell that the oracleparameter is optional. I thought the IsNullable property might be it, but it would appear not.

Is this possible?
Thanks
Kevin
kevinherring
 
Posts: 64
Joined: Wed 04 Jan 2006 15:32

Postby Alexey » Thu 01 Mar 2007 13:00

If i get you right, you need to find out what is the default value of the parameter. There is no property for this purpose.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby kevinherring » Thu 01 Mar 2007 13:02

kind of, I'm not actually interested in what the value is, just that it has one, but I am guessing the answer is the same.
Thanks
Kevin
kevinherring
 
Posts: 64
Joined: Wed 04 Jan 2006 15:32

Postby Alexey » Thu 01 Mar 2007 13:13

Unfortunately, we do not provide such feature. You can check this manually using one of the system views.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby kevinherring » Thu 01 Mar 2007 13:14

good idea, thanks.
kevinherring
 
Posts: 64
Joined: Wed 04 Jan 2006 15:32

Postby Alexey » Thu 01 Mar 2007 13:18

Not at all.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby kevinherring » Tue 24 Jul 2007 10:09

I forgot all about this, but I am bringing it back from the dead.....
We use software called PL/SQL Developer, and in it optional parameters are indicated as such. So I emailed them and asked them how they did it:

http://www.allroundautomations.com/ubb/ultimatebb.php?ubb=get_topic;f=3;t=004092

The answer was that they use the following:

Code: Select all
OCIAttrGet(ParamHandle,           
           OCI_DTYPE_PARAM,           
           HasDefault,           
           Nil,           
           OCI_ATTR_HAS_DEFAULT,           
           ErrorHandle);



Do the Corelab drivers allow me to access this in any way? Could this be an enhancement suggestion if not?

Any other suggestions would be appreciated also....
kevinherring
 
Posts: 64
Joined: Wed 04 Jan 2006 15:32

Postby Alexey » Tue 24 Jul 2007 11:03

We will consider the possibility of providing needed functionality to you.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby kevinherring » Tue 24 Jul 2007 11:04

Thanks Alexey
kevinherring
 
Posts: 64
Joined: Wed 04 Jan 2006 15:32

Postby Alexey » Wed 25 Jul 2007 12:42

We have analyzed OCIAttrGet function and found out that it may be used only when describing parameters. If parameters are set up by a user or if a plain query is called (but not a stored proc), then HasDefault property of OracleParameter will be undefined. In other data providers such functionality is also absent.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby kevinherring » Wed 25 Jul 2007 12:52

Thanks Alexey, but I'm not quite sure I understand your reply.

Are you saying there is no way this function can be used, or just that there are only specific circumstances where it can be used?
kevinherring
 
Posts: 64
Joined: Wed 04 Jan 2006 15:32

Postby Alexey » Wed 25 Jul 2007 13:16

This function might be used only for procedures when calling DeriveParemeters. For user-created parameters it cannot be called.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby kevinherring » Wed 25 Jul 2007 13:36

ah! I'm with you.

Only being able to use it with OracleCommandBuilder.DeriveParameters seems pretty reasonable. But you are saying that the feature doesnt exists currently though right?

I am writing some oracle code in the meantime that looks at the all_source view and parses it. I will post if for others when it is done.
kevinherring
 
Posts: 64
Joined: Wed 04 Jan 2006 15:32

Postby Alexey » Wed 25 Jul 2007 13:54

I can't say for sure that there is no better way then parsing.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby Alexey » Wed 25 Jul 2007 14:03

Take a look at sys.dbms_describe.describe_procedure.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Next

Return to dotConnect for Oracle