OraFAQ Forum: SQL & PL/SQL » error ORA-01481

Home » SQL & PL/SQL » SQL & PL/SQL » error ORA-01481 (Windows XP)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

error ORA-01481 [message #354231]

Fri, 17 October 2008 02:21

ejoeyz_85
Messages: 30
Registered: October 2008

Member

hello everyone...

I am getting the error: "ORA-01481: invalid number format model"
on this statement:

Quote:

select hostname, to_char(&date_today , 'YYYY'), to_char(&date_today , 'WW'), genesis_port, instance_name,
api_name, sum(total_time), max(max_time), min(min_time), (sum(total_time)/sum(count)), sum(count)
from gen_api_performance_daily
where statistics_date between to_char(&date_last_week, 'YYYYMMDD') and to_char(&date_today , 'YYYYMMDD')
group by hostname, to_char(&date_today , 'YYYY'), to_char(&date_today , 'WW'), genesis_port, instance_name, api_name;

i inserted date_today = 20080907 and date_last_week = 20080901

Any ideas why I am getting the error ? how to solve?

Report message to a moderator

Re: error ORA-01481 [message #354233 is a reply to message #354231]

Fri, 17 October 2008 02:26

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Because you would use TO_CHAR to convert a date into a string.
You want to convert your strings into dates, and then sometimes extract part of them back out again, and so you need to rewrite your query like this:

Code: [Select all] [Show/ hide]

select hostname
      ,to_char(to_date(&date_today,'yyyymmdd') , 'YYYY')
      ,to_char(to_date(&date_today,'yyyymmdd') , 'WW')
      ,genesis_port
....
from   gen_api_performance_daily
where  statistics_date between to_date(&date_last_week, 'YYYYMMDD')              
                       and     to_date(&date_today , 'YYYYMMDD')
group by ...

Report message to a moderator

Re: error ORA-01481 [message #354237 is a reply to message #354231]

Fri, 17 October 2008 02:45

ejoeyz_85
Messages: 30
Registered: October 2008

Member

i still got an error..

i have changed the statement like this:

Quote:

select hostname, to_char(to_date(&date_today, 'YYYYMMDD') , 'YYYY'), to_char(to_date(&date_today, 'YYYYMMDD') , 'WW'), genesis_port, instance_name,
api_name, sum(total_time), max(max_time), min(min_time), (sum(total_time)/sum(count)), sum(count)
from gen_api_performance_daily
where statistics_date between to_date(&date_last_week, 'YYYYMMDD') and to_date(&date_today , 'YYYYMMDD')
group by hostname, to_char(&date_today , 'YYYY'), to_char(&date_today , 'WW'), genesis_port, instance_name, api_name;

anything wrong?

Report message to a moderator

Re: error ORA-01481 [message #354238 is a reply to message #354237]

Fri, 17 October 2008 02:50

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

anything wrong?

Quote:

to_date(&date_today, 'YYYYMMDD')
to_char(&date_today...

Either &date_today is a date or a string, so at least one of these is wrong.

Use SQL*Plus and copy and paste your session.
Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Re: error ORA-01481 [message #354239 is a reply to message #354237]

Fri, 17 October 2008 02:53

Frank
Messages: 7901
Registered: March 2000

Senior Member

Use quotes around your &dates

Report message to a moderator

Re: error ORA-01481 [message #354240 is a reply to message #354238]

Fri, 17 October 2008 02:55

ejoeyz_85
Messages: 30
Registered: October 2008

Member

when i inserted &date_today = 20080907, the error occured.. and when i inserted other than that date, it was successful..

what is wrong?

Report message to a moderator

Re: error ORA-01481 [message #354244 is a reply to message #354240]

Fri, 17 October 2008 03:08

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

You didn't change the Group By clause.

Report message to a moderator

Re: error ORA-01481 [message #354246 is a reply to message #354240]

Fri, 17 October 2008 03:10

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

The error you're getting is because when you issue a command like TO_CHAR('20080907','YYYY') Oracle performs an implicit TO_DATE on the string '20080907' in order to change it into a date.
The default format mask that you are using at your site is obviously not YYYYMMDD.
That is why you need to perform an explicit TO_DATE on the strings.

Report message to a moderator

Re: error ORA-01481 [message #354247 is a reply to message #354240]

Fri, 17 October 2008 03:11

Frank
Messages: 7901
Registered: March 2000

Senior Member

Was it really the same error, or maybe a division by zero?

Report message to a moderator

Re: error ORA-01481 [message #354254 is a reply to message #354247]

Fri, 17 October 2008 03:39

ejoeyz_85
Messages: 30
Registered: October 2008

Member

so how to change it? Shocked

Shocked

Report message to a moderator

Re: error ORA-01481 [message #354257 is a reply to message #354254]

Fri, 17 October 2008 04:07

Frank
Messages: 7901
Registered: March 2000

Senior Member

That's not really an answer...

Report message to a moderator

Re: error ORA-01481 [message #354260 is a reply to message #354231]

Fri, 17 October 2008 04:14

ejoeyz_85
Messages: 30
Registered: October 2008

Member

it was really the same error... i just inserted the 20080907 as date_today... error occured then..

Report message to a moderator

Re: error ORA-01481 [message #354261 is a reply to message #354260]

Fri, 17 October 2008 04:15

Frank
Messages: 7901
Registered: March 2000

Senior Member

Show us a copy & paste from a sqlplus session where you execute what you describe, please.

Report message to a moderator

Re: error ORA-01481 [message #354262 is a reply to message #354260]

Fri, 17 October 2008 04:16

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Michel Cadot wrote on Fri, 17 October 2008 09:50

Use SQL*Plus and copy and paste your session.
Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Re: error ORA-01481 [message #354263 is a reply to message #354261]

Fri, 17 October 2008 04:23

ejoeyz_85
Messages: 30
Registered: October 2008

Member

Quote:

select hostname, to_char(to_date(&date_today, 'YYYYMMDD') , 'YYYY'), to_char(to_date(&date_today, 'YYYYMMDD') , 'WW'), genesis_port, instance_name,
api_name, sum(total_time), max(max_time), min(min_time), (sum(total_time)/sum(count)), sum(count)
from gen_api_performance_daily
where statistics_date between to_date(&date_last_week, 'YYYYMMDD') and to_date(&date_today , 'YYYYMMDD')
group by hostname, to_char(&date_today , 'YYYY'), to_char(&date_today , 'WW'), genesis_port, instance_name, api_name;

insert &date_today = 20080907

error : invalid number format model

Report message to a moderator

Re: error ORA-01481 [message #354267 is a reply to message #354263]

Fri, 17 October 2008 04:46

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Michel Cadot wrote on Fri, 17 October 2008 11:16

Michel Cadot wrote on Fri, 17 October 2008 09:50

Use SQL*Plus and copy and paste your session.
Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Re: error ORA-01481 [message #354271 is a reply to message #354263]

Fri, 17 October 2008 05:17

Frank
Messages: 7901
Registered: March 2000

Senior Member

This is not a copy-paste from sqlplus:
- the error is not in the format sqlplus would return it
- there would be more input required from you.

So, please open up sqlplus.
Paste your query
enter the requested items
copy the complete session
come back here
reply to this message
include the contents of your clipboard in [CODE]-tags, not as a QUOTE.

Report message to a moderator

Re: error ORA-01481 [message #354295 is a reply to message #354260]

Fri, 17 October 2008 08:05

joicejohn
Messages: 327
Registered: March 2008
Location: India

Senior Member

@ejoeyz_85,
In addition to the other comments,

ejoeyz_85 wrote on Fri, 17 October 2008 14:44

it was really the same error... i just inserted the 20080907 as date_today... error occured then..

I do hope you didn't see the comment provided by @JRowBottom,

JRowbottom wrote on Fri, 17 October 2008 13:38

You didn't change the Group By clause.

And still you didn't heed his advice:

ejoeyz_85 wrote on Fri, 17 October 2008 14:53

*** Added CODE tags, Extracted only the Group By Clause

Code: [Select all] [Show/ hide]

group by hostname, to_char(&date_today , 'YYYY'), 
to_char(&date_today , 'WW'), genesis_port, 
instance_name, api_name;

Regards,
Jo

Report message to a moderator

Re: error ORA-01481 [message #354296 is a reply to message #354263]

Fri, 17 October 2008 08:09

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

ejoeyz_85 wrote on Fri, 17 October 2008 05:23

insert &date_today = 20080907

What does this mean? Please provide syntactically correct information. You INSERT into a TABLE, not a variable.

Report message to a moderator

Previous Topic:	Delete data
Next Topic:	Use SUBSTR to get 3rd position with _ delimeter

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 21 00:22:21 CDT 2025