How To Make A Menu In Sqlplus Or Pl/sql?

I am making this program that will have a menu that gets the user's input and performs a certain script based on his/her choice. Something along the lines of: Please make a select

Solution 1:

Ans 1. Column script is the column in the select query defined by as script, which means script is a column alias.

Ans 2.as script is referring to as a column alias of the select stmt. There is only one column in this select statement. Example SELECT column AS col1 FROM table;. The column NEW_VALUE gets the selected value (which is driven from the case stmt, which in this case is the script name test1.sql, test2.sql, or FinalAssignment.sql) and stores it into v_script.

Ans 3.dual table is a special one-row table present by default in all Oracle database installations. More on dualhere. You can select anything from dual, like select sysdate from dual; or select 'ABCDEF' AS col1 from dual;.

Ans 4. The v_script column will contain the script name as per your selection from the case statement in the select query, i.e. from the menu selection (as discussed in Ans 2.). Once that is selected, you may want to run that selected script (Wouldn't you?). Thats what @&v_script does. You run a script in SQLPlus using @script_name

PROMPT  1: Make a sales invoice 
PROMPT  2: Inquire a sales invoice    
accept selection PROMPT "Enter option 1-2: "

set term off
column script new_value v_script  --Q1. What's column script?selectcase'&selection.'--from accept abovewhen'1'then'@test1.sql'--script to run when chosen option 1.when'2'then'@test2.sql'--script to run when chosen option 2.else'@FinalAssignment.sql'--this scriptendas script  --Q2. What script is this referring to? from dual; --Q3. Don't know thisset term on

@&v_script. --Q4. What script is being ran here?

Caveats-

1. FinalAssignment.sql should be the name of the script itself i.e. the script where the above code is.

2. Line 1,2, and 3 are part of the script. PROMPT is a valid SQL*Plus command and so is ACCEPT.