Is something like this an acceptable compromise for the users:ĭisconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2. Note that this time, I do not have to re-define 1 as it has already been done from inside the script. The closest I could get the script to work the way I wanted it to work is to add DEFINE 1 at the end of the script: Please see the outputs below:īut here's the problem - it still picks up the last value, which I'd like to avoid, when there's no input. I'd like to keep the 'process' as simple as possible as it'll be run by a user who will forget how to do it in 3 days and then wonder why the script doesn't work )Īnyways, as you have suggested, I've pre-defined 1 before running the script. User will log into the database via SQLPlus and run the script (i.e. The script is to be run directly from SQLPlus prompt.

As I've mentioned before, there's no process that calls the script. The ACCEPT command takes input from the user and stores it in a user variable, and also allows you some level of control over what the user enters.

New 5: dbms_output.put_line ( 'You entered: ' || 'abc' ) The most reliable and robust method for getting input from the user is to explicitly prompt for values using the ACCEPT and PROMPT commands. Once you've done this it will keep the value until you end the session or undefine it: New 5: dbms_output.put_line ( 'You entered: ' || '' ) ĭefine the variable before calling it. Old 5: dbms_output.put_line ( 'You entered: ' || '&1' ) Pass an empty string as arguments to the script:ĭbms_output.put_line ( '#1 cannot be null' ) ĭbms_output.put_line ( 'You entered: ' || '&1' ) However i see that this is not possible to do that PROMPT statements and accepting user inputs execute only on the SQL prompt and not inside a PL/SQL block. To overcome this there are a couple of methods you could use: If they're not already define it'll prompt you. To use them Oracle needs to know their values.

When you 'set define on', Oracle interprets ampersands as substitution variables. You can write a custom script in SSMS SELECT FROM YOURTABLE -COULD ALSO BE A USER PROMPT WHERE COLUMNNAME '' -USER PROMPT where first the PARAMETERNAME is what you name the parameter, nvarchar(50) is where you put the datatype of the column and DEFAULTVALUE is what you want as the default value if no input to be provided.

Sql prompt input