spooling an sqlplus query without prompted variable text

1
votes

I have an sql file in which I prompt the user for a variable using & sign and running the query with sqlplus accordingly. It spools the output to a txt file.

select * from ledger_line where order_no=&order_no;

I am not putting the whole script here. When I run this, the text prompting the user with value also displayed just before the output lines. I want only the answerset to be displayed. verify off, feedback off didn't work. Also running the sqlplus in silent mode with -s is no use.

command line:

SQL> @D:/deneme.sql
Connected.
Enter value for order_no: 99

Output txt:

Enter value for order_no: 99
Büro Malzeme Gideri      000001 S 0077030720 81 0077030720         0.90   TRY                                     
TicBrçlr-MG/FG-MMGid     000002 S 0032000103 96 0032000103         0.90 - TRY                             0,90

Any ideas?

1
sqloraclesqlplus

1 Answers

1
votes

Get the value from the user before you start spooling, with the accept command:

accept order_no prompt "Enter order number: "
spool my_file
select * from ledger_line where order_no=&order_no;

The substitution variable is defined and populated by the accept, so it already has a value when you use it the select, so it doesn't prompt again.