Accept only restricted characters using accept in oracle

i need to accept only Y/N for is_master variable declare b开发者_开发百科elow.

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)  : '

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

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.

Doing something like this allows you to check the values and maybe prompt the user as to the validity / invalidity of the input.

PROMPT Enter the value to validate
ACCEPT value PROMPT "Value: "
SELECT 
    DECODE( '&&value', 'Y', 'This input is OK',
               'y', 'This input is OK',
               'N', 'This input is OK',
               'n', 'This input is OK',
                'ERROR This input is invalid') as Result 
FROM DUAL;

However, i must admit that using this check to perform further logic conditionally is something that i find myself to be stumped at as well.

However, my knowledge of scripts is very limited.

Maybe seeing this might strike a chord with someone who has a greater knowledge of variables and how to get the result below into a variable and then use that for further processing.

I like the loop option that Alex gave, but if you want the script to stop if an invalid value is given, you should be able to do something like this (not tested):

WHENEVER SQLERROR EXIT
ACCEPT answer PROMPT 'OK? (Y/N): ' FORMAT A1
BEGIN IF '&answer.' NOT IN ('Y','N','y','n') THEN RAISE VALUE_ERROR; END IF; END;
/
WHENEVER SQLERROR CONTINUE