Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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;
like image 293
kralco626 Avatar asked Aug 31 '11 16:08

kralco626


People also ask

How do I find the default value of a column in Oracle?

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.

How do I set default value in SQL Developer?

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.

What is the default value of number in Oracle?

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.

What is the default value for VARCHAR2 in Oracle?

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 .


1 Answers

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.

like image 101
DCookie Avatar answered Oct 08 '22 20:10

DCookie