SQL function with multiple default parameters

Question

I would like to know, if there is any possible way in SQL to distinguish which one of default parameters was set when function was called? F.e. if there is function:

CREATE OR REPLACE FUNCTION someFunction (
ID NUMBER,
showNames VARCHAR2 DEFAULT 'N',
showAddress VARCHAR2 DEFAULT 'N')

and it will be called like that:

someFunction(123, 'Y')

is there any way to distinguish which parameter was set by 'Y'? Or if I need to set showAddress to 'Y' do I have to call function like that:

someFunction(123, 'N', 'Y')

I'm tagging this question "oracle" because the use of varchar2 suggests Oracle. — Gordon Linoff
– Gordon Linoff, Commented May 5, 2014 at 10:46

Rahul · Accepted Answer · 2014-05-05 10:56:17Z

4

if you use Oracle database you could specify exact param name:

   someFunction(123, showAddress =>'Y')

edited May 5, 2014 at 10:56

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

answered May 5, 2014 at 10:46

user3278460

Sign up to request clarification or add additional context in comments.

Comments

Rahul · Accepted Answer · 2014-05-05 10:51:51Z

1

Since this is tagged as Oracle; you can use named parameter like formal => actual. So in your case, you can say someFunction(123, shownames => 'Y') in which case ID and shownames will be set; whereas showaddress will have default value.

Otherwise, it's all positional parameter and hence calling someFunction(123, 'Y') will set ID and then shownames based on position of the parameters.

See more here

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/subprograms.htm#LNPLS00825

answered May 5, 2014 at 10:51

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Comments

user3603927 · Accepted Answer · 2014-05-06 14:01:34Z

0

After trying this method:

 someFunction(123, showAddress =>'Y')

I've got exception message: ORA-00907: missing right parentesis. This problem exist only when I am using showAddress =>'Y' - when I use just someFunction(123) everything works fine. Can it be caused by using old ORACLE version? (I am using 10.2 at the moment).

answered May 6, 2014 at 14:01

user3603927

133 bronze badges

Collectives™ on Stack Overflow

SQL function with multiple default parameters

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related