I am writing a code in PL/SQL on Oracle 10g which requires a user input. I want to give a prompt for user input which I can't to do so. It'd be great if you help!
I tried the following code, just to test if the prompt was working or not. However, the code if giving an error as "ORA-00900: invalid SQL statement". Can you please tell me where I'm going wrong?
ACCEPT x NUMBER PROMPT 'Please enter a value';
DECLARE
a NUMBER:=&x;
BEGIN
dbms_output.put_line(a);
END;
/
PL/SQL does not allow user input.
ACCEPT is an SQL*Plus command that is run on the client and takes user input and stores it in a substitution variable. It runs on the client and does NOT run on the database.
&x is a substitution variable and SQL*Plus (and other clients that support substitution variables) will take the value of the substitution variable and effectively do a find-replace substituting the variable for its value. Then the replaced text is sent to the database (which never sees the original text). Again, the database does NOT understand substitution variables.
So, if you are running your code via a client application that supports substitution variables (SQL*Plus, SQL Developer, SQLCl and some others) then your code will be parsed by the client, substituted and sent to the database.
If you are running on a different client (that does not support substitution variables) then your code is invalid because ACCEPT is not a PL/SQL command (and PL/SQL does not support taking user input) and the client will send the code to the database as-is and the database will not understand it.