Where to determine the default value in oracle package

Question

when you define a package in oracle, there is a header and then a body.

All parameters must be defined in both places. I want to make one of the parameters optional for the calling program (IBM message broker). Do I need to add a default value to the header and body definition?

In addition, can anyone confirm that messagebroker will be able to call proc without specifying any value for the parameter with the default value?

Thanks!

Update: I notice that I can add a default value to the header and not to the body, or I can add it to both. I can’t add it to the body.

What is the difference between adding it to both just the header?

Update:

I can do this when I specify only the default value in the specification, and not in the body. Or I can also specify a default value in both places. What is the difference?

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; 

Answer 1

If you want to make the parameter optional, you must specify a default value. I would be surprised if the default works correctly if it is not specified in the body declaration.

I'm used to making all package specification declarations exact copies of package body declarations to avoid problems.

EDIT:

As the OP points out, it can only be in the specification, and it works. If it is in the body, but not in the specification, an error occurs:

 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 only in the specification, everything 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> 

However, the answer to the question of what is the difference, it seems that there is no difference between putting the default value in the specification or in both places - the end result is the same. I would like to confirm my conviction that you should put it in both places for documentary purposes.

Answer 2

In a package, you can have default variables / constants in the specification or in the body. Personally, I put them in the body, since I do not need to look at the specification to understand what is happening; I know that the official Oracle does not agree with me. In the body, it should be right under create or replace

I am a little confused by your use of the word parameter, although that means that you pass this to functions / procedures in your package. If you have a global variable defined in the package specification or package, there is no need to pass it anywhere. If you are changing globality, then you are asking for a whole bunch of mess or you will hand it to someone who will follow you in a few years.

If you use it for only one function / procedure, then set it in the declaration to this particular element.

That should help .