Oracle Apps Notes

A collection of my random notes, primarily on Oracle Apps

Monthly Archives: February 2016

Default parameter values in package subprograms

For a procedure (or function) in a package, you can assign default values only in the package specifications without assigning them in the package body:


SQL> CREATE OR REPLACE PACKAGE test_default_pkg AS
PROCEDURE set_global (p_num IN NUMBER DEFAULT 1);
END;
/

Package TEST_DEFAULT_PKG compiled

SQL> SHOW ERRORS;
No errors.
SQL> CREATE OR REPLACE PACKAGE BODY test_default_pkg AS
PROCEDURE set_global (p_num IN NUMBER) AS
BEGIN
NULL;
END;
END;
/

Package body TEST_DEFAULT_PKG compiled

SQL> SHOW ERRORS;
No errors.

The reverse is not allowed though:


SQL> CREATE OR REPLACE PACKAGE test_default_pkg AS
PROCEDURE set_global (p_num IN NUMBER);
END;
/

Package TEST_DEFAULT_PKG compiled

SQL> SHOW ERRORS;
No errors.
SQL> CREATE OR REPLACE PACKAGE BODY test_default_pkg AS
PROCEDURE set_global (p_num IN NUMBER DEFAULT 1) AS
BEGIN
NULL;
END;
END;
/

Package body TEST_DEFAULT_PKG compiled

Errors: check compiler log
SQL> SHOW ERRORS;
Errors for PACKAGE BODY HR.TEST_DEFAULT_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------------------
2/25 PLS-00593: default value of parameter "P_NUM" in body must match that of spec

Also, if the default value is specified in both the package specification and package body, they must match:


SQL> CREATE OR REPLACE PACKAGE test_default_pkg AS
PROCEDURE set_global (p_num IN NUMBER DEFAULT 1);
END;
/

Package TEST_DEFAULT_PKG compiled

SQL> SHOW ERRORS;
No errors.
SQL> CREATE OR REPLACE PACKAGE BODY test_default_pkg AS
PROCEDURE set_global (p_num IN NUMBER DEFAULT NULL) AS
BEGIN
NULL;
END;
END;
/

Package body TEST_DEFAULT_PKG compiled

Errors: check compiler log
SQL> SHOW ERRORS;
Errors for PACKAGE BODY HR.TEST_DEFAULT_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------------------
2/25 PLS-00593: default value of parameter "P_NUM" in body must match that of spec