Home » SQL & PL/SQL » Client Tools » Toad related query
Toad related query [message #352471] Tue, 07 October 2008 22:00 Go to next message
sg52
Messages: 7
Registered: October 2008
Junior Member
Hi,

I am using oracle 10g release 1.
i am using toad for development process.
I encountered a problem while using substitution variable.
Please look at the code:

SELECT DISTINCT T1.ACCT
,T1.CLS_DATE
,T1.STATUS
FROM ACCOUNT T1, MESSAGE E9
WHERE T1.ACCT = E9.ACCT
AND T1.OPN_D >= TO_DATE('&1') - 60
AND T1.CLS_DATE BETWEEN TRUNC(TO_DATE('&1'),'D') - 1 AND TO_DATE('&1')
AND E9.E99_MSG_C IN ('ABS,'ASD','SDR')

This code runs perfect in SQL*Plus
but when run in TOAD it gives an error:

ORA-01858 a non-numeric character was found where a numeric was expected

I think its due to use of &1 in to_Date()
but why it has problem in TOAD when it works in SQL*Plus.
Is their a specific way to do this in TOAD while using this??

Also, specifying format with to_Date() does not help solving the problem.

Kindly help me on this.

Sandy
Re: Toad related query [message #352489 is a reply to message #352471] Wed, 08 October 2008 01:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Show us with a copy-paste from your sqlplus session how this works in sqlplus..
Re: Toad related query [message #352633 is a reply to message #352471] Wed, 08 October 2008 12:13 Go to previous messageGo to next message
sg52
Messages: 7
Registered: October 2008
Junior Member
Kindly see the attached file.

This is the exact query i am executing in TOAD, but its giving me error:

ORA-01858 a non-numeric character was found where a numeric was expected

While it is running fine in SQL*Plus.

I dont know why is this so.

Can you help me on this?
  • Attachment: query.txt
    (Size: 1.38KB, Downloaded 227 times)
Re: Toad related query [message #352634 is a reply to message #352633] Wed, 08 October 2008 12:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
quoting Ana (anacedent):
Those who live by the GUI, die by the GUI.

Regards
Michel
Re: Toad related query [message #353594 is a reply to message #352633] Tue, 14 October 2008 05:10 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Looks like the default date format of your TOAD differs from the one in sqlplus.
Use an explicit format mask. TO_DATE should be used with two parameters.
Oh, and next time please copy-paste your query IN your post. Saves me the trouble of downloading and opening your attachment.

[Updated on: Tue, 14 October 2008 05:11]

Report message to a moderator

Previous Topic: Toad question
Next Topic: PLSQL Developer not working with oracle10g (merged)
Goto Forum:
  


Current Time: Fri Dec 09 04:17:50 CST 2016

Total time taken to generate the page: 0.08951 seconds