Home » SQL & PL/SQL » SQL & PL/SQL » Oerloading Poblem (Oracle 9i)
Oerloading Poblem [message #327585] Tue, 17 June 2008 00:09 Go to next message
pointers
Messages: 410
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 #327588 is a reply to message #327585] Tue, 17 June 2008 00:13 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>How to fix this issue.
What issue?
Re: Oerloading Poblem [message #327592 is a reply to message #327585] Tue, 17 June 2008 00:21 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

What is the data type of bind variable :a

kanish
Re: Oerloading Poblem [message #327593 is a reply to message #327585] Tue, 17 June 2008 00:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
'22-mar-08' is a string, so it calls the variant which tries to do a to_number on the input.
Re: Oerloading Poblem [message #327594 is a reply to message #327588] Tue, 17 June 2008 00:25 Go to previous messageGo to next message
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 Go to previous messageGo to next message
pointers
Messages: 410
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 #327606 is a reply to message #327603] Tue, 17 June 2008 00:57 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
How will you convert a String in Date.

Have'nt you heard of TO_DATE function??

Regards,
Rajat
Re: Oerloading Poblem [message #327608 is a reply to message #327603] Tue, 17 June 2008 00:57 Go to previous messageGo to next message
pointers
Messages: 410
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 #327609 is a reply to message #327603] Tue, 17 June 2008 00:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
. I think 'dd-mon-yy' is a default date format

This is a string just a string, NEVER EVER rely on implicit conversion and default format.
See:
SQL> select to_date('22-mar-08') from dual;
select to_date('22-mar-08') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

In addtion implicit conversion from string to number has a higher priority than implicit conversion from string to date.
So in your case, Oracle will try to convert to number and not to date.

Quote:
how to pass a date value.

Using TO_DATE specifying a format.

Regards
Michel
Re: Oerloading Poblem [message #327611 is a reply to message #327608] Tue, 17 June 2008 01:03 Go to previous messageGo to next message
pointers
Messages: 410
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 #327612 is a reply to message #327611] Tue, 17 June 2008 01:04 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Because this is not the default Date format. Laughing
Re: Oerloading Poblem [message #327614 is a reply to message #327611] Tue, 17 June 2008 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
pointers wrote on Tue, 17 June 2008 08:03
Thanks, you are right.

I got the expected answer.Why doesnot oracle take 'dd-mon-yy' as the date input though it is default date format.

Regards,
ps

But you have a function that takes a string as input value and you give a string, why does it have to search to convert it to another datatype?

Regards
Michel

Re: Oerloading Poblem [message #327616 is a reply to message #327611] Tue, 17 June 2008 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please DO NOT modify your post when or after someone answers it, the topic will be incomprehensible for futur readers.

Regards
Michel
Re: Oerloading Poblem [message #327617 is a reply to message #327614] Tue, 17 June 2008 01:12 Go to previous messageGo to next message
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 Go to previous messageGo to next message
pointers
Messages: 410
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 #327627 is a reply to message #327622] Tue, 17 June 2008 01:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
you have a function that takes a string as input value and you give a string, why does it have to search to convert it to another datatype?

Regards
Michel
Re: Oerloading Poblem [message #327630 is a reply to message #327622] Tue, 17 June 2008 01:54 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 #327634 is a reply to message #327631] Tue, 17 June 2008 01:57 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Yes Frank you are right.
I have said it in different context.

Please check my previous post.

Regards,
Rajat Ratewal
Re: Oerloading Poblem [message #327636 is a reply to message #327634] Tue, 17 June 2008 02:01 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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

Previous Topic: Meta data
Next Topic: Index
Goto Forum:
  


Current Time: Sat Dec 10 07:19:59 CST 2016

Total time taken to generate the page: 0.05806 seconds