Issues When User Input Data In Sql Command

I have a batch files which when i run calls a SQL file. This SQL file prompts user to input data which I store in MYPeriod( this accepts date as input ).the output of SQL file is

Solution 1:

Substitution variables are optionally terminated by a period to separate them from any characters that follow. If you want to use one in the string as well, you have to add that termination explicitly:

spool E:\abc\Test\File1_&MYPeriod..csv

For the two extra lines issue, add set verify off; at the moment it is set to on (by default) so it shows you the old and new value of any substitution variables you use.

---

The only way I know to get the date into the file name is to put it into a substitution variable first:

set termout off
column x_run_dt new_value y_run_dt
selectto_char(sysdate, 'YYYYMMDDHH24MISS') as x_run_dt from dual;
set termout on
spool E:\abc\Test\File1_&MYPeriod._&y_run_dt..csv

The new_value clause lets you create a substitution variable, &y_run_dt in this case, with a value from a queried column, x_run_dt. Wrapping the select that generates that value between set termout clauses hides it from the normal output, when run as a script.