Hi,
I have the following problem - how to read user input from the command line? Can this be done using only PL/SQL and SQL?

Here is an example. First I want to read the "user choice", and if it is 1, read the value for "a", if it is 2 - read the value for "b". But when I execute the code below (from sql window), it prompts me to enter the "user choice" and after that, regardless of my input prompts me to enter BOTH "a" AND "b". After that, it executes only the statements (not shown in the code) for corresponding "user_choice". Someone told me that "the precompiler looks for the & symbol when parsing the code and does the prompts". Is there a way around it?


VARIABLE user_cho NUMBER
ACCEPT user_cho PROMPT 'Enter your choice: '

DECLARE
user_choice NUMBER := &user_cho;
a NUMBER := 0;
b NUMBER := 0;

BEGIN
IF(user_choice = 1)
THEN
a := &a;
ELSIF(user_choice = 2)
THEN
b := &b;
END IF;
END;
/

Thanks,
e.

In case you want to pass the input to an SQL statement as parameters, then u may as well use;

'&VAR1'

EG.

SELECT NAME
FROM EMPLOYEE
WHERE CODE = '&VAR1'
AND GRADE = '&VAR2';

The user will be prompted with,
Enter value for var1:

Once that has been entered, he/she will be prompted to enter the next, var2. Then the query will execute.


Sef