CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsql
Jesús Zazueta
Jesús Zazueta

Reputation: 1192

Oracle stored procedure call with provided/default parameters

Suppose I have the following Oracle stored procedure signature:

procedure my_procedure(parameter_1 varchar2, parameter_2 varchar2 default 'someDefaultValue', parameter_3 varchar2 default 'anotherDefaultValue', parameter_4 varchar2 default 'oneMoreDefaultValue')

If I make the following call to this stored procedure:

BEGIN my_procedure('one','two'); END;

I'm pretty sure that, inside the stored procedure, parameter_1 will hold the value one.

However, what variable will hold the value two?

Thanks!

Upvotes: 1

Views: 6777

Answers (1)

Jacob
Jacob

Reputation: 14741

As you are passing a value for second parameter as two, second parameter will take it as a passing value. See the following example

CREATE OR REPLACE PROCEDURE my_procedure (
    parameter_1     VARCHAR2,
    parameter_2     VARCHAR2 DEFAULT 'someDefaultValue',
    parameter_3     VARCHAR2 DEFAULT 'anotherDefaultValue',
    parameter_4     VARCHAR2 DEFAULT 'oneMoreDefaultValue'
)
AS
BEGIN
    DBMS_OUTPUT.put_line ('first param ' || parameter_1);
    DBMS_OUTPUT.put_line ('second param ' || parameter_2);
    DBMS_OUTPUT.put_line ('third param ' || parameter_3);
    DBMS_OUTPUT.put_line ('fourth param ' || parameter_4);
END;

and execute procedure by

SET SERVEROUTPUT ON SIZE 30000;

BEGIN
    my_procedure ('one', 'two');
END;

and output

first param one
second param two
third param anotherDefaultValue
fourth param oneMoreDefaultValue

Upvotes: 3

Related Questions