Home » Other » Training & Certification » PL/SQL function which returns an error if I enter invalid date (merged and renamed by LF)
PL/SQL function which returns an error if I enter invalid date (merged and renamed by LF) [message #263597] Thu, 30 August 2007 08:31 Go to next message
nagaraj42
Messages: 8
Registered: August 2007
Location: chennai
Junior Member
Can anyone please help me in writing the function

I have to write a function in which if I enter the date as input and if it is invalid then it should throw the error.




Regards
Nagaraj
Re: plsql function which gives error if i enter the invalid date [message #263605 is a reply to message #263597] Thu, 30 August 2007 08:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you give a date as input, it can't be an invalid date.

Regards
Michel
Re: plsql function which gives error if i enter the invalid date [message #263607 is a reply to message #263597] Thu, 30 August 2007 08:48 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I believe you should know input value format; if you don't restrict that, it will become an impossible mission. For example, will this function work for (today)
- 30.08.2007
- 30.08.2007.
- 08/30/07
- 30-aug-07
- 2454343 (today as Julian date)
- etc.

Once this decision is made (for example, let's use DD.MM.YYYY as acceptable format), we could think about the next step - deciding whether this value is valid or not. One way to do it could be as follows: try to convert input string into a date; if it is possible, return 'VALID'; if not, TO_DATE function will fail, raise an exception and return 'INVALID'.
CREATE OR REPLACE FUNCTION Is_Date (par_date IN CHAR)
RETURN CHAR
IS
  l_date DATE;
BEGIN
  l_date := TO_DATE(par_date, 'dd.mm.yyyy');
  
  RETURN ('VALID');

EXCEPTION
  WHEN OTHERS THEN
    RETURN ('INVALID');
END;
/

select is_date('30.08.2007') d1, is_date('32.08.2007') d2 from dual;

D1         D2
---------- ----------
VALID      INVALID
Re: plsql function which gives error if i enter the invalid date [message #263614 is a reply to message #263607] Thu, 30 August 2007 09:10 Go to previous messageGo to next message
nagaraj42
Messages: 8
Registered: August 2007
Location: chennai
Junior Member
Thanks a lot for your quick reply..

The query is fine... But what I want is if I enter the invalid date it has to give the Oracle error and not to display whether it is "Valid" or " Invaid "

Can you please help me...
Re: plsql function which gives error if i enter the invalid date [message #263620 is a reply to message #263614] Thu, 30 August 2007 09:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
raise_application_error or remove exception section.

Regards
Michel
Re: plsql function which gives error if i enter the invalid date [message #263624 is a reply to message #263620] Thu, 30 August 2007 09:25 Go to previous messageGo to next message
nagaraj42
Messages: 8
Registered: August 2007
Location: chennai
Junior Member
Thanks a lot Michel ....
Re: plsql function which gives error if i enter the invalid date [message #263629 is a reply to message #263614] Thu, 30 August 2007 09:39 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
nagaraj4
What I want is if I enter the invalid date it has to give the Oracle error

In that case, you don't need a function. Any mis-use of a DATE datatype will result with an error and Oracle will raise an exception. For example:
SQL> select to_date('32.08.2007', 'dd.mm.yyyy') from dual;
select to_date('32.08.2007', 'dd.mm.yyyy') from dual
               *
ERROR at line 1:
ORA-01847: day of month must be between 1 and last day of month


SQL> select to_date('29.02.2007', 'dd.mm.yyyy') from dual;
select to_date('29.02.2007', 'dd.mm.yyyy') from dual
               *
ERROR at line 1:
ORA-01839: date not valid for month specified
So why would you need a FUNCTION to do that? Overkill, in my opinion.
Re: plsql function which gives error if i enter the invalid date [message #263632 is a reply to message #263629] Thu, 30 August 2007 09:44 Go to previous messageGo to next message
nagaraj42
Messages: 8
Registered: August 2007
Location: chennai
Junior Member
That way we can do .... I agree
but I am in learning stage of functions... so i want to write a function.



Regards
Raju
function to raise error [message #263921 is a reply to message #263597] Fri, 31 August 2007 05:11 Go to previous messageGo to next message
nagaraj42
Messages: 8
Registered: August 2007
Location: chennai
Junior Member
Can anyone please tell me how to use the SQLERRM function in the exception to raise an error

The query is

CREATE OR REPLACE FUNCTION BDSDATA.VALID_DATE ( PAR_DATE VARCHAR2)
RETURN VARCHAR2 IS OUT_DATE DATE;
BEGIN
OUT_DATE:= TO_DATE ( PAR_DATE, 'DD/MM/YYYY');
RETURN OUT_DATE ;
EXCEPTION

WHEN OTHERS THEN
raise_application_error(SQLERRM);


END ;
/
Re: function to raise error [message #263933 is a reply to message #263921] Fri, 31 August 2007 05:30 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Have a look at this.

By
Vamsi
Re: function to raise error [message #263939 is a reply to message #263921] Fri, 31 August 2007 05:33 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
RAISE_APPLICATION_ERROR requires two arguments, not only one. Therefore, you might try with RAISE_APPLICATION_ERROR(SQLCODE, SQLERRM) or, even better, remove the whole EXCEPTION hanlder section (as you don't do anything in there what Oracle wouldn't do itself).
Re: function to raise error [message #263964 is a reply to message #263939] Fri, 31 August 2007 06:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Even easier: remove the exception-handler all together.
Re: function to raise error [message #263976 is a reply to message #263939] Fri, 31 August 2007 07:13 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh?
Littlefoot
even better, remove the whole EXCEPTION hanlder section

Re: function to raise error [message #263985 is a reply to message #263976] Fri, 31 August 2007 07:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
oops
Re: function to raise error [message #263994 is a reply to message #263985] Fri, 31 August 2007 07:50 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
./fa/1693/0/
Re: function to raise error [message #263999 is a reply to message #263994] Fri, 31 August 2007 08:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ah?
Michel Cadot wrote on Thu, 30 August 2007 16:15
raise_application_error or remove exception section.

Regards
Michel
Re: function to raise error [message #264035 is a reply to message #263999] Fri, 31 August 2007 09:46 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Buahahahhahahah! LOL!!! I'm an embarrassment to nature.
Re: function to raise error [message #264060 is a reply to message #264035] Fri, 31 August 2007 10:32 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
./fa/1693/0/

Laughing

Michel
Previous Topic: mock questions for SQL and PLSQL
Next Topic: How to improve DBA skills? (renamed by LF)
Goto Forum:
  


Current Time: Fri Dec 09 00:07:22 CST 2016

Total time taken to generate the page: 0.05540 seconds