Search code examples
sqlplusspool

How to hide ACCEPT command used for user input in SQL Plus while spooling it in a text file? I have tried "set VERIFY off"

I am new in SQL Plus. I have multiple queries in a SQL file.

Some queries have variables so whenever I use ACCEPT for that, the output file gets spooled including the command and input I entered. Something like that:

SP2-0003: Ill-formed ACCEPT command starting as ,name char prompt 'Enter name:'

with the old and new statements like:

Enter value for name: 'john diaz'

old 3: (select * from sample where upper(name) = upper(&name)),

new 3: (select * from sample where upper(name) = upper('john diaz'))

How to remove those statements from output file?? Anyone??

Solution 
  • SQL> SET VERIFY OFF

SQL> ACCEPT name CHAR PROMPT 'Enter name::  ' HIDE

Enter name:

SQL> select * from sample where upper(name) = upper(&name);

    This way, it will not show OLD/NEW Value, and also you could enter Value in Hidden Way. No display on what you enter.