Oracle PLSQL : How to use substitution variable based on each value?

Question

Declare
  v_test varchar2(30) := 'enter value for A';
  v_temp;
Begin
  v_temp := &v_test;
  dbms_output.put_line('v_temp :='||v_temp);
end;

While executing above PLSQL block, for substitution variable &v_test instead of prompting for Enter value for v_test it should prompt the value inside v_test. i.e. it should prompt 'enter value for A'. So each time if i change the value of v_test, then that value should prompt.

Is there any solution for this?

Answer 1

PL/SQL is not an interactive language, it is for writing APIs not UIs. Likewise SQL*Plus is not equipped control words such as IF or ELSE, it is not a programming language.

If you need a smarter UI to kick off your code you should use a UI programming tool. As you're using SQL*Plus your users have access to the OS so a shell script or .bat file seems like the best option. Also, we can run web pages straight out of the database but that may be a little over-engineered, it depends how complex your requirements might be.

If neither of those suggestions appeals you could try a more detailed SQL*Plus prompt:

ACCEPT v_test CHAR FORMAT 'A30' 
PROMPT 'Enter the test you want to run: A , B or C :'       
ACCEPT v_deptno NUMBER FORMAT '99' 
PROMPT 'If you chose A please enter DEPT NO or skip:'       
ACCEPT v_mgr CHAR FORMAT 'A30'
PROMPT 'If you chose B please enter MGR NAME or skip:'       
ACCEPT v_stdt DATE FORMAT 'YYYY-MM-DD'
PROMPT 'If you chose C enter START DATE (YYYY-MM-DD) or skip:'       
ACCEPT v_endt DATE FORMAT 'YYYY-MM-DD'
PROMPT 'If you chose C enter END DATE (YYYY-MM-DD) or skip:'       

Now you can past these variables to an anonymous block which does have the IF and ELSE control words needed to figure out which values have been presented and take the appropriate actions.

I agree it's clunky and not very forgiving so the users will hate it; but as I said this is not what these tools are really supposed to do. Anyway, find out more.