Home » SQL & PL/SQL » SQL & PL/SQL » Function Overloading
icon8.gif  Function Overloading [message #346571] Mon, 08 September 2008 23:15 Go to next message
honeysn
Messages: 2
Registered: September 2008
Junior Member
Hi,
I have a overloading function as shown below:
******** package **************************
create or replace package test_load is
function print_it (p_arg in date) return varchar2;
function print_it (p_arg in varchar2) return number;
end;
****************************************************

************* Package Body ************************
create or replace package body test_load
is
function print_it(p_arg in date) return varchar2
is
begin
dbms_output.put_line('Inside date');
return to_char(p_arg,'FmMonth, dd yyyy');
end;

function print_it(p_arg in varchar2) return number
is
begin
dbms_output.put_line('Inside varchar');
return to_number(p_arg,'999,999.00');
end;
end;
************************************************

I tried to invoke the first overloaded function using below commands:
var display_date varchar2(20)
exec :display_date:=test_load.print_it('08-Mar-01');

I am getting the error:
ORA-06502: PL/SQL: numeric or value error

Its because second overloaded function (with input as varchar) is getting called instead of first overloaded function (with input as date).

Please suggest how to make sure first overloaded function (with date input) is called, when pass date as input.

Thanks.
Re: Function Overloading [message #346574 is a reply to message #346571] Mon, 08 September 2008 23:22 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

>Please suggest how to make sure first overloaded function (with date input) is called, when pass date as input.

In your post, you never passed "with date input".
Re: Function Overloading [message #346576 is a reply to message #346571] Mon, 08 September 2008 23:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or more precisely: '08-Mar-01' is a string not a date.

Regards
Michel
icon14.gif  Re: Function Overloading [message #346577 is a reply to message #346571] Mon, 08 September 2008 23:34 Go to previous messageGo to next message
honeysn
Messages: 2
Registered: September 2008
Junior Member
Thanks guys for pointing out my mistake. Now I am able to execute the function properly ... I used following command

exec :display_date:=test_load.print_it(to_date('08-Mar-01'));

Now its considered as a date.

Thanks for your help.

[Updated on: Mon, 08 September 2008 23:46]

Report message to a moderator

Re: Function Overloading [message #346600 is a reply to message #346577] Tue, 09 September 2008 01:44 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Now its considered as a date.

But is wrong in many environments:
SQL> select to_date('08-Mar-01') from dual;
select to_date('08-Mar-01') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Regards
Michel
Previous Topic: SQL Problem
Next Topic: ORA-23401: materialized view "CRAPPASUGS"."MV_CR_T_CONTACTS" does not exist
Goto Forum:
  


Current Time: Wed Dec 07 20:10:12 CST 2016

Total time taken to generate the page: 0.09233 seconds