Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06592
icon5.gif  ORA-06592 [message #202589] Fri, 10 November 2006 08:14 Go to next message
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 #202592 is a reply to message #202589] Fri, 10 November 2006 08:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Oracle invented to_char(date, 'D') for that...

To see what day-name is returned, select '#'||upper(rtrim(to_char(PData, 'DAY')))||'#'

[Updated on: Fri, 10 November 2006 08:33]

Report message to a moderator

Re: ORA-06592 [message #202593 is a reply to message #202592] Fri, 10 November 2006 08:35 Go to previous messageGo to next message
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 #202647 is a reply to message #202593] Fri, 10 November 2006 12:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
use explicit nls_parameters then!
Re: ORA-06592 [message #202663 is a reply to message #202647] Fri, 10 November 2006 15:22 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: ORA-06592 [message #202884 is a reply to message #202880] Mon, 13 November 2006 03:02 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
gdottorini
... it's not Lunedi but Luned́

Do I need new spectacles? ./fa/450/0/
Re: ORA-06592 [message #202907 is a reply to message #202884] Mon, 13 November 2006 04:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
look for the accent on the i
Re: ORA-06592 [message #202918 is a reply to message #202907] Mon, 13 November 2006 05:08 Go to previous message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Ah! I'd need Hubble the telescope to see that :blind_smiley
Previous Topic: How to get procedure script from dictioinary table
Next Topic: explain error 12203
Goto Forum:
  


Current Time: Thu Dec 08 10:14:33 CST 2016

Total time taken to generate the page: 0.10152 seconds