How to declare a date variable in sqlplus to pass as bind variable?

Question

I want to pass a date as bind variable in Sqlplus. I used the suggestion provided here but it seems declared bind variable passed as varchar2?

variable my_date varchar2(30)
exec :my_date := '2013-10-01';
select sysdate ,:my_date from dual where sysdate > to_date(:my_date,'yyyy-mm-dd');

 select sql_text, v.sql_id, name, value_string, datatype_string
  from v$sql_bind_capture vbc
  join v$sql v
 using (hash_value)
 where v.sql_id in ('8c5xc95vxc7yr', '6vn08798ax8bw', '61g3km3x621wt');


    SQL_TEXT    SQL_ID  NAME    VALUE_STRING    DATATYPE_STRING
select sysdate ,:my_date from dual where sysdate > to_date(:my_date,:"SYS_B_0") 6vn08798ax8bw   :MY_DATE        VARCHAR2(32)
select sysdate ,:my_date from dual where sysdate > to_date(:my_date,:"SYS_B_0") 6vn08798ax8bw   :MY_DATE        VARCHAR2(32)
select sysdate ,:my_date from dual where sysdate > to_date(:my_date,:"SYS_B_0") 6vn08798ax8bw   :SYS_B_0    yyyy-mm-dd  VARCHAR2(32)

How to pass a date variable as bind parameter in Sqlplus?

update I wrote pl/sql code block to pass date variable as bind variable. Does bind variable type affect execution plan?

David Aldridge · Accepted Answer · 2013-10-23 07:39:38Z

4

As documented, you cannot define a date variable in SQL*Plus.

The bind variable is indeed passed as Varchar2 in your code, which exactly what AskTom says will happen. However the explicit date conversion method converts it to a date in the SQL engine.

answered Oct 23, 2013 at 7:39

David Aldridge

52.5k8 gold badges73 silver badges99 bronze badges

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

Collectives™ on Stack Overflow

How to declare a date variable in sqlplus to pass as bind variable?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related