 ORA-06592 [message #202589] |
Fri, 10 November 2006 08:14  |
gdottorini
Messages: 23
Registered: November 2006
Location: Napoli
|
Junior Member |
|
|
Hi there,
i need an explanation on stange behaviour of a database functions.
We are trying to migrate an application from oracle 9i Release 9.2.0.3.0 to an Oracle 10g Release 10.1.0.5.0.
There's function in the database that calculate the number of the day inside the week (eg. Monday is 1, Sunday is 7)
This is the code
Case upper(rtrim(to_char(PData, 'DAY')))
When 'LUNEDI' Then Giorno := 1;
When 'MARTEDI' Then Giorno := 2;
When 'MERCOLEDI' Then Giorno := 3;
When 'GIOVEDI' Then Giorno := 4;
When 'VENERDI' Then Giorno := 5;
When 'SABATO' Then Giorno := 6;
When 'DOMENICA' Then Giorno := 7;
When 'MONDAY' Then Giorno := 1;
When 'TUESDAY' Then Giorno := 2;
When 'WEDNESDAY' Then Giorno := 3;
When 'THURSDAY' Then Giorno := 4;
When 'FRIDAY' Then Giorno := 5;
When 'SATURDAY' Then Giorno := 6;
When 'SUNDAY' Then Giorno := 7;
End Case;
If I launch the function in the 10g database I get the ORA-06592.
Please could anyone tell me why? Thanks in advance.
Regards,
Giulio
|
|
|
|
|
|
| Re: ORA-06592 [message #202593 is a reply to message #202592] |
Fri, 10 November 2006 08:35  |
gdottorini
Messages: 23
Registered: November 2006
Location: Napoli
|
Junior Member |
|
|
HI Frank,
you're quite right about the 'D' format, but i have to use the case because i don't know if the database is italian or american.
Infact SUNDAY is the first day for american but is the 7Th for italian.
Bye,
Giulio
|
|
|
|
|
|
| Re: ORA-06592 [message #202663 is a reply to message #202647] |
Fri, 10 November 2006 15:22 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The Error you're getting is caused by the value you've got not appearing in the CASE list. You need to add an ELSE clause to the list.
@Frank's suggestion is a better way of doing it though.
As the wonderful docs show, you can specify NLS parameters (including the NLS_DATE_LANGUAGE) for the to_char function, like this:to_char(sysdate,'day','NLS_DATE_LANGUAGE = <language>') |
|
|
|
| Re: ORA-06592 [message #202880 is a reply to message #202589] |
Mon, 13 November 2006 02:46 |
gdottorini
Messages: 23
Registered: November 2006
Location: Napoli
|
Junior Member |
|
|
Hi,
i've used the nls parameter. Before id didn't function because i've written in a wrong way the italian days.
In fact, for example, it's not Lunedi but Luned́.
Many thanks to Frank and JRowbottom.
Best Regards,
Giulio
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
