Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01722 INVALID NUMBER
ORA-01722 INVALID NUMBER [message #314888] Fri, 18 April 2008 03:25 Go to next message
alson
Messages: 41
Registered: October 2007
Location: -
Member

hi, experts

Need your expertise on this wierd problem, I am able to run the below script in my Test with no error but in my production database , it will return ORA-01722 invalid number error

select 'Check for lines in the interface table'
from oe_lines_iface_all soli,
po_requisition_headers porh,
po_system_parameters posp,
po_requisition_lines porl
where soli.order_source_id = TO_CHAR (posp.order_source_id)
AND soli.orig_sys_line_ref = porl.requisition_line_id
AND porl.requisition_line_id = 73757
AND soli.orig_sys_document_ref =porh.requisition_header_id
and porh.requisition_header_id = 27459
AND porh.requisition_header_id =porl.requisition_header_id

the Test database is clone from Production and the data is up to date. I try check on NLS_SESSION_PARAMETERS, settings on both instance are the same, plese check the attached file.
is there any setting that i need to check ?

thank you
please heeelp
ng
Re: ORA-01722 INVALID NUMBER [message #314889 is a reply to message #314888] Fri, 18 April 2008 03:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is NOT an expert question.

It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Rules are described in: OraFAQ Forum Guide
Read them, follow them including what is said about formatting.

As you are an expert, I don't answer the question because you obviously already knows it.

Regards
Michel
Re: ORA-01722 INVALID NUMBER [message #314892 is a reply to message #314888] Fri, 18 April 2008 03:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What are the data types of the following columns:

soli.order_source_id
posp.order_source_id
porl.requisition_line_id
porh.requisition_header_id
Re: ORA-01722 INVALID NUMBER [message #314900 is a reply to message #314888] Fri, 18 April 2008 04:02 Go to previous messageGo to next message
alson
Messages: 41
Registered: October 2007
Location: -
Member

is all number.
Re: ORA-01722 INVALID NUMBER [message #314907 is a reply to message #314888] Fri, 18 April 2008 04:32 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
How about posting the table structures in the required format. Forgot to mention also the explain plan of the query from your Development and Product box.

Regards

Raj

[Updated on: Fri, 18 April 2008 04:36]

Report message to a moderator

Re: ORA-01722 INVALID NUMBER [message #314911 is a reply to message #314907] Fri, 18 April 2008 04:40 Go to previous messageGo to next message
alson
Messages: 41
Registered: October 2007
Location: -
Member

but my Test instance is ok to run the script, and both have the same data structure.
Re: ORA-01722 INVALID NUMBER [message #314913 is a reply to message #314888] Fri, 18 April 2008 04:42 Go to previous messageGo to next message
alson
Messages: 41
Registered: October 2007
Location: -
Member

is there any other setting other than nls_session_parameters that will cause this problem ?

thanks
Re: ORA-01722 INVALID NUMBER [message #314914 is a reply to message #314911] Fri, 18 April 2008 04:42 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
OK, don't post what Raj requested. Just let him guess, why should you have to give him any information he asks for just because you require some assistance, sheesh, the cheek of the guy!
Re: ORA-01722 INVALID NUMBER [message #314916 is a reply to message #314911] Fri, 18 April 2008 04:44 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Are the data between your test instance and Production are the same ?

Regards

Raj
Re: ORA-01722 INVALID NUMBER [message #314919 is a reply to message #314913] Fri, 18 April 2008 04:49 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Why why why why

http://www.orafaq.com/forum/t/101016/94420/

Regards

Raj
Re: ORA-01722 INVALID NUMBER [message #314920 is a reply to message #314888] Fri, 18 April 2008 04:49 Go to previous messageGo to next message
alson
Messages: 41
Registered: October 2007
Location: -
Member

hi, Raj,
i overlook the below post :
"
Forgot to mention also the explain plan of the query from your Development and Product box"

I will chk out for you as soon as possible, this could be the root cause right ?
Re: ORA-01722 INVALID NUMBER [message #314925 is a reply to message #314920] Fri, 18 April 2008 05:08 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Where condition from your query
Quote:

where soli.order_source_id = TO_CHAR (posp.order_source_id)
AND soli.orig_sys_line_ref = porl.requisition_line_id
AND porl.requisition_line_id = 73757
AND soli.orig_sys_document_ref =porh.requisition_header_id
and porh.requisition_header_id = 27459
AND porh.requisition_header_id =porl.requisition_header_id

Question from @JRowbottom
Quote:

What are the data types of the following columns:

soli.order_source_id
posp.order_source_id
porl.requisition_line_id
porh.requisition_header_id

Your comment
Quote:
is all number.


Don't you think your reply is contradictory.
soli.order_source_id = TO_CHAR (posp.order_source_id)

Regards

Raj
Re: ORA-01722 INVALID NUMBER [message #315564 is a reply to message #314925] Mon, 21 April 2008 21:43 Go to previous message
alson
Messages: 41
Registered: October 2007
Location: -
Member

hi, I found that if if remark the following 2 lines in the init.ora file, the problem will be solved, can anyone tell me what is the purpose of below 2 lines, is there any other solution without remark the 2 lines?

# optimizer_index_caching=10
# optimizer_index_cost_adj=90

Fyi, my optimizer_mode_force = true

_optimizer_mode_force = true
Previous Topic: Column to rows
Next Topic: How to use alias name for a column in sql query
Goto Forum:
  


Current Time: Mon Dec 05 04:39:10 CST 2016

Total time taken to generate the page: 0.06995 seconds