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