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;
Select TABLE_NAME, COLUMN_NAME, DATA_DEFAULT from DBA_TAB_COLUMNS where TABLE_NAME = 'TABLE_NAME'; Replace the Table_Name for which you want to see the default column data.
From the 'Connections' tab, right-click on a table in your database, and click 'Edit' from the context menu. Select the column that you wish to set a default value, then from the 'Column Properties' panel you can set the default value by populating the 'Default:' field in the form.
For numeric types, the default is 0 , with the exception that for integer or floating-point types declared with the AUTO_INCREMENT attribute, the default is the next value in the sequence.
A VARCHAR2 column can store a value that ranges from 1 to 4000 bytes. It means that for a single-byte character set, you can store up to 4000 characters in a VARCHAR2 column. By default, Oracle uses BYTE if you don't explicitly specify BYTE or CHAR after the max_size .
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With