Accept only restricted characters using accept in oracle

Question

i need to accept only Y/N for is_master variable declare below.

For example if i use the below statement in SQL script the user can enter any character.

I want to restrict user to enter only y Y n or N

Accept is_master prompt ' Is the user a Master user (Y/N)  : '

Answer 1

Inspired by an earlier answer from Tony Andrews, if you have an external script called accept_y_n.sql:

accept answer prompt '&question (Y/N): ' format A1

set verify off
set termout off

column script new_value v_script
select case
    when '&answer' in ('Y','N') then ''
    else 'accept_y_n'
    end as script
from dual;

set termout on

@&v_script.

... then from a real script you can loop to get the response in the required format:

define question='Is the user a Master user'
@accept_y_n
define is_master=&answer

select '&is_master' as is_master from dual;

It'll keep prompting until you get a Y or an N, and you can use the response later.

Is the user a Master user (Y/N): A
Is the user a Master user (Y/N): 1
Is the user a Master user (Y/N):
Is the user a Master user (Y/N): Y

I
-
Y

Answer 2

You can use the format keyword after accept to determine the format the user needs to follow as it is defined here. However I do not see a Y/N-like format among format models. With A1 format you restrict the input to be only 1 character.

According to this you can do something with the formats of change keyword but I have no experience with it.