where to define default value in oracle package

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.

---

In the package you can have default variables / constants in either the spec or the body. Personally I put them in the body as I don't really have to look at the spec to work out what's going on; I know official Oracle disagrees with me. In the body this should come straight under the create or replace

I'm a little confused by your use of the word parameter though which implies you're passing this to functions / procedures in your package. If you have a global variable set in a package spec or body there's no need at all to pass it anywhere. If you're altering a global then you're asking for a whole heap of mess or will be gifting one to whoever follows you in a years time.

If you're using it for only one function / procedure then set it in the declaration to that particular element.

This should help.