Oerloading Poblem [message #327585] |
Tue, 17 June 2008 00:09  |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
Hi,
I have been working on functions and procedure overloading.
Here, the function(overloaded)accepts eiether date or character literal and prints the accepted value.
But I am gettion an error.
create or replace package over_load
as
function print_it (v_date date) return varchar2;
function print_it (v_date varchar2) return number;
end;
CREATE OR REPLACE PACKAGE BODY over_load
AS
FUNCTION print_it (v_date DATE)
RETURN VARCHAR2
AS
BEGIN
RETURN TO_CHAR (v_date, 'Month, dd yyyy');
END;
FUNCTION print_it (v_date VARCHAR2)
RETURN NUMBER
AS
BEGIN
RETURN TO_NUMBER (v_date, '99,99');
END;
END;
SQL> begin
2 :a:=over_load.print_it('22-mar-08');
3 end;
4
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "TESTUSER.OVER_LOAD", line 14
ORA-06512: at line 2
Please help me in fixing this issue.
I have created procedure for the same purpose. But when I invoke the procedure for date format, it is printing the number function content rather date function content.
create or replace package over_load
as
procedure print_it (v_date date);
procedure print_it (v_date varchar2);
end;
create or replace package body over_load
as
procedure print_it (v_date date) as
begin
dbms_output.put_line('your date is' ||v_date);
end;
procedure print_it (v_date varchar2) as
begin
dbms_output.put_line('your number is ' ||v_date);
end;
end;
SQL> execute over_load.print_it('22-mar-08');
your number is 22-mar-08
PL/SQL procedure successfully completed.
How to fix this issue.
Regards,
Pointers.
|
|
|
|
|
|
Re: Oerloading Poblem [message #327594 is a reply to message #327588] |
Tue, 17 June 2008 00:25   |
rajatratewal
Messages: 507 Registered: March 2008 Location: INDIA
|
Senior Member |
|
|
begin
:a:=over_load.print_it('22-mar-08');
end;
'22-mar-08' is varchar2 datatype not of Date type.
Now how will it retuen number from '22-mar-2008'
RETURN TO_NUMBER (v_date, '99,99')
Regards,
Rajat Ratewal
|
|
|
Re: Oerloading Poblem [message #327603 is a reply to message #327594] |
Tue, 17 June 2008 00:50   |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
@anacedent
I am facing problem with both the function and procedures. May be you have seen the succesfull message at the last line of my earlier post, but that is the wrong expected answer.
@mm_kanish05
the data type is date
I am trying to pass a date value. so expected a date value, so declared a date variable.
declare
b date;
begin
b:=over_load_fun.print_it('22-mar-08');
end;
@Frank
ok, then would you explain me,how to pass a date value. I think 'dd-mon-yy' is a default date format, Isn't it?
@rajatratewal
I have just used the same variable name (v_date) as a parameter in both the functions.
..............
I know '22-mar-08' is a string then how to pass a date value.
Regards,
Ps.
|
|
|
|
Re: Oerloading Poblem [message #327608 is a reply to message #327603] |
Tue, 17 June 2008 00:57   |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
I changed the variable type in the calling block.
SQL> ed
Wrote file afiedt.buf
1 declare
2 b varchar2(10);
3 begin
4 b:=over_load_fun_a.print_it('22-mar-08');
5* end;
SQL> /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "TESTUSER.OVER_LOAD_FUN_A", line 14
ORA-06512: at line 4
|
|
|
|
Re: Oerloading Poblem [message #327611 is a reply to message #327608] |
Tue, 17 June 2008 01:03   |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
Thank you all, you are right.
I got the expected answer.
I have used
declare
b varchar2(20);
begin
b:=over_load_fun_a.print_it(to_date('22-mar-08','dd-mon-yy'));
dbms_output.put_line(b);
end;
output:
SQL> /
March , 22 2008
PL/SQL procedure successfully completed.
Regards,
ps
[Updated on: Tue, 17 June 2008 01:07] Report message to a moderator
|
|
|
|
|
|
Re: Oerloading Poblem [message #327617 is a reply to message #327614] |
Tue, 17 June 2008 01:12   |
rajatratewal
Messages: 507 Registered: March 2008 Location: INDIA
|
Senior Member |
|
|
select value
FROM NLS_SESSION_PARAMETERS
WHERE PARAMETER = 'NLS_DATE_FORMAT'
Check this value.If you pass value in this format it will be
recognized as Default date format.
|
|
|
Re: Oerloading Poblem [message #327622 is a reply to message #327612] |
Tue, 17 June 2008 01:26   |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
@raja
If i remove to_date function from line 4, it is not working.Though my default date format is 'dd-mon-rr'.
SQL> select value
2 FROM NLS_SESSION_PARAMETERS
3 WHERE PARAMETER = 'NLS_DATE_FORMAT';
VALUE
--------------------------------------------------------------------------------
DD-MON-RR
SQL> declare
2 b varchar2(20);
3 begin
4 b:=over_load_fun_a.print_it('22-mar-08');
5 dbms_output.put_line(b);
6 end;
7 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "TESTUSER.OVER_LOAD_FUN_A", line 14
ORA-06512: at line 4
@Michel Cadot
I totally agree with you and don't rely on implicit conversion and all from now onwards.
But,The following is working fine for me.
SQL> select to_date('22-mar-08') from dual;
TO_DATE('22-MAR
---------------
22-MAR-08
How does the above work though the format is not mentioned.
I know the above query should be like
select to_date('22-mar-08','dd-mon-yy') from dual
Even though the format specifier is removed, the query is working fine in the first query. Isn't it just because 'dd-mon-yy' is a default date format. If it so, why does not the removal of to_date function work.?
Regards,
PS
|
|
|
|
Re: Oerloading Poblem [message #327630 is a reply to message #327622] |
Tue, 17 June 2008 01:54   |
rajatratewal
Messages: 507 Registered: March 2008 Location: INDIA
|
Senior Member |
|
|
your Function has Parameter of varchar2 datatype.
It will not convert it to Date implicitly.
If you are doing something like this.it will work.
DECLARE
v DATE;
BEGIN
v:='22-mar-08';
dbms_output.put_line(TO_CHAR(v,'DD/MM/YYYY HH24:MI:SS'));
END;
But not the other way.
Regards,
Rajat Ratewal
|
|
|
Re: Oerloading Poblem [message #327631 is a reply to message #327617] |
Tue, 17 June 2008 01:54   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
rajatratewal wrote on Tue, 17 June 2008 08:12 | select value
FROM NLS_SESSION_PARAMETERS
WHERE PARAMETER = 'NLS_DATE_FORMAT'
Check this value.If you pass value in this format it will be
recognized as Default date format.
|
Wrong wrong wrong.
If you pass in a string, it will treat it as a string.
Only if you have NO procedure accepting a string, it will try to convert it to some other format.
NEVER NEVER NEVER rely on implicit datatype conversions. Trust me, (and everyone else here warning you) you WILL regret if you do.
And by the way, years consist of four digits.
|
|
|
|
Re: Oerloading Poblem [message #327636 is a reply to message #327634] |
Tue, 17 June 2008 02:01   |
rajatratewal
Messages: 507 Registered: March 2008 Location: INDIA
|
Senior Member |
|
|
Frank Wrote On:Tue, 17 June 2008 01:54
Quote: | And by the way, years consist of four digits
|
yes year has for digits but if you pass two it will also be accepted if default date format is DD-MON-RR.
|
|
|
Re: Oerloading Poblem [message #327653 is a reply to message #327636] |
Tue, 17 June 2008 02:39  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
rajatratewal wrote on Tue, 17 June 2008 09:01 | Frank Wrote On:Tue, 17 June 2008 01:54
Quote: | And by the way, years consist of four digits
|
yes year has for digits but if you pass two it will also be accepted if default date format is DD-MON-RR.
|
Yes and sometimes it accepts default format... do you see what I mean?
Regards
Michel
|
|
|