How to declare a date variable in sqlplus to pass as bind variable?
12,002
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.
Author by
nayakam
Refer : Writing The Perfect Question Stack Overflow Question Checklist
Updated on July 18, 2022Comments
-
nayakam almost 2 years
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?