Home » SQL & PL/SQL » SQL & PL/SQL » Accept Command in SQL*PLUS
Accept Command in SQL*PLUS [message #257447] Wed, 08 August 2007 06:58 Go to next message
MEEENAR
Messages: 72
Registered: May 2005
Location: CHENNAI
Member
Hi Friends,

empinfo.sql
accept v_hiredate date 'dd-mon-yy'
select ename ,job,deptno,sal,hiredate from emp where
trunc(hiredate)= TRUNC('&v_hiredate')



If i rum this sql file iam getting error

ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER


is that anthing like that we should not have trunc on both sides of the query
Re: Accept Command in SQL*PLUS [message #257455 is a reply to message #257447] Wed, 08 August 2007 07:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
SQL> accept v_hiredate date 'dd-mon-yy'
SP2-0003: Ill-formed ACCEPT command starting as 'dd-mon-yy'
SQL> select ename ,job,deptno,sal,hiredate from emp where
  2  trunc(hiredate)= TRUNC('&v_hiredate');
Enter value for v_hiredate: 01-jan-07
old   2: trunc(hiredate)= TRUNC('&v_hiredate')
new   2: trunc(hiredate)= TRUNC('01-jan-07')
trunc(hiredate)= TRUNC('01-jan-07')
               *
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER



Did you even notice that the accept threw an error?

Re: Accept Command in SQL*PLUS [message #257460 is a reply to message #257447] Wed, 08 August 2007 07:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
'&v_hiredate' is a string
TRUNC expect a number or a date not a string
-> Oracle assumes it is a string to number conversion and that TRUNC will return a number
-> both side of = are of incompatible type
-> error

Regards
Michel

Re: Accept Command in SQL*PLUS [message #257479 is a reply to message #257447] Wed, 08 August 2007 08:01 Go to previous message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
So please look up the TO_DATE function in the manual to turn your character string into a DATE.
Previous Topic: Joining Query after selecting TOP N
Next Topic: query
Goto Forum:
  


Current Time: Fri Dec 09 11:34:03 CST 2016

Total time taken to generate the page: 0.10852 seconds