My assignment is to write a PLSQL module to insert data into database. Upon a certain condition, it may need additional information and should prompt the user for one more detail. This should be done directly in PLSQL and wording is straight from the assignment.
I've researched the topic and found some people said this cannot be done in PLSQL? But the ACCEPT PROMPT function does exist.
ACCEPT v_string PROMPT 'Enter your age: ';
While this works directly from SQLPlus, it does not work in PLSQL as it gives me this error:
PLS-00103: Encountered the symbol "V_STRING" when expecting one of the following: := . ( @ % ;
Can anyone provide some insight as to how I'm supposed to ask the user from PLSQL, only upon when a certain condition is true (the condition is checked when you get something else from the DB). To clarify, I only need help on how to accept input.
Thanks for your time.
There is a trick that will allow you to do something like this, but (a) it's a bit of a hack, (b) you need to be logged into the database server itself, and (c) it only works on Linux (and perhaps other flavours of Unix).
Generally, it's not possible to ask for user input in PL/SQL, especially if you're connecting to a database on a remote machine. Either your assignment is wrong or you've misunderstood it.
PL/SQL programs are designed to run on the database server, so it doesn't make sense to ask the user for input during them. Using the ACCEPT command, SQL*Plus can ask the user for input while running a script client-side. SQL*Plus will then substitute in the value entered before sending SQL or PL/SQL to the database.