when you define a package in oracle there is a like a header and then a body.
One must define all parameters in both locations. I want to make one of the parameters optional for the calling program (IBM message broker). Do I have to add the default value in both the header and body definition?
Addionally, can anyone confirm that messagebroker will be able to call the proc with out specifying any value for the parameter with a default?
Thanks!
Update: I notice that I can add the default into the header and not the body, or I can add it into both. I cannot add it into just the body.
What is the differance between adding it to both vs just the header?
Update:
I can do this where I only specify the default in the spec and not the body. Or i can also specify the default in both places. What is the differance?
create or replace
package myPackage is
PROCEDURE myProc (
parm1 IN varchar2,
parm1 IN date,
parm1 IN number default null
);
end myPackage;
create or replace
package body myPackage is
PROCEDURE myProc (
parm1 IN varchar2,
parm1 IN date,
parm1 IN number
) is
...
...
...
end myProc;
end myPackage;
If you want to make a parameter optional, then you must specify a default. I would be surprised if the default value works properly if it isn't in the declaration of the body.
I have gotten in the habit of making all my package spec declarations exact copies of the package body declarations to avoid issues.
EDIT:
As OP points out, it can be in the spec only and it works. If it's in the body but not the spec, an error results:
SQL> CREATE OR REPLACE PACKAGE p AS
2 PROCEDURE prc(p1 VARCHAR2, p2 VARCHAR2);
3 END;
4 /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY p AS
2 PROCEDURE prc(p1 VARCHAR2 DEFAULT 'P1', p2 VARCHAR2)
3 IS
4 BEGIN
5 dbms_output.put_line(p1||','||p2);
6 END;
7 END;
8 /
Warning: Package body created with compilation errors
SQL>
But if in the spec only, all works:
SQL> CREATE OR REPLACE PACKAGE p AS
2 PROCEDURE prc(p1 VARCHAR2 DEFAULT 'P1Dflt', p2 VARCHAR2);
3 END;
4 /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY p AS
2 PROCEDURE prc(p1 VARCHAR2, p2 VARCHAR2)
3 IS
4 BEGIN
5 dbms_output.put_line(p1||','||p2);
6 END;
7 END;
8 /
Package body created
SQL> DECLARE
2 BEGIN
3 p.prc(p2=>'Test');
4 END;
5 /
P1Dflt,Test
PL/SQL procedure successfully completed
SQL>
That said, the answer to the question as to what the difference it, it appears that there is no difference between putting the default in the spec only or in both places - the end result is the same. I would reiterate my belief that you should put it in both places for documentary purposes.