Parameter default values, PL-SQL Programming

Parameter Default Values

As the illustration below shows, you can initialize the IN parameters to the default values.

In that way, you can pass various numbers of actual parameters to a subprogram, accommodating or overriding the default values as you please. Furthermore, you can add new proper parameters without having to change every call to the subprogram.

PROCEDURE create_dept (

new_dname CHAR DEFAULT 'TEMP',




VALUES (deptno_seq.NEXTVAL, new_dname, new_loc);



If an actual parameter is not passed, then the default value of its corresponding formal

parameter is used. Consider the calls below to create_dept:



create_dept('MARKETING', 'NEW YORK');

The first call passes no actual parameters; therefore both the default values are used. The second call passes only an actual parameter; therefore the default value for new_loc is used.

The third call passes the two actual parameters, so neither of the default value is used. Typically, you can use the positional notation to override the default values of proper parameters. Though, you cannot skip a formal parameter by leaving out its actual parameter. For illustration, the call below incorrectly relates the actual parameter 'NEW YORK' with the formal parameter new_dname:

create_dept('NEW YORK'); -- incorrect

You cannot answer the problem by leaving a placeholder for the actual parameter. For illustration below, the call is illegal:

create_dept(, 'NEW YORK'); -- illegal

In such cases, you must use named notation, which is as shown below:

create_dept(new_loc => 'NEW YORK');

You also not assign a null to an uninitialized formal parameter by leaving out its real parameter. For illustration, given the declaration


FUNCTION gross_pay (

emp_id IN NUMBER,

st_hours IN NUMBER DEFAULT 40,





The function call below does not assign a null to ot_hours:

IF gross_pay(emp_num) > max_pay THEN ... - illegal

Rather, you should pass the null explicitly, as in

IF gross_pay(emp_num, ot_hour => NULL) > max_pay THEN ...

Or you can initialize ot_hours to NULL, which is as shown:


Finally, if creating a stored subprogram, you cannot use the host variables in the DEFAULT clause. The SQL Plus below illustrates causes a bad bind variable error as at the time of creation, num is merely a placeholder whose value may change:


SQL> CREATE FUNCTION gross_pay (emp_id IN NUMBER DEFAULT :num, ...

Posted Date: 10/5/2012 5:35:34 AM | Location : United States

Related Discussions:- Parameter default values, Assignment Help, Ask Question on Parameter default values, Get Answer, Expert's Help, Parameter default values Discussions

Write discussion on Parameter default values
Your posts are moderated
Related Questions
Write SQL queries to solve the following specifications. Include the query AND THE OUTPUT.  A screen dump of the output is acceptable. Show as many rows as you can. A screen dump i

CURRVAL and NEXTVAL The series is a schema object which generates the sequential numbers. Whenever you form a sequence, you can specify its primary value and an increment. T

Create a view named CustomerAddresses that shows the shipping and billing addresses for each customer in the MyGuitarShop database. This view should return these columns from the

EXIT Statement   You can use the EXIT statement to exit a loop. The EXIT statement has 2 forms: the conditional EXIT WHEN and the unconditional EXIT. With the either form, you

Parameter Modes: You do not require to specify a parameter mode for the input bind arguments (those used, for illustration, in the WHERE clause) as the mode defaults to IN. Th

Cursor Variables:   To execute the multi-row query, the Oracle opens an unnamed work region that stores the processing information. You can use an explicit cursor that names

Initializing and Referencing Collections Until you initialize a collection, a nested table or varray is automatically null (i.e. the collection itself is null, not its elements)

In Packages The Forward declarations also group logically related subprograms in the package. The subprogram specifications go in the package specification, & the subprogram b

Authorize and fetch data from Instagram Project Description: Incorporate Instagram feed on mobile site platform: c#, ms sql, jquerymobile, jquery Web admin Author

Varrays versus Nested Tables The Nested tables are differing from varrays in the following ways: 1)  Varrays have a maximum size, while nested tables do not. 2)  Varrays are