Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Overloading of procedures in package with default parameters.

774612Mar 5 2012 — edited Mar 7 2012
I created one package as

CASE 1:
--------------------------------------------------------------------------------------------------------------------
create or replace package PACK_DEFAULT is
-- Public procedure declarations
procedure proc_test(p_name varchar2 default 'SCOTT');
procedure proc_test(p_name varchar2 default 'TOM',p_salary number default 1000);

end PACK_DEFAULT;

create or replace package body PACK_DEFAULT is

-- procedure implementations
procedure proc_test (p_name varchar2 default 'SCOTT' )
is
begin
dbms_output.put_line('1st procedure executed ' || p_name);
end;

procedure proc_test (p_name varchar2 default 'TOM' ,p_salary number default 1000)
is
begin
dbms_output.put_line('2nd procedure executed '|| p_name ||'& '||p_salary);
end;

end PACK_DEFAULT;
------------------------------------------------------------------------------------------------------------

when i executed procedure as

begin
-- Call the procedure
pack_default.proc_test();
end;

it gave run-time error
pls-00307:too many declarations of 'PROC_TEST' match this call

If i changed in default parameter of number data-type as

CASE 2:

---------------------------------------------------------------------------------------------------------------
create or replace package PACK_DEFAULT is
-- Public procedure declarations
procedure proc_test(p_name varchar2 default 'SCOTT');
procedure proc_test(p_name varchar2 default 'TOM',p_salary number default '1000');

end PACK_DEFAULT;

create or replace package body PACK_DEFAULT is

-- procedure implementations
procedure proc_test (p_name varchar2 default 'SCOTT' )
is
begin
dbms_output.put_line('1st procedure executed ' || p_name);
end;

procedure proc_test (p_name varchar2 default 'TOM' ,p_salary number default '1000')
is
begin
dbms_output.put_line('2nd procedure executed '|| p_name ||'& '||p_salary);
end;

end PACK_DEFAULT;

--------------------------------------------------------------------------------------------------------------------------

then it executed fine and the out put was
1st procedure executed SCOTT
--------------------------------------------------------------------------------------------------------------------------

I have some doubts kindly clarify

1. why second procedure don't gave any run time error as first ?
2. If oracle internal convert the data type of default parameter in number in second case then why is it not working in case first ?
3. Why the 1st procedure is executed not 2nd procedure in case 2nd?

Thanks in advance
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 4 2012
Added on Mar 5 2012
4 comments
1,067 views