Extra output lines when calling SQL scripts from bacth file

Question

I have a batch file where I am passing a value to SQL and then executing the SQL query. I am getting desired output but additionally few more lines also appearing in the output.

Batch File

set /p hostname="Enter hostname:"
echo %hostname%
set arg1=
set arg1=%arg1: =%
set COMMAND=sqlplus.exe -s %arg1% 
%COMMAND% @C:UsersinfodbaDesktopest.sql %hostname%

SQL file

select ID from Tablename where pid = '&1';

Here I am passing hostname to query as "&1" and I get this output:

old   1: select ID  from Tablename where pid='&1'     //extra lines
new   1: select ID from Tablename  where pid='SQY5760dznicWC'   //extra lines

SQY5760dznicWC/08_01_0_pdf_23o0akf8jwgy6.pdf    //desired output. 

but if I remove the &1 and pass the hostname directly, I am not getting those extra lines.

Please help me figure out how to remove those extra lines.

question from:https://stackoverflow.com/questions/65645041/extra-output-lines-when-calling-sql-scripts-from-bacth-file

Answer 1

You can suppress the variable substitution line by setting VERIFY to OFF using following command:

SET VERIFY OFF

You need to write this command in your sql script.