How to use date variable in sql developer's "Enter Binds" dialog?

Question

I am trying to run a query from sql developer and query has variables (:var). I am having problem with the date variables.

I used all the possible combinations to format date using to_date() function. Every time getting below exception:

ORA-00932: inconsistent datatypes: expected DATE got NUMBER
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:

Sorry can't post image here

okay so my query will be something like this select first_name, last_name, dob, org from emp where dob > :highDate; when you run this query in sql developer you will see the "Enter Binds" pop to enter the value for variable :highDate. — vermap
– vermap, Commented Mar 28, 2014 at 16:38
Also, what is the datatype of DOB? DATE? Finally, what do you have NLS_DATE_FORMAT set to? — Mark J. Bobak
– Mark J. Bobak, Commented Mar 28, 2014 at 16:43
yes dob has date datatype and the value i am passing is to_date('20-JAN-2010', 'DD-MM-YYYY') — vermap
– vermap, Commented Mar 28, 2014 at 16:44

Mark J. Bobak · Accepted Answer · 2016-11-26 20:56:06Z

14

Try changing your query to be:

select first_name,
       last_name,
       dob,
       org
  from emp
 where dob > to_date(:highDate,'DD-MON-YYYY');

then when prompted, enter '20-JAN-2010'.

edited Nov 26, 2016 at 20:56

answered Mar 28, 2014 at 16:59

Mark J. Bobak

14.5k6 gold badges45 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

vermap Over a year ago

thks Mark, is it mean we can't pass a function as variable?

vermap Over a year ago

as my query is too big make all these changes isn't there any otehr way

Mark J. Bobak Over a year ago

The issue is that it's a bind variable. If you use a bind variable, it's a placeholder for a variable of a given type. You can't pass an arbitrary string containing a function, no. Consider that the query is parsed with the bind variable as a placeholder for a specific value of a given datatype. If you can't re-write the query, then try setting the NLS_DATE_FORMAT='DD-MON-YYYY' in the session, then executing the original query, and then entering '20-JAN-2010'. In this case, Oracle should use the value of NLS_DATE_FORMAT and implicitly convert the string to a date.

Amos M. Carpenter Over a year ago

@MarkJ.Bobak: I think you meant 'dd-mon-yyyy' in your query (to match the format of the value to enter into the prompt).

Mark J. Bobak Over a year ago

Corrected. Thanks!

|

Blza Box · Accepted Answer · 2018-07-16 11:31:42Z

8

Just copying the answer from Oracle Community forum:

You should be able to enter dates which conform to your NLS_DATE_FORMAT setting.

e.g. If NLS_DATE_FORMAT is DD-MON-YYYY, you can enter 24-jan-2011 for today's date.

Worked for me.

answered Jul 16, 2018 at 11:31

Blza Box

3453 silver badges6 bronze badges

2 Comments

Marmite Bomber Over a year ago

This is the correct answer, which works without modifying the query. Check the current NLS formats with select * from NLS_SESSION_PARAMETERS. The same is valid for entering timestamps using NLS_TIMESTAMP_FORMAT

Johan Boulé Dec 10, 2024 at 15:45

Unfortunately this won't always work. For example, if the bind variable is passed to a to_char() function, this only works if the variable really is of type date, as no implicit conversion can happen. So, unfortunately, it seems SQL Developer is lacking a proper implementation to specify the type and do the proper input. A shame for a product that's been developed for so long!

tbone · Accepted Answer · 2014-03-28 16:45:05Z

4

Try using a substitution variable. For example:

select (&var - 1) from dual;

sql developer will ask you to enter a substitution variable value, which you can use a date value (such as sysdate or to_date('20140328', 'YYYYMMDD') or whatever date you wish).

answered Mar 28, 2014 at 16:45

tbone

15.5k3 gold badges37 silver badges42 bronze badges

4 Comments

vermap Over a year ago

I don't want to change the way.. as i am copying my query from a property file and want to run that on sql developer as the query is too big to make the changes.

tbone Over a year ago

a simple search/replace ':' with a '&' should work. Ctrl-F in SqlDeveloper will bring up the find/replace wizard

user5683823 Over a year ago

Substitution variables are very different from bind variables in many different ways, so this is not a valid solution. Substitution variables cause hard parse every time. Substitution variables allow SQL injection. Etc.

tbone Over a year ago

The poster just wanted to run a query in SQL Developer, this isn't exposing some big performance or security issue to the world. This is a valid solution for this situation.

atavio · Accepted Answer · 2017-01-11 11:18:44Z

2

Try with:

SELECT TO_DATE(:my_var, 'dd.mm.yyyy') my_date from dual;

and then enter something like 01.02.2017 (without ') as the value of :my_var

answered Jan 11, 2017 at 11:18

atavio

1,32515 silver badges19 bronze badges

Comments

DPA · Accepted Answer · 2015-07-14 13:28:31Z

1

It's not possible. Probably because SQL plus doesn't have it.

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2939749100346967872

answered Jul 14, 2015 at 13:28

DPA

1641 silver badge9 bronze badges

Comments

Neel Alex · Accepted Answer · 2019-08-23 06:12:09Z

0

In the query provide the date in the below format

to_date(:var,'DD-MON-YYYY')

and while executing the query from sql developer, provide the value for var bind parameter as 29-DEC-1995

note that you should not use quotes. Its implicitly done by SQL developer. Happy Coding!

answered Aug 23, 2019 at 6:12

Neel Alex

7437 silver badges10 bronze badges

Collectives™ on Stack Overflow

How to use date variable in sql developer's "Enter Binds" dialog?

6 Answers 6

7 Comments

2 Comments

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

7 Comments

2 Comments

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related