CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldevelopersql-tuning
Jimenemex
Jimenemex

Reputation: 3166

Bind Dates in SQL Tuning advisor

I'm trying to bind a date to a variable using Oracle SQL Developers SQL Tuning advisor, but it recognizes it as a VARCHAR2 and not a DATE. Is there a way to specify the data type of the parameters you bind?

SQL:

SELECT * FROM Actv
WHERE ActvId = :in_UserGrpCds AND ActvTime >= :in_FromDate AND ActvTime < :in_ThruDate
OFFSET :in_CurrRecordOffset FETCH NEXT :in_NextRecordOffset ROWS ONLY;

Image

The in_ThruDate get's bound as a VARCHAR2 and it can't run the tune.

Bind Variables :
 1 -  (NUMBER):1091
 2 -  (NUMBER):0
 3 -  (VARCHAR2):07-21-2018
 4 -  (NUMBER):50000
 5 -  (VARCHAR2):08-21-2018

-------------------------------------------------------------------------------
ERRORS SECTION
-------------------------------------------------------------------------------
- ORA-00932: inconsistent datatypes: expected DATE got NUMBER

-------------------------------------------------------------------------------

Upvotes: 0

Views: 382

Answers (1)

Kris Rice
Kris Rice

Reputation: 3410

We only bind strings/numbers/nulls so adjust your SQL to include the to_date:

SELECT * FROM Actv
WHERE ActvId = :in_UserGrpCds 
AND ActvTime >= to_date(:in_FromDate ,'MM-DD-YYYY')
AND ActvTime < to_date(:in_ThruDate ,'MM-DD-YYYY')
OFFSET :in_CurrRecordOffset FETCH NEXT :in_NextRecordOffset ROWS ONLY;

Upvotes: 4

Related Questions